How to identify the query that caused the error us

Question

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_assoc($result)) {
                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 1

Here is an approach that will not only improve the quality of your error messages, it will improve the way you handle your result sets.

$q["Orders"]="SELECT * FROM orders WHERE location = 'IN' ORDER BY orderNum DESC LIMIT 20";
$q["Inventory"]="SELECT * FRO inventory";

if(!$link=mysqli_connect("host","user","pass","db")){
    echo "Failed to connect to MySQL: ",mysqli_connect_error();
}elseif(mysqli_multi_query($link,implode(';',$q))){
    do{
        $q_key=key($q);  // current query's key name (Orders or Inventory)
        if($result=mysqli_store_result($link)){   // if a result set... SELECTs do
            while($row=mysqli_fetch_assoc($result)){  // if one or more rows, iterate all
                $rows[$q_key][]=$row;
            }
            mysqli_free_result($result);
            echo "<div><pre>";  // <pre> is for easier array reading
                var_export($rows[$q_key]);
            echo "</pre></div>";
        }
    } while(next($q) && mysqli_more_results($link) && mysqli_next_result($link));
}
if($mysqli_error=mysqli_error($link)){  // check & declare variable in same step to avoid duplicate func call
    echo "<div style=\"color:red;\">Query Key = ",key($q),", Query = ",current($q),", Syntax Error = $mysqli_error</div>";
}

Error on first query: If your first query tries to access a table that doesn't exist in the nominated database like: ordersXYZ Array $rows will not exist, no var_export() will occur, and you will see this response:

Query Key = Orders, Query = SELECT * FROM ordersXYZ WHERE location='IN' ORDER BY orderNum DESC LIMIT 20, Syntax Error = Table '[someDB].ordersXYZ' doesn't exist

Error on second query: If your first query is successful, but your second query tries to access a non-existent table like: inventory2
$rows["Orders"] will hold the desired row data and will be var_export()'ed, $row["Inventory"] will not exist, and you will see this response:

Query Key = Inventory, Query = SELECT * FROM inventory2, Syntax Error = Table '[someDB].inventory2' doesn't exist

No errors: If both queries are error free, your $rows array will be filled with the desired data and var_export()'ed, and there will be no error response. With the queried data saved in $rows, you can access what you want from $rows["Orders"] and $rows["Inventory"].

---

Things to note:

1. You may notice that I am making variable declarations and conditional checks at the same time, this makes the code more DRY.

2. As my approach uses implode() with a semi-colon on the elseif line, be sure not to add a trailing semi-colon to your queries.

3. This set of queries always returns a result set because all are SELECT queries, if you have a mixed collection of queries that affect_rows, you may find some useful information at this link(https://stackoverflow.com/a/22469722/2943403).

4. mysqli_multi_query() will stop running queries as soon as there is an error. If you are expecting to catch "all" errors, you will discover that there never be more than one.

5. Writing conditional break points like in the OP's question and solution is not advisable. While custom break points may be rightly used in other circumstances, for this case the break points should be positioned inside of the while() statement of the do() block.

6. A query that returns zero rows will not cause a error message -- it just won't create any subarrays in $rows because the while() loop will not be entered.

7. By using the key() function, the OP's if/elseif condition that counts the columns in each resultset row can be avoided. This is better practice because running a condition on every iteration can become expensive in some cases. Notice that the array pointer is advanced inside of $q at the end of each do() iteration. This is an additional technique that you will not find on the php manual page; it allows key() to work as intended.

8. And, of course, the <div><pre>var_export()...</pre></div> lines can be removed from your working code -- it was purely for demonstration.

9. If you are going to run any more queries after this code block that reuse variables, be sure to clear all used variables so that residual data does not interfere. e.g. $mysqli_error=null; // clear errors & reset($q); // reset array pointer.

10. Take heed to this somewhat vague warning at your own discretion: http://php.net/manual/en/mysqli.use-result.php :

    One should not use mysqli_use_result() if a lot of processing on the client side is performed, since this will tie up the server and prevent other threads from updating any tables from which the data is being fetched.

11. Lastly and MOST IMPORTANTLY for security reasons, do not display query or query error information publicly -- you don't want sinister people to see this kind of feedback. Equally important, always protect your queries from injection hacks. If your queries include user-provided data, you need to filter/sanitize the data to death before using it in mysqli_multi_query(). In fact when dealing with user input, my very strong recommendation is to move away from mysqli_multi_query() and use either prepared statements or pdo for your database interactions for a higher level of security.

Answer 2

In your do loop, add a counter, each successful mysqli_next_result increment the counter. Once mysqli_next_result returns false, output the counter as well.

Answer 3

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);

Answer 4

This works for two queries.

If the error is in the first, the response for 1st query to PHP is the error message, and for the second (which won't fire), a message tattling on the first.

If the error is in the second, the first's response is returned and the 2nd gets the error message.

I'm using associative arrays and nixing array elements[0]. This adds ['Error'] key only if there is a relevant error.

Finally, I'm not the best PHPer, so it's up to you to fix what's ugly.

$query_nbr=0;

if (mysqli_multi_query($link,$query ))
{ 
  do
  { 
    unset($field_info) ;            $field_info = array() ;
    unset($field_names) ;           $field_names = array() ;
    unset($values) ;                $values = array(array()) ;

    if ($result = mysqli_store_result($link))
    { 
      $query_nbr += 1 ; 
      $rows_found = mysqli_num_rows($result);
      $fields_returned = mysqli_num_fields($result);
      $field_info = mysqli_fetch_fields($result);

      $field_nbr=0;
      foreach ($field_info as $fieldstuff)
      { $field_nbr +=1 ;
        $field_names[$field_nbr] = $fieldstuff->name ;
      }

      $now = date("D M j G:i:s T Y") ;

      if ($query_nbr == 1)
      { 
        $result_vector1 = array('when'=>$now) ;
        $result_vector1['nrows']=0;
        $result_vector1['nrows']=$rows_found ;
        $result_vector1['nfields']=$fields_returned ;
        $result_vector1['field_names']=$field_names ;
      }
      else 
      { 
        $result_vector2 = array('when2'=>$now) ;
        $result_vector2['nrows2']=0;
        $result_vector2['nrows2']=$rows_found ;
        $result_vector2['nfields2']=$fields_returned ;
        $result_vector2['field_names2']=$field_names ;
      }

      $row_nbr=0 ;
      while ($row = mysqli_fetch_array($result, MYSQLI_BOTH))
      { 
        $row_nbr++ ;
        for ($field_nbr=1;$field_nbr<=$fields_returned;$field_nbr++)
        {
          $values[$row_nbr][$field_names[$field_nbr]]    =$row[$field_nbr-1] ;
        }
      }
      mysqli_free_result($result) ;

      unset($values[0]) ;
      if ($query_nbr == 1)
      {$result_vector1['values']=$values ;}
      else
      {$result_vector2['values2']=$values ;}

    }  // EO if ($result = mysqli_store_result($link))

  } while (mysqli_more_results($link) && mysqli_next_result($link)) ;

}  // EO  if (mysqli_multi_query($link,$query ))
else 
{
  // This will be true if the 1st query failed
  if ($query_nbr == 0)
  { 
    $result_vector1['Error'] = "MySQL Error #:  ".mysqli_errno($link).":  ".mysqli_error($link) ;
    $result_vector2['Error'] = "MySQL Error in first query." ;
  }

}  // EO MySQL

//  Here we only made it through once, on the 2nd query
if ( $query_nbr == 1 && $nqueries == 2  && empty( $result_vector2 ) )
{
  $result_vector2['Error'] = "MySQL Error #:  ".mysqli_errno($link).":  ".mysqli_error($link) ;
}