How to loop through mysqli_multi_query() to find out what queries succeeded?

Question

I'm running multiple UPDATE SQL queries as:

$queriesRun = mysqli_multi_query($connection, $queries);

Now, how do I loop through the results to know which queries succeeded and which failed? The PHP manual is giving me a headache with so many functions that can be used afterwards.

Thanks!

If you need to check all the statuses, why not just do them as separate queries? — Barmar, Jun 12 '13 at 15:42
+1 @Barmar -- do separate queries. There's no advantage to multi_query, and it's a security risk. — Bill Karwin, Jun 12 '13 at 15:46

Saturnix · Accepted Answer · 2013-06-12T15:48:02.690

2

how do I loop through the results to know which queries succeeded and which failed?

int mysqli_stmt_affected_rows ( mysqli_stmt $stmt ) and bool mysqli_next_result ( mysqli $link ) are the 2 functions you're looking for.

<?php
    $mysqli = new mysqli("localhost", "my_user", "my_password", "world");

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

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

    /* execute multi query */
    if ($mysqli->multi_query($query)) {
        do {
            /* store first result set */
            if ($result = $mysqli->store_result()) {
                while ($row = $result->fetch_row()) {
                    printf("%s\n", $row[0]);
                }
                $result->free();
            }
            /* print divider */
            if ($mysqli->more_results()) {
                printf("-----------------\n");
            }
        } while ($mysqli->next_result());
    }

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

From the documentation.

If you wan to use procedural style, check the example in the documentation. You just have to use mysqli_more_results or $mysqli->next_result() to switch between various queries.

edited Jun 12 '13 at 15:48

answered Jun 12 '13 at 15:22

Saturnix

10,130
17
64
120

I'm running ``UPDATE`` and I need to know whether all UPDATEs finished successfully. If not, I need to know which failed. – Frantisek Jun 12 '13 at 15:23
Did you just copy the first example? That's likely to give the OP a headache ;-) – jeroen Jun 12 '13 at 15:23
1

Perhaps OP should have told that this is about an UPDATE query to avoid headaches. – Saturnix Jun 12 '13 at 15:25
Why does it make a difference what kind of queries they are? He said he wants to know if they succeeded or failed, that's the same for all types of queries, isn't it? – Barmar Jun 12 '13 at 15:50
It if is the same, then the answer is ok. – Saturnix Jun 12 '13 at 16:43
The types of queries DO matter. UPDATE queries won't provide result sets. So this is not a solution for the edited question. – mickmackusa Feb 08 '15 at 16:30

score 0 · Answer 2 · edited May 23 '17 at 12:05

Here is a procedural-style mysqli_multi_query solution built to take queries that do not return record sets. It displays each query statement, its affected rows, and a running count of total affected rows from $queries. In the event of an error, mysqli_multi_query() stops and the responsible error is displayed.

$single_queries=explode(';',$queries);
if(mysqli_multi_query($connection,$queries)){
    do{
        echo "<br>",array_shift($single_queries),"<br>";
        $cumulative_rows+=$aff_rows=mysqli_affected_rows($connection);
        echo "Affected Rows = $aff_rows, ";
        echo "Cumulative Affected Rows = $cumulative_rows<br>";
    } while(mysqli_more_results($connection) && mysqli_next_result($connection));
}
if($error_mess=mysqli_error($connection)){
    echo "<br>",array_shift($single_queries),"<br>Error = $error_mess";
}

Outputs (assuming 5 rows exist in Test table where Column1=''):

UPDATE Test SET Column1='changed' WHERE Column1=''
Affected Rows = 5, Cumulative Affected Rows = 5

UPDATE Test SET Column1='changed again' WHERE Column1='changed'
Affected Rows = 5, Cumulative Affected Rows = 10

If you want better-identified queries, change $queries to an associated array where the key describes each query, then check out this similar post of mine: How to identify the query that caused the error using mysqli_multi_query?

How to loop through mysqli_multi_query() to find out what queries succeeded?

2 Answers2