mysqli: can it prepare multiple queries in one statement?

  • Added:
  • |
  • In: Basic PHP

I would like to know if i can prepare one mysqli statement that executes multiple queries:

mysqli->prepare(query1 ...1,2,3 param...; query2...4,5 param...);
mysqli->prepare(insert into ...1,2,3 param...; insert into...4,5 param...);
and after all
mysqli->bind_param("sssss", 1, 2, 3, 4, 5);

In that way it make error: Call to a member function bind_param() on a non-object in...

$stmt = $sql->getQueryPrepare("INSERT INTO user (id_user, username, pw, email) VALUES (?,?,?,?); INSERT INTO process (id_user, idp) VALUES (?,?);");

$stmt->bind_param("ssssss",$id, $username, $pw, $email, $id, $idp);

This Question Has 2 Answeres | Orginal Question | Donovant

A prepared statement can only execute one MySQL query. You can prepare as many statements as you want in different variables:

$stmtUser = $sql->prepare("INSERT INTO user (id_user, username, pw, email) VALUES (?,?,?,?)");
$stmtProc = $sql->prepare("INSERT INTO process (id_user, idp) VALUES (?,?);");

And then execute them later. If you want to ensure that neither one is ever run unless both are able to run, then you need to look into transactions, like Thomas said.

Also, a general tip: "call to member function on a non-object" is the standard error you get when prepare() fails and so $stmt isn't actually a prepared statement object. It usually means you need to look for an error in your prepare() statement rather than anything later.

No, a single call to the mysqli prepare() function cannot prepare multiple queries at once. You can, however, prepare more than one query for execution by using different variables. The documentation for this function is available here.

It also looks like you are trying to setup a transaction, which is a different question than you asked. If that's what you really want to know, then you'll need to provide more information about your database setup and probably more specifics about the use case you are trying to solve.

Meet with owner

Sajjad Hossain

Hey, I am Sajjad, working in web development sector since 2012. I love to do amazing things. Let's do a project together.
Connect Social With PHPAns