How do you check if table is empty before resetting the auto increment? I have a database table that a user can add to and remove data from within my drupal module. What I would like to do is automatically check to see if the table is completely empty after every delete, in which case I would like to run an "ALTER TABLE" to reset the auto increment to 1. However I am having trouble building a function that does this for me. 

How can I implement a function to automatically detect if all fields are empty, and automatically reset the increment without the need to go into the database and apply ALTER SQL manually?

Here is some of the code...

function achievementlist_entry_delete($form, &amp;$form_state) {
foreach(($form_state['values']['table']) as $rows) {
    $num_deleted = db_delete('achievements')
    -&gt;condition('achvmntID', $rows)
    -&gt;execute();
}
    //Here I would like to check the "achievements" database, as well as ALTER TABLE
    //it is empty
}
Important: This is a risky thing to do, particularly on a production site.
It could possibly cause problems with your database so be sure that you know your database schema very well and are fully aware of the implications of doing this.



You already use db_delete() so you seem to be familiar with database interaction.

So you just need to query to check for any values. You could use something like:

SELECT COUNT(*) FROM achievements


and make sure the result is zero, or 

SELECT 1 FROM achievements LIMIT 1


and make sure that no rows are returned.

You can use db_query().

Then you can use db_query() again to reset the increment.

For example in mysql it would be:

ALTER TABLE achievements AUTO_INCREMENT = 1


Since it seems you already know drupal db this isn't really a drupal question.
It breaks down into:
How do I check a database table is empty and how do I reset an auto increment field.

In both cases searching google for that will get lots of results, including stack exchange answers or your chosen database's manual pages.