MySQLi prepared statements error reporting

Question

I m trying to get my head around MySQli and I m confused by the error reporting  I am using the return value of the MySQLi  prepare  statement to detect errors when executing SQL  like this    stmt test     mysqliDatabaseConnection- gt stmt init    if  stmt test- gt prepare  INSERT INTO testtable VALUES  23 44 56         stmt test- gt execute      stmt test- gt close      else echo  Statement failed      stmt test- gt error     lt br gt       But  is the return value of the prepare statement only detecting if there is an error in the preperation of the SQL statement and not detecting execution errors  If so should I therefore change my execute line to flag errors as well like this   if  stmt test- gt execute     errorflag true    And then just to be safe should I also do the following after the statement has executed   if  stmt test- gt errno    errorflag true        Or was I OK to start with  and the return value on the MySQLi prepare  statement captures all errors associated with the complete execution of the query it defines   Thanks  C

User · Accepted Answer

Each method of mysqli can fail  You should test each return value  If one fails  think about whether it makes sense to continue with an object that is not in the state you expect it to be   Potentially not in a  quot safe quot  state  but I think that s not an issue here   Since only the error message for the last operation is stored per connection statement you might lose information about what caused the error if you continue after something went wrong  You might want to use that information to let the script decide whether to try again  only a temporary issue   change something or to bail out completely  and report a bug   And it makes debugging a lot easier   stmt    mysqli- gt prepare  quot INSERT INTO testtable VALUES         quot       prepare   can fail because of syntax errors  missing privileges       if   false    stmt          and since all the following operations need a valid ready statement object      it doesn t make sense to go on      you might want to use a more sophisticated mechanism than die        but s it s only an example   die  prepare   failed      htmlspecialchars  mysqli- gt error        rc    stmt- gt bind param  iii    x   y   z      bind param   can fail because the number of parameter doesn t match the placeholders in the statement    or there s a type conflict     or      if   false    rc          again execute   is useless if you can t bind the parameters  Bail out somehow    die  bind param   failed      htmlspecialchars  stmt- gt error        rc    stmt- gt execute       execute   can fail for various reasons  And may it be as stupid as someone tripping over the network cable    2006  quot server gone away quot  is always an option if   false    rc       die  execute   failed      htmlspecialchars  stmt- gt error        stmt- gt close     Just a few notes six years later    The mysqli extension is perfectly capable of reporting operations that result in an  mysqli  error code other than 0 via exceptions  see mysqli driver   report mode  die   is really  really crude and I wouldn t use it even for examples like this one anymore  So please  only take away the fact that each and every  mysql  operation can fail for a number of reasons  even if the exact same thing went well a thousand times before

User · Answer

Completeness  You need to check both  mysqli and  statement  If they are false  you need to output  mysqli- gt error or  statement- gt error respectively   Efficiency  For simple scripts that may terminate  I use simple one-liners that trigger a PHP error with the message  For a more complex application  an error warning system should be activated instead  for example by throwing an exception   Usage example 1  Simple script    This is in a simple command line script  mysqli   new mysqli  localhost    buzUser    buzPassword     q    UPDATE foo SET bar 1     statement    mysqli- gt prepare  q   or trigger error  mysqli- gt error  E USER ERROR    statement- gt execute   or trigger error  statement- gt error  E USER ERROR     Usage example 2  Application    This is part of an application class FuzDatabaseException extends Exception      class Foo     public  mysqli    public function   construct mysqli  mysqli         this- gt mysqli    mysqli        public function updateBar          q    UPDATE foo SET bar 1        statement    this- gt mysqli- gt prepare  q       if    statement          throw new FuzDatabaseException  mysqli- gt error              if    statement- gt execute            throw new FuzDatabaseException  statement- gt error                 foo   new Foo new mysqli  localhost   buzUser   buzPassword     try      foo- gt updateBar      catch  FuzDatabaseException  e     msg    e- gt getMessage         Now send warning emails  write log

User · Answer

Not sure if this answers your question or not  Sorry if not  To get the error reported from the mysql database about your query you need to use your connection object as the focus   so   echo  mysqliDatabaseConnection- gt error   would echo the error being sent from mysql about your query   Hope that helps

[php] MySQLi prepared statements error reporting

Just a few notes six years later...

Examples related to php

Examples related to mysql

Examples related to mysqli

Examples related to prepared-statement