mysqli_multi_query: need to check single result?

Question

I'm using this code to insert a record with a unique id and then return the id-string just created:

$sql ="set @id=UUID();";
$sql .="INSERT INTO `items` (`id`,`parent`, `text`, `type`) VALUES(@id,'".$parent."', '".$text."', '".$type."');";
$sql .="select @id;";


if (mysqli_multi_query($conn, $sql)) {
  while (mysqli_more_results($conn)) {
    mysqli_use_result($conn);
    mysqli_next_result($conn);
  }//while

  $result = array('id' => mysqli_store_result($conn)->fetch_row()[0]);

  }//if

If everything works as it should, the three queries should return:

1/true (I guess)
1/true
object

I never used this function before and I was wondering: what happens if the insert query fails?

The third query will still be executed?

And in that case, how can I check the result of the second query?

Edit:

Or in general:

having a set of 10 queries, in case of failure how can I check which one has failed?

_“having a set of 10 queries, in case of failure how can I check which one has failed?”_ - quote manual for mysqli_multi_query: _“Returns FALSE if the first statement failed. To retrieve subsequent errors from other statements you have to call mysqli_next_result() first.”_ — CBroe, Feb 01 '18 at 11:17
Well, that's pretty weird because after some tests it seems that a query failure stops the execution of the next queries... how can I get the next result if there's no next result? (WTF) I guess it is intended as: if the first query is ok, then go to the next using mysqli_next_result. (?) — Gwen Hufschmid, Feb 01 '18 at 15:33

Gwen Hufschmid · Answer 1 · 2018-02-01T17:14:23.370

After some test...

Let's assume we have the following 3 queries:

$sql ="INSERT INTO `items` (`id`,`parent`) VALUES ('id',WRONG_NO_QUOTES);";
$sql .="INSERT INTO `items` (`id`,`parent`) VALUES ('id','right2');";
$sql .="INSERT INTO `items` (`id`,`parent`) VALUES ('id','right3');";

Using the code provided by the manual...

if (mysqli_multi_query($conn,$sql)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($conn)) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if (mysqli_more_results($conn)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($conn));
}
//...plus this....
if(mysqli_errno($conn)){
        echo 'mysqli_errno:';
        var_dump(mysqli_errno($conn));
        echo'<br>';
    }

...the output is:

mysqli_errno:
[...]test.php:39:int 1146

...and there are no changes to the database.

Conclusion:

When an error occours, the next queries are NOT executed. AND the error can be easly catched at the end of the do/while loop.

EDIT

The code above generates a strict standards notice.

I edited it to avoid the notice and get a (basic) backtrace of the error:

$n=1;
$i=1;
if (mysqli_multi_query($conn,$sql)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($conn)) {
            while ($row = $result->fetch_row()) {
                echo $row[0];
            }
            $result->free();
        }
        /* print divider */
        if (mysqli_more_results($conn)) {
            echo "<hr>";
            $n++;
            mysqli_next_result($conn);
        }else{$i=0;}
    } while ($i>0);
}
if(mysqli_errno($conn)){
  echo 'mysqli error on query number '.$n;
  var_dump(mysqli_errno($conn));
}

score 0 · Accepted Answer · answered Feb 08 '18 at 10:55

Since my actual problem was to avoid competition between queries and get the right identifier (check: this question) I realized there is a simpler (and maybe better) way to reach the desired result (here using pdo extension):

$db = new PDO ("mysql:host=$hostname;dbname=$dbname", $user, $pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 

//No user defined variable here inside (safe)
$identifier=db->query("SELECT UUID();")->fetch()[0];

Having the identifier stored into a PHP variable, then I can treat every following query separately using pdo and prepared statements.

mysqli_multi_query: need to check single result?

2 Answers2