Page 1 of 1

mysql mass decrement ID field

Posted: Tue Apr 01, 2008 2:40 pm
by hopper
here is a snippet of code i am building, the goal will be to delete an entry from the database then decrement the remaining ID fields by one to fill the gap, that way when a new entry is added its added with the proper value

Code: Select all

 
<?php
$mysqli = new mysqli("localhost", "root", "****" "htf");
 
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
else {
    $sql = "DELETE FROM ".$_GET['type']." WHERE id=".$_GET["id"];
    echo $sql;
    //mysqli_query($mysqli, $sql);
    echo    "File Removed<br>";
//for ($x = $_GET['id']; $x <= $_GET['highest']-1; $x++) {
//$y=$x+1;
$sql = "UPDATE {$_GET['type']} SET id= id - 1 WHERE id > {$_GET['id']}";
echo $sql."<br>";
    mysqli_query($mysqli, $sql) or die('failed');
//}
mysqli_close($mysqli);
}
//echo "<meta http-equiv=\"refresh\" content=\"0; URL=\"./manage.php\">";
?>
 
the delete query works so i commented it out for debugging purposes
the UPDATE query when passed values echos out to

Code: Select all

UPDATE Other SET id= id - 1 WHERE id > 3

Code: Select all

but dies
any suggestions?

Re: mysql mass decrement ID field

Posted: Tue Apr 01, 2008 2:46 pm
by John Cartwright
You should never mess with the auto_incremented fields. You'll likely run into errors as you get more requests per second. Take a look at the race condition.

Question is, why do you even want to bother updating the ids?

Re: mysql mass decrement ID field

Posted: Tue Apr 01, 2008 2:54 pm
by hopper
first of all i deliberatly set it to NOT auto increment, should have mentioned that to begin with
second, its to maintain a menu that uses the id field as the number for the menu item, so when i delete the menu it automatically decrements them and then adds one at the end with the proper value