MySQLi: Can It Prepare Multiple Queries in One Statement

mysqli: can it prepare multiple queries in one statement?

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.

MySQLi multiple prepare statements

Two points:

1. Based on personal experience, you can only have one prepared statement in existence at a time. I suspect this is because the db requires each PS to have a session-unique name, and the PHP layer is passing some common default name rather than generating a unique name for each PS. By comparison, the PostgreSQL driver allows an optional name for each PS, but still allows only one unnamed PS to exist. Essentially this means that you must prepare, bind, execute and fetch one PS completely before you can prepare the next PS.

2. You're misusing mysqli_stmt::fetch(). fetch() returns only true or false, and is used to update variables which have previously been bound with mysqli_stmt::bind_result(). To retrieve values into a $row array, you must first call mysqli_stmt::get_result() to return a mysqli_result, and then call mysqli_result::fetch_array().

Can a prepared statement hold multiple queries in php

You can't execute multiple statements in a prepared query:

SQL syntax for prepared statements does not support multi-statements
(that is, multiple statements within a single string separated by ;
characters)

so you will need to prepare and execute each of the queries separately, using mysqli_stmt::insert_id to get the appropriate id value for the second and third queries:

$sql = "INSERT INTO `user_info`(`first_name`, `last_name`, `phone`, `cpf`) 
            VALUES (?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssss', $firstName, $lastName, $phone, $cpf);
$stmt->execute();
$insert_id = $stmt->insert_id;
$stmt->close();

$sql = "INSERT INTO `{$table}`(`email`, `password`, `active`,`user_info_id`, `created`, `role_id`" . $restaurantInsert . ")
            VALUES (?, ?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssiisss', $email, $password, 1, $insert_id, $created, $role, $restaurantValue);
$stmt->execute();
$stmt->close();

$sql = "INSERT INTO `address`(number, street, city, state, zip, district, country, created, user_info_id)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
$stmt = $conn->prepare($sql);
$country = 'BR';
$stmt->bind_param('sssssssi', $number, $street, $city, $stateCode, $zip, $district, $country, $created, $insert_id);
$stmt->execute();
$stmt->close();

Note I'm not 100% certain what you're trying to achieve with role_id" . $restaurantInsert . ", you might need to edit the second query appropriately to use that.

Multiple Prepared statements in PHP with MySQLi

Directly off the mysqli page: http://php.net/manual/en/mysqli.commit.php

<?PHP
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$mysqli->set_charset('utf8mb4');

/* set autocommit to off */
$mysqli->autocommit(FALSE);

/* Insert some values */
$mysqli->query("INSERT INTO table1 VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO table2 VALUES ('DEU', 'Bavarian', 'F', 11.2)");

/* commit transaction */
$mysqli->commit();

/* close connection */
$mysqli->close();

*Edit with prepared statements for "non-sane" action:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "root", "", "");
$mysqli->set_charset('utf8mb4');

/* set autocommit to off */
$mysqli->autocommit(FALSE);

$stmt1 = $mysqli->prepare("INSERT INTO tbl1 (id, intro) VALUES (?, ?)");
$stmt2 = $mysqli->prepare("INSERT INTO tbl2 (id, name) VALUES (?, ?)");

$str1 = 'abc';
$str2 = 'efg';
$str3 = 'hij';
$str4 = 'klm';

$stmt1->bind_param('ss', $str1, $str2);
$stmt2->bind_param('ss', $str3,$str4);

$stmt1->execute();
$stmt2->execute();

/* commit and set autocommit to on */
$mysqli->autocommit(true);

Prepared Statements - Multiple Queries

Just put your statements one under another. Do not use close(), do not use mysqli prepared statements, do not collect 200:

$stmt = $pdo -> prepare("SELECT * FROM table WHERE id = ?");
$stmt -> execute(array($rid));
$results = $stmt -> fetchAll(); // for many rows

$stmt = $pdo -> prepare("SELECT id FROM table WHERE name = ?");
$stmt -> execute(array($name));
$id = $stmt -> fetchColumn(); // for single scalar value

$stmt = $pdo -> prepare("SELECT * FROM table WHERE id = ? LIMIT 1");
$stmt -> execute(array($rid));
$row = $stmt -> fetch(); // for single row

and so on

Or, with even more intelligent library, it could be even

// one single line to get your $results instead of screenful of code, mind you
$results = $db->getAll("SELECT * FROM table WHERE id = ?i",$rid);
// as well as others
$id      = $db->getOne("SELECT id FROM table WHERE name = ?s", $name);
$row     = $db->getRow("SELECT * FROM table WHERE id = ?i LIMIT 1",$rid)

Still want to stick with raw mysqli?

Multiple MYSQLi prepared statements

your code is good however you should dont name them the same name $stmt

the first statment is $stmt

then give second statment other name like $stmt2. or what ever name you like.

example:

$stmt = $con->prepare("INSERT INTO reviews (order_id, comment) VALUES (?, ?)");
$stmt->bind_param('is', $order_id, $comment);
$stmt->execute(); 
$stmt->close();


// Update transactions to show review added
$stmt2 = $con->prepare("UPDATE transactions SET review = ? WHERE order_id = ?");
$stmt2->bind_param('ii', 1, $order_id);
$stmt2->execute(); 
$stmt2->close(); 

and to debug your code and see where is the error use this.

if ($stmt = $con->prepare("INSERT INTO reviews (order_id, comment) VALUES (?, ?)") ){
   $stmt->bind_param('is', $order_id, $comment);
   $stmt->execute(); 
   $stmt->close();
    }
 else {printf("Error message:: %s\n", $con->error);}

Using one parameter multiple times in prepared mysqli-statement

Just to close the question:

The answer is no.

If you want to bind a parameter only one time and using it multiple times in a query you have to use PDO and this maybe also needs a special configuration.

But there seems to be more reasons to use PDO instead of mysqli, according to this great answer or this.

But sure there are workarounds. See the other answers to this question.

PHP MySQLi multi_query prepared statement

No.

mysqli::multi_query takes a query string as its argument, not a prepared statement.

mysql::prepare can only prepare a single statement:

The query must consist of a single SQL statement.

---

Related Topics