auto_increment problems

Acer Aspire one d250 aod250-1325 netbook
October 18, 2010 at 00:28:35
Specs: Windows 7
I have a MySQL db, with hundreds of rows being inserted/deleted every day. With this the `id` field has an auto_increment and as the rows are being deleted, there are gaps in the auto_increment. I have created a function to recalculate the auto_increment and set the id of all records one by one.

function mysql_fixauto($table){

$sql = "SELECT * FROM `".$table."`";
$res = mysql_query($sql) or die(mysql_error());
$count = mysql_num_rows($res) or die(mysql_error());

$no = 0;
while ($row = mysql_fetch_array($res) or die(mysql_error())) {
$no = $no+1;
$id = $row['id'];
$sql = "UPDATE `".$table."` SET `id` = '".$no."' WHERE `".$table."`.`id` = '".$id."';";
$res = mysql_query($sql) or die(mysql_error());
If ($no == $count){
$sql = "ALTER TABLE `".$table."` AUTO_INCREMENT =".$no."";
$res = mysql_query($sql) or die(mysql_error());
return "Operation successful!";


Problem is I keep on getting a error ;

mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /test.php on line 17

Any ideas on what is goin wrong??

Thanks in advance.

See More: auto_increment problems

Report •

October 18, 2010 at 09:09:26
You're overwriting the $res object in the loop, use a different variable name.

BTW, the only reason to bother with this is if you're running into the 2^32 limit on the number of rows - in which case, you should be using something else. See :

Report •

October 18, 2010 at 12:10:47
Thanks, solved the problem :D

Report •
Related Solutions

Ask Question