multi query with prepared statements

Question

I am trying understand how multi queries work in mysqli. But I confess that is not easy to understand.

Basically how I can do these queries in a multi query? The page doesn't talk about prepared statements in multi queries.

($sql = $db -> prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)"));
$sql -> bind_param('sss', $name, $email, $hash);
$sql -> execute();

($sq = $db -> prepare("INSERT INTO activation_link_password_reset  (activation_link) VALUES (?)"));
$sq -> bind_param('s', $linkHash);
$sq -> execute();

score 2 · Accepted Answer · answered Jun 08 '11 at 17:33

2

You can't use prepared statements there, and the speedup is also negligible, so go for the easier to debug seperate queries.

If you really want to do it in 1 call with prepared statements, create a PROCEDURE (even more difficult to debug...), and prepare CALL(:param1,:param2);.

answered Jun 08 '11 at 17:33

Wrikken

69,272
8
97
136

speed is the least. My preocupation is the SQL injection. :) – anvd Jun 08 '11 at 17:55

Bob_Gneu · Answer 2 · 2011-06-08T17:42:01.303

I actually just figured this out a few days ago. The move to MySQLi is not a trivial one but ill go ahead and show you the query loop for prepared statements.

$db = new mysqli(...)

$statement = $db->prepare('SELECT count(*) as [hitchhikers] FROM `T_Users` WHERE `id` > ? AND `signin` like ?');
$statement->bind_param("ss", 42, '%adams');

if ($statement->execute())
{
    $statement->store_result();
    $statement->bind_result($hitchhikers);
    $statement->fetch();

    // do something with hitchhikers here
}
else
{
    // there was a problem with the execution, check $db->error
}

$statement->close();
$db->next_result();

Everything here works similar to the non oo format by replacing -> with _ and passing the returned value into the new function, but this process here is pretty much what you should look to be doing with each of those statements. You can abstract it away with a mysqli wrapper class as well.

After the $db->next_result() call you are able to repeat the process again. This change has to do with the way the MySQL connection and results are handled with MySQLi. You can, of course, avoid this issue entirely by switching back to mysql_ queries and resources.

Specifically for multi_query, you separate your queries with a semicolon, in a single string. The example you linked to shows how to process the results.

MySQLi Book @PHP.net

Would you like a specific example? One that doesn't look like the one that is linked? — Bob_Gneu, Jun 08 '11 at 17:46
i don't have any problem with a single query. The question is related to multi querie with prepared statements. thanks. — anvd, Jun 08 '11 at 17:48
I see. You cannot use multi_query and prepared statements. There is no mechanism to do the binding for them. — Bob_Gneu, Jun 08 '11 at 17:50

multi query with prepared statements

2 Answers2