updating multiple rows / loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
imstupid
Forum Commoner
Posts: 84
Joined: Fri Feb 18, 2005 1:24 pm

updating multiple rows / loop

Post by imstupid »

hello-
So I have a table with 19 rows in it. I wanted to be able to update one field in each row all at once, instead of doing it one at a time. I've attempted to write a basic loop script which

a) takes forever
b) only updates the first row.

The table is set up with an auto_increment ID field set up as the primary key.

here's the code for that hack-job script I tried to write.

Code: Select all

...connect and other stuff here

$getID = mysql_query("SELECT ID FROM tablename"); 

$uniqueID = mysql_fetch_array( $getID )
		 or die(mysql_error());
		 
$ID = $uniqueID['ID'] ;	

while ($ID < 20) {

	$existingname = "SELECT name FROM tablename WHERE ID='$ID'"; 

	$name ="$existingname.0905";

	mysql_query("UPDATE tablename SET name='$name' WHERE ID='$ID'") 
		 or die(mysql_error()); 

 }
 
 echo "name updated";
if anyone out there could point me in the right direction I'd appreciate it.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Two tips
  • you may reference original value of a field in the UPDATE query:

    Code: Select all

    .... SET name=concat(name, '.0905')
  • UPDATE would affect entire table if you omit the WHERE clause.
thus:

Code: Select all

UPDATE tablename SET name=concat(name, '.0905')
Post Reply