which is more efficient?

[perky416]

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

[AyKay47]

while loop, to make a switch work the way you would like it to here would entail more work than is necessary

[perky416]

Hi mate.

 

Even though with a while loop a new query is executed for each row? but with case only one query is executed for all rows? A while is still more efficient and faster?

 

Just trying to understand.

 

Thanks mate.

[perky416]

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");

[premiso]

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.

[fenway]

One UID, one update.

[ebmigue]

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.