Why Can't I Run Two MySQLi Queries? The Second One Fails

Why can't I run two mysqli queries? The second one fails

It is possible with mysqli_multi_query().

Example:

<?php

$mysqli = new mysqli($host, $user, $password, $database);

// create string of queries separated by ;
$query  = "INSERT INTO images (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName');";
$query .= "INSERT INTO images_history (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name, day, month, year) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName', '$day', '$month', '$year');";

// execute query - $result is false if the first query failed
$result = mysqli_multi_query($mysqli, $query);

if ($result) {
    do {
        // grab the result of the next query
        if (($result = mysqli_store_result($mysqli)) === false && mysqli_error($mysqli) != '') {
            echo "Query failed: " . mysqli_error($mysqli);
        }
    } while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli)); // while there are more results
} else {
    echo "First query failed..." . mysqli_error($mysqli);
}

The key is that you must use mysqli_multi_query if you want to execute more than one query in a single call. For security reasons, mysqli_query will not execute multiple queries to prevent SQL injections.

Also keep in mind the behavior of mysqli_store_result. It returns FALSE if the query has no result set (which INSERT queries do not) so you must also check mysqli_error to see that it returns an empty string meaning the INSERT was successful.

See:

mysqli_multi_query

mysqli_more_results

mysqli_next_result

mysqli_store_result

Mysqli doesn't allow multiple queries?

mysqli allow multiple queries with mysqli_multiple_query function like this:

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if (mysqli_multi_query($link, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
}

note that you need to use semicolon after each query.

Syntax error when running multiple queries through mysqli

With BEGIN; and COMMIT; that are four statements. You can't run multiple statements in one mysqli_query() call. If you need a transaction, use mysqli::begin_transaction(). And only use one statement per mysqli_query() call:

$conn->begin_transaction();
$conn->query("INSERT ...");
$conn->query("DELETE ...");
$conn->commit();

Note that you should also configure mysqli to throw exceptions. I would write your script the following way:

// set connection variables

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect($servername, $username, $password, $dbname);
$timestamp6h = time() - 21600;

$conn->begin_transaction();
$conn->query("
    INSERT INTO archiv6h SELECT * FROM links WHERE tweettimestamp < $timestamp6h
    ON DUPLICATE KEY UPDATE
        archiv6h.tweetscount    = archiv6h.tweetscount    + links.tweetscount,
        archiv6h.followerscount = archiv6h.followerscount + links.followerscount,
        archiv6h.tweettimestamp = archiv6h.tweettimestamp + links.tweettimestamp
");
$conn->query("DELETE FROM links WHERE tweettimestamp < $timestamp6h");
$conn->commit();

If anything fails, $conn->commit(); will never be executed, and the script will output an error message. Threre is not even a need to close the conection, since it will be closed when the script ends.

mysqli_multi_query - Commands out of sync; you can't run this command now

I just found the answer in the PHP manual:

WATCH OUT: if you mix $mysqli->multi_query and $mysqli->query, the
latter(s) won't be executed!

BAD CODE:

$mysqli->multi_query(" Many SQL queries ; "); // OK
$mysqli->query(" SQL statement #1 ; ") // not executed!
$mysqli->query(" SQL statement #2 ; ") // not executed!
$mysqli->query(" SQL statement #3 ; ") // not executed!
$mysqli->query(" SQL statement #4 ; ") // not executed!

The only way to do this correctly is:

WORKING CODE:

$mysqli->multi_query(" Many SQL queries ; "); // OK
while ($mysqli->next_result()) {;} // flush multi_queries
$mysqli->query(" SQL statement #1 ; ") // now executed!
$mysqli->query(" SQL statement #2 ; ") // now executed!
$mysqli->query(" SQL statement #3 ; ") // now executed!
$mysqli->query(" SQL statement #4 ; ") // now executed!

I just insert this code after mysqli_multi_query():

while(mysqli_next_result($connect)){;}

PHP mysqli query issue when running multiple queries inside while loops

Short answer

You missed to add $getMachineID->store_result(); before the inner while loop.

Background

If you make a query (using a regular query or via a prepared statement), a statement is returned that blocks your database connection until all results are fetched, or the whole result set is stored, or the statement is closed.

So if you want to make nested queries, you have to either fetch all results saving them in an array or something and close the statement, or use store_result().

You have to call store_result(), if you want to use the affected_rows property of the statement. But you have to call it also, if you are fetching e.g. data from a LONGTEXT field, since it can contain up to 4 GB of text. If you just call fetch(), it results (without changing the PHP settings) in an allocation error, because PHP tries to allocate 4 GB of memory to be sure that it can fetch the whole string.

Improvements?

Maybe you can get rid of the inner while loop by using a JOIN. Something like

SELECT
  machineID, machineIP, machineName, hostID 
FROM
  machines
  JOIN machinedetails USING(machineID)
WHERE
  machinePurpose = ?

This would reduce the number of queries and should improve the performance and also solve the problem.

How to Select multiple queries in PHP to MySQL

You can resolve the problem with Mysql UNION statement.

$query = "(SELECT * FROM `table` WHERE `ip` LIKE '1.1.1.1') UNION (SELECT * FROM `table` WHERE `ip` LIKE '2.2.2.2')";

---

Related Topics