mysqli: can it prepare multiple queries in one statement?

Question

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...);
or 
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);

$stmt->execute();
$stmt->close();

score 32 · Accepted Answer · answered Jul 24 '12 at 16:55

32

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.

answered Jul 24 '12 at 16:55

octern

4,825
21
38

1

But multiples prepare isn't atomic process? – Donovant Jul 25 '12 at 16:42
1

Correct, doing this with multiple prepares is not atomic. – octern Jul 25 '12 at 19:30
4

@octern, **This doesn't work**. Indeed it doesn't even make sense if you'd think about it, the calls cannot intersect each other since they are sharing the same `$mysqli` connection. If you pre-prepared two statements and then tried to execute both `$stmtUser` and `$stmtProc`, only one will succeed and the second one will definitely fail. Have you actually tested and gotten an actual working code? – Pacerier Jun 29 '15 at 10:33
Works fine here – pmiguelpinto90 Jul 05 '18 at 13:44
@Pacerier if you mean by intersecting that you get an error, then close the statement after executing it. I am not sure if multiple prepares can happen, but executing one and not closing it will cause sync errors when a second it executed. – Richard Kiefer Mar 13 '20 at 14:39
Can there be interference, when I prepare one statement, then start a foreach and inside bind and execute this statement every turn and ALSO have one NORMAL query (not a prepared statement) which is executed? – bomben Jun 30 '23 at 19:45

score 17 · Answer 2 · edited Apr 03 '19 at 17:43

17

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.

edited Apr 03 '19 at 17:43

Neuron

5,141
5
38
59

answered Jul 24 '12 at 16:15

Thomas

1,402
1
11
14

1

While you can prepare more than one query for execution by using different variables, you **cannot intersect** them. [It won't work](http://stackoverflow.com/questions/11632902/mysqli-can-it-prepare-multiple-queries-in-one-statement#comment50240272_11635679). – Pacerier Jun 29 '15 at 10:36

mysqli: can it prepare multiple queries in one statement?

2 Answers2

Linked

Related