Page 1 of 1

looping through MySQL to change the id to equal # of rows

Posted: Tue Oct 07, 2003 11:09 pm
by onefocus99
When deleting a row from the database, that id is now missing.
-So what I'm trying to do, is update in a loop of all of the
id numbers - sort of like renaming them.

Here's the code I used:

$x=0;
$query1 = mysql_query ("SELECT * FROM counter", $db);
$thenum = mysql_num_rows($query1);
while( $therow = mysql_fetch_array($thenum) )
{
$x++;
$therow_id = $therow[id];
$query2 = "UPDATE counter SET id='$x' WHERE id='$therow_id' limit 1";
$aResult = mysql_query($query2);
}

Nothing happens though and I get this:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result
:?
that warning can't be right because I've used it before in another place.
Any-ways I'm probably missing something in the code.

Posted: Wed Oct 08, 2003 12:02 am
by Paddy
while( $therow = mysql_fetch_array($thenum) )

should be

while( $therow = mysql_fetch_array($query1) )

Posted: Wed Oct 08, 2003 10:09 am
by onefocus99
Yes, thankyou, it worked and the warning went away as well. Such a small thing when lots on your mind can mess things up.

Posted: Thu Oct 09, 2003 10:49 am
by JAM
Just a thought...

Code: Select all

update table set id = id - 1 where id > $id_you_removed

Posted: Fri Oct 10, 2003 4:59 am
by twigletmac
The point of an id value is to be unique for each record. IMHO, you should not be reusing deleted values as it removes the uniqueness of the id and will cause massive problems if you use that id to refer to a record.

Mac