David David - 7 months ago 84
PHP Question

Locating mysqli_multi_query errors

Using a example from elsewhere on SO to better catch 'hiding' errors. While the code below will catch and return an error, is it possible to improve this to report for which query the error occurred?

With the code below, the output is:

Columns: 18
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRO inventory' at line 1


Code being tested:

$query = "SELECT * FROM orders WHERE location = 'IN' ORDER BY orderNum DESC LIMIT 20;";
$query .= "SELECT * FRO inventory"; // With error
$ord = array ( );
$invent = array ( );

if ( mysqli_multi_query ( $link, $query ) ) {
do {
// fetch results
if ( $result = mysqli_store_result ( $link ) ) {
echo 'Columns: ' . mysqli_field_count ( $link ) . "<br>";
while ( $row = mysqli_fetch_array ( $result, MYSQLI_ASSOC ) ) {
if ( count ( $row ) > 17 )
$orders[] = $row;
elseif ( count ( $row ) == 6 )
$inv[] = $row;
}
}
if ( !mysqli_more_results ( $link ) )
break;
if ( !mysqli_next_result ( $link ) ) {
// report error
echo 'Error: ' . mysqli_error ( $link );
break;
}
} while ( true );
mysqli_free_result ( $result );
}

Answer

To answer my own question and since the documentation is poor, here's a solution that hopefully will help others. What was missing is a way to catch an error on the 1st query. (The hidden actions of myqsqli_multi_query are difficult to understand.)

Now check for entries in $err array.

$q[1] = "SELECT * FROM orders WHERE location = 'IN' ORDER BY orderNum DESC LIMIT 20";
$q[2] = "SELECT * FROM inventory";
$ord = array ( );
$invent = array ( );
$err = array ( );
$c = 1;

if ( mysqli_multi_query ( $link, implode ( ';', $q ) ) ) {
    do {
        // fetch results
        if ( $result = mysqli_use_result ( $link ) )
            while ( $row = mysqli_fetch_array ( $result, MYSQLI_ASSOC ) ) {
                if ( count ( $row ) > 17 )
                    $orders[] = $row;
                elseif ( count ( $row ) == 6 )
                    $inv[] = $row;
            }
        }
        $c++;
        if ( !mysqli_more_results ( $link ) )
            break;
        if ( !mysqli_next_result ( $link ) || mysqli_errno ( $link ) ) {
            // report error
            $err[$c] = mysqli_error ( $link );
            break;
        }
    } while ( true );
    mysqli_free_result ( $result );
}
else
    $err[$c] = mysqli_error ( $link );

mysqli_close ( $link );