You have no "WHERE" clause on your $Query.
So it iterated through the loop, and updated ALL records in the database
with the $counting.... each time. In essence, each time the query
executed, it updated 22 records with the same id number. You're simply
seeing the last query result.
Update counter set id='$id'
Updates ALL rows with that id number.
update counter set id='$id' where id='$currentnumber' will isolate it
row by row. But you've got to get the $currentnumber first.
You can do this using a fetchall array ref in one query, and an active
update with where:
(Of course, this is done with Perl here, not PHP like you're using - but
you should be able to grasp the concept anyway)...
#------------------------------------------------------------
$x=0;
$query=qq~select `id` from counter~;
$sth=$dbh->prepare($query);
$sth->execute || print "Error: $DBI::errstr\n";
$id_num = $sth->fetchall_arrayref() or die "$DBI::errstr\n";
$last_row = $#{$id_num};
$last_col = $#{$id_num->[$row_index]};
$sth->finish;
foreach $row_index(0..$last_row){
$x++;
$currentnumber=$id_num->[$row_index][0];
$query=qq~update counter set `id`='$x' where `id`='$currentnumber'
limit 1~;
$dbh->do($query);
}
#-------------------------------------------------------------
- or by using two statement handles (2 dbd connections) simultaneously:
$x=0;
$query1=qq~select `id` from counter~;
$sth1=$dbh1->prepare($query1);
$sth1->execute;
while(@row=$sth1->fetchrow){
$x++;
$query2=qq~update counter set `id`='$x' where `id`='$row[0]' limit 1~;
$dbh2->do($query2);
}
$sth->finish;
#--------------------------------------------------------------
A little simplistic, but I think it will fix the 22 rows with the same
number due to the limit 1 part of the query. If not, then you'll have
to manually change all of the row id numbers to fix it. (Good thing this
was done on a table with only 22 rows... always have a backup).
Actually, this manipulation of reference numbers is a bad idea, unless
it's just for your own single personal use.
Here's why:
If your database is to be edited/addedto/deleted by multiple users
(simultaneous web users?), you can get into trouble by changing key id's
this way. For instance, if you've got a user entering info into the
database on a specific record referenced by the $id, and the entry
hasn't been submitted yet... and you... update your database using this
method and changing all of the reference id's, once the user submits the
record update, guess what-> the original reference id will have been
renumbered/renamed by the routine, and the table update is done on the
wrong record.
Just some food for thought.
Good luck.
PG wrote:
When deleting a row from the database, that id is now missing.
So what I'm trying to do is update in a loop (maybe an sql loop if
there is one) of all of the id numbers - sort of like renaming them.
It did partly work because all the id's were set to 22. Thats because
there was 22 rows.
Here's the code I used:
$result = mysql_query ("SELECT * FROM counter");
for($counting = 1; $row = mysql_fetch_row ($result); ++$counting)
{
$id = $counting;
print("-- -#1-IDcounting=$id ");
$Query = "UPDATE counter SET id='$id' ";
$aResult = mysql_query($Query);
$id = 0;
print("-- -#2-IDcounting=$id ");
print("<BR>");
}
//$aResult = mysql_query($Query); //not sure if I need it again
The result was:
-- -#1-IDcounting=1 -- -#2-IDcounting=0
-- -#1-IDcounting=2 -- -#2-IDcounting=0
-- -#1-IDcounting=3 -- -#2-IDcounting=0 ... etc
It looks like its working, however when accessing the database all the
id's were set to 22.
Did I do something wrong in the loop or is there some other way?
