PHP/mysqli - prepared statement (in a loop) or multi_query

Question

I've just moved on from using mysql to mysqli extension in PHP.

I've come across two ways of doing the same thing (multiple update queries), what are the pros/cons of each? Should I be using one or the other or something else entirely?

Prepared statement in a loop:

//prepare statement
foreach(whatever){
  //execute statement
}

or

Multi-query:

foreach(whatever){
  //build many queries into a single string
}
multi_query(long string)

I know that prepared statements offer better security. When using mysql in PHP I've heard its best to avoid using UPDATE statements in a loop - isn't executing a mysqli prepared statement in a loop the same thing by another name?

Answer 1

If for some reason you can't update all targeted records with just a single mysql update statement to avoid the need for this this PHP loop altogether, it's perfectly fine reusing this same update mysqli statement object in a loop.

Style and resource-wise, reusing your parameterized statement is best rather than constantly recreating it. By reusing it, all you're doing after the initial bind_param call is re-assigning the value of the bound PHP variables on each iteration, then simply re-executing (See: mysqli_stmt->execute Example #1 Object oriented style).

Remember, in your WHERE clause, you'd just have another PHP variable-assigned parameter like WHERE (recordID = ?) to iterate over.

It takes extra resources to initially set up each parameterized statement, so multiple parameterized statements should be reserved for passing in multiple unrelated statements or queries. Also, it does not appear that PHP's multi queries functions and methods support parameterization at all anyway.

Answer 2

bob-the-destroyer is right (better to use prepared statements). I just wanted to add a few things.

1. executing $mysqli->multi_query($sql) after the loop is equivalent to execute $mysqli->query($sql) every time inside the loop. The difference between multi_query and just query is that query doesn't accept more than 1 query separated by semicolons. multi_query does make it a little more difficult to check for errors on the second and following queries executed.

2. I don't know why someone would avoid issuing UPDATEs inside a loop. But it is best practice to wrap the loop in a transaction so if any query fails you can rollback all the updates.

Answer 3

The other two answers do not address the actual differences between multi-query and prepared statements - they are completely different.

• Prepared statements - you create a statement template once then execute it multiple times with different values used for each template (using bind_param). There is a server round-trip for setting up the statement and one round-trip per query. It uses MySQL's binary protocol to send data, so it will actually be sending less data through the lines than a normal query.

• Multi-query - This is simply executing multiple normal MySQL queries all at once. There is one server round-trip for the whole multi-query. This almost definitely provides better performance compared prepared statements (unless your query is abnormally gigantic and the changing values are small).

So I would recommend mutli-query for speed. Its no less secure if you properly escape your data with mysqli::real_escape_string. Another benefit of multi-query is that you can do completely different queries all in the same request - while prepared statements rely on query symmetry to provide any benefit.