Mysql auto_increment messed up because I deleted some id's...how do I fix this?

Question

I'm creating a database that auto increments. I made some error so I had to delete and insert a few id's in the middle of the database. Now, every time I insert a new id data into the table, despite explicitly saying what id I want the new data to be assigned to, it's still assigning the new data to a completely wrong id number. For example, I have datas with ID 1-20. I deleted datas from 5-20, leaving id 1-4. Now when I insert a new ID, I'd like it to be 5 instead of 21. How do I do this using terminal or the php (below)?

<?php
  // 1. Create a database connection
  $dbhost = "127.0.0.1";
  $dbuser = "widget_cms";
  $dbpass = "xxxxxxx";
  $dbname = "widget_corp";
  $connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
  // Test if connection failed. If yes, exit.
  if(mysqli_connect_errno()) {
    die("Database connection failed: " . 
         mysqli_connect_error() . 
         " (" . mysqli_connect_errno() . ")"
    );
  }
  $id = 5; 
  $menu_name = "New ID";
  $position = 5;
  $visible = 1;
  // 2. Perform database query
  $query = "UPDATE subjects SET ";
  $query .= "menu_name = '{$menu_name}', ";
  $query .= "position = '{$position}', ";
  $query .= "visible = '{$visible}' ";
  $query .= "WHERE id = '{$id}' ";
  $result = mysqli_query($connection, $query);
  if ($result && mysqli_affected_rows($connection) == 1) {
      // Success. Usually redirects to some other page
      echo "<h1>Success!</h1>";
    } else {
      die("Database query error. " . mysqli_error($connection));
  }
?>

If your id's ___must___ be consecutive, then there's something wrong with your design: autoincrement is intended for unique ids, not consecutive values — Mark Baker, Mar 05 '15 at 23:37
Once you have used the id normally you cannot use it again, as MySQL maintains the counter so to speak and just add's one every time a new row is inserted. However have a look at this http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql — RiggsFolly, Mar 05 '15 at 23:43
a common problem people find when they come from spreadsheets. — , Mar 05 '15 at 23:45

score 0 · Accepted Answer · answered Mar 05 '15 at 23:43

0

You can use this MySQL command:

ALTER TABLE subjects AUTO_INCREMENT = 5

But as noted by Mark Baker in his comment: "Autoincrement is intended for unique ids, not consecutive values.". So your code should not rely on consecutive values.

answered Mar 05 '15 at 23:43

KIKO Software

15,283
3
18
33

Mysql auto_increment messed up because I deleted some id's...how do I fix this?

1 Answers1