Jump to content

which is more efficient?


perky416

Recommended Posts

The code im using is similar to the following:

 

Query within a while loop:

 

while($row = mysql_fetch_assoc($query)){
mysql_query("UPDATE products SET price='$_POST['price'][$i] WHERE product='$_POST['checkbox_value']'");
$i++;
}

 

single query using case:

 

while($row = mysql_fetch_assoc($query)){
$string .= "WHEN product='" . $_POST['checkbox_value'][$i] . "' THEN ' ". $_POST['price'][$i] . "'";
$i++; }

mysql_query("UPDATE products SET price = CASE $string ELSE price END");

One query will always be preferable to multiple. But running the query inside the loop causes a ton of overhead, I would even just do this instead of that:

 

while($row = mysql_fetch_assoc($query)){
    $update .= "UPDATE products SET price='$_POST['price'][$i] WHERE product='$_POST['checkbox_value']';\n";
    $i++;
}

mysql_query($update) or trigger_error('Update Failed: ' . mysql_error());

 

As that way, only 1 function call is used and reduces the overhead on the php end of things. But if the CASE works, that would probably be even better than the multiple queries and 1 mysql_query call above.

Hi everyone,

 

Please could somebody give me their views on which is a more efficient and faster way to update multiple rows of a database with different values?

 

Using a query within a while loop or using a query with a case?

 

Thanks

 

Why would you want to use a loop when using SQL UPDATE?

 

The whole point with SQL UPDATE is not to use loops anymore, because the DBMS will take care of it for you.

 

Of course, there are cases, that it is necessary to use loops.

 

But still, use UPDATE, use CASE WHEN in the SET clause if necessary, and refine your WHERE clause. That ought to do it.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.