The first query simply updates a single column within a mysql row:
UPDATE tableName SET fieldName= 'fieldValue' WHERE fieldName2= 'fieldValue2';
The last mysql statement is fantastic for a single query. However, now suppose that we need to update multiple rows, and the field name changes in value. Said in other words, there are different fields that require a different updated value. Well, here is an example :
First, put the fields to be updated into an associative array and put it through a PHP foreach loop
<?php foreach ($associative_array as $field => $value) { $sql = "UPDATE categories SET fieldname = $value WHERE field = $field"; mysql_query($sql); } ?>
that should work right? … … NOPE … well, why?
OK … It will probably work, but one thing that you have to keep in mind is that you are pinging the database N times for N=the amount of rows that need to be updated. What if N=1000?
This is saying that you have N queries to make, and what if there are are N users doing the same thing? You are setting yourself up for a crash.
Is there another way? … YEP
UPDATE mytable
SET fieldName= CASE other_field
WHEN 1 THEN 'value'
WHEN 2 THEN 'value'
WHEN 3 THEN 'value'
WHEN 4 THEN 'value'
END
WHERE id IN (1,2,3, 4);
OK … we are going to apply this to a PHP script, but first let’s talk about the scenario in which multiple columns must be updated within a single MySql query:
So, if the CASEis the fieldname that we are checking for, and the value of the fieldname matches the value we are checking for, then we update it to the new field value.
The WHERE clause ensures that the logic is only applied to the smallest number of rows possible. Now you can see the event in which Nis a row, and N->1000. Now see that all N rows were updated in only 1 Query. Pretty cool
What about when you need to update multiple fields instead of one? We add another CASE block.
UPDATE tableName
SET fieldname1= CASE fieldValue1
WHEN robot THEN 'voltron'
WHEN truck THEN 'optimus Prime'
WHEN websites THEN 'redbonzai'
WHEN code THEN 'PHP'
END,
fieldname2= CASE fieldValue2
WHEN 'clown' THEN 'Bozo'
WHEN 'robot' THEN 'transformer'
WHEN 'furry' THEN 'Teddy Bear'
WHEN 'OS' THEN 'Linux'
END
WHERE fieldValue2 IN ('clown', 'robot', 'furry', 'OS');
Alright, now back to the PHP example. Lets turn this into a dynamic query:
<?php
$column1 = array( 'rice' => 'wild',
'developer' => 'php',
'furry'=> 'teddy bear',
'funny'=> 'comedian',
'work'=> 'never stops',
'money'=> 'millionaire',
'pink'=> 'polka dots',
'Furniture'=> 'Modern'
);
$column2 = array(
'rice' => 'wild',
'developer' => 'php',
'furry'=> 'teddy bear',
'funny'=> 'comedian',
'work'=> 'never stops',
'money'=> 'millionaire',
'pink'=> 'polka dots',
'Furniture'=> 'Modern'
);
$fields1 = implode(',', array_keys($column1));
$fields2 = implode(',', array_keys($column2));
$sql = "UPDATE column1 SET fieldName1= CASE fieldValue ";
foreach ($column1 as $field => $value) {
if(is_numeric($field)){$field='$field';}else {$field=$field;}
$sql .= sprintf("WHEN %s THEN %s ", $field, $value);
//echo $field .": ".$value."
";
if($field==end(array_keys($column1))){
$sql .= "END WHERE fieldName1 IN ($fields1);";
$sql .= "UPDATE column2 SET fieldName2 = CASE fieldValue2";
}
}
foreach($column2 as $field=>$value){
if(is_numeric($field)){$field='$field';}else {$field=$field;}
$sql .= sprintf("WHEN %s THEN %s ", $field, $value);
if($field== end(array_keys($column2))){
break 1;
}
}
$sql .= "END WHERE fieldName2 IN ($fields2)";
echo $sql; //I echoed this out so that you can copy and paste it into your IDE, and see how it works.
Next step is:
$query = mysql_query($sql) or die(mysql_error());
// there are much better ways to validate your query, but this article is not about PHP query validation,
it is about a single query to update multiple rows.
?>
Now in this particular example, only 8 rows are being updated, but 7 queries have been trimed, which is not insignificant. Apply this technique to a situation where hundreds or thousands of rows have to be updated and you can imagine the benefits it will have. There is a down side to this …
I understand if you have multiple rows to update, but you are ONLY updating 2 or three columns.
The more columns you update, you have to make another foreach loop.
But in the end, you end up with a pretty nice SQL string, and only 1 Query.
