0

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, &$form_state) {
foreach(($form_state['values']['table']) as $rows) {
    $num_deleted = db_delete('achievements')
    ->condition('achvmntID', $rows)
    ->execute();
}
    //Here I would like to check the "achievements" database, as well as ALTER TABLE
    //it is empty
}
  • As a side question, why do you want to reset the auto increment? – rooby Oct 25 '14 at 12:11
  • At least during the course of development, it is just a matter of personal preference. If there is only one item in the database after I add and delete everything it would be nice to see everything reset automatically without the need to type any additional SQL. – jrounsav Oct 27 '14 at 19:48
0

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.

| improve this answer | |
  • I understand the process and the SQL but I am having trouble with the syntax. Do I insert something like "if($query == null){ALTER TABLE...}" or "if(!$query){...}" I am not sure what to use to make sure that no rows are returned. – jrounsav Oct 27 '14 at 19:52
  • This is really dangerous as it opens up a race condition that could lead to a destabilized site. Auto-increment fields are not meant to be reset. – Jaypan Aug 24 '18 at 21:46
  • @Jaypan I agree, this is potentially risky and is generally not necessary so I have added a note to the top in regards to that. – rooby Aug 26 '18 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.