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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
onefocus99
Forum Newbie
Posts: 16
Joined: Tue Oct 07, 2003 11:09 pm
Location: Hamilton Ont., Montreal, Chibougamau,... PQ, Victoria BC, now Alberta Canada

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

Post 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.
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post by Paddy »

while( $therow = mysql_fetch_array($thenum) )

should be

while( $therow = mysql_fetch_array($query1) )
onefocus99
Forum Newbie
Posts: 16
Joined: Tue Oct 07, 2003 11:09 pm
Location: Hamilton Ont., Montreal, Chibougamau,... PQ, Victoria BC, now Alberta Canada

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Just a thought...

Code: Select all

update table set id = id - 1 where id > $id_you_removed
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Post Reply