467,148 Members | 1,283 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,148 developers. It's quick & easy.

looping through MySQL to change the id (using PHP) to equal the number of rows

PG
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?
Jul 19 '05 #1
  • viewed: 3741
Share:
6 Replies
BDR
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:
#------------------------------------------------------------
$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?


Jul 19 '05 #2
BDR
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:
#------------------------------------------------------------
$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?


Jul 19 '05 #3
BDR
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?


Jul 19 '05 #4
BDR
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?


Jul 19 '05 #5
PG
Yes thankyou. Here is the code in PHPL:.

$x=0;
$query1 = mysql_query ("SELECT * FROM counter", $db);
while( $therow = mysql_fetch_array($query1) )
{
$x++;
$therow_id = $therow[id];
$query2 = "UPDATE counter SET id='$x' WHERE id='$therow_id' limit 1";
$aResult = mysql_query($query2);
}
BDR <jo*@noemail.com> wrote in message news:<3F**************@noemail.com>...
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?

Jul 19 '05 #6
PG
Yes thankyou. Here is the code in PHPL:.

$x=0;
$query1 = mysql_query ("SELECT * FROM counter", $db);
while( $therow = mysql_fetch_array($query1) )
{
$x++;
$therow_id = $therow[id];
$query2 = "UPDATE counter SET id='$x' WHERE id='$therow_id' limit 1";
$aResult = mysql_query($query2);
}
BDR <jo*@noemail.com> wrote in message news:<3F**************@noemail.com>...
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?

Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Reply via newsgroup | last post: by
reply views Thread by Phil Powell | last post: by
reply views Thread by Philip Stoev | last post: by
reply views Thread by Mike Chirico | last post: by
6 posts views Thread by Ridge Burner | last post: by
Atli
6 posts views Thread by Atli | last post: by
nine72
2 posts views Thread by nine72 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.