how to handle errors in mysqli prepared statements efficiently

[ajoo]

Hi all !

 

 

Here is a small piece of code that I wrote to Select from a DB:-

			$query = "SELECT Id, User, Pass, FROM $table WHERE User = ?";  
			$stmt = $con->prepare($query);
			$stmt->bind_param('s',$user);
			$stmt->execute();
			$stmt->bind_result($db_id,$db_user,$db_pw);
			$stmt->fetch();
                        ...

Each of these statements warrant that they be checked for failure and for possible exceptions since each of these can fail. 

 

However such similar blocks of code may be present at 100's of places in a large application and so checking for failure after each line of code would be make it a very lengthy & cumbersome procedure.

 

I was wondering if there is a simpler, elegant way to handle these kind of failures or exceptions. And that's what I wish to ask. 

 

Thanks loads everyone.

[fastsol]

Use a db wrapper class that has the functionality you want built in to it.  This way every time you run a query it goes through the class and will automatically do the error checking.

[Barand]

or you could

$mysqli_driver = new mysqli_driver();
$mysqli_driver->report_mode = MYSQLI_REPORT_ERROR;

$con = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);

$query = "SELECT Id, User, Pass, FROM $table WHERE User = ?";
$stmt = $con->prepare($query);
$stmt->bind_param('s',$user);
$stmt->execute();
$stmt->bind_result($db_id,$db_user,$db_pw);
$stmt->fetch();

[ajoo]

Hi Guru Barand and Fastsol,

 

Thanks for the response. I would request you both to please elaborate the explanation of the two different solutions suggested. 

 

Guru Barand, would doing what you suggested halt / kill/ the further execution of the script if 

$query = "SELECT Id, User, Pass, FROM $table WHERE User = ?";

 had, say, a syntax error?

 

In case  my question is not clear I'll restate it. What I am looking for is a simple way that would test each line in the block of code. In fact such that the next line would execute only if the previous one executed successfully or else it would quit further processing with an error.  

 

Kindly clarify.

Grateful for the reply.

Edited February 28, 2015 by ajoo

[Barand]

Is the concept of "testing" alien to you?

[ajoo]

No it's not Guru Barand. In fact after posting the question I am doing the same very thing. Just thought I would get a nod from you and be sure.

 

Thanks. Will revert with the result and any further issues.

[ajoo]

Hi Guru Barand, 

 

Yes I have tested out the lines of code as suggested by you and yes they give out warnings in addition to the default php error reporting. The warnings are more precise about the errors.  So I guess this is great during testing. However in production all error reporting would have to be turned off. 

 

Coming back to the original block of code,

$query = "SELECT Id, User, Pass, FROM $table WHERE User = ?";
$stmt = $con->prepare($query);
$stmt->bind_param('s',$user);
$stmt->execute();
$stmt->bind_result($db_id,$db_user,$db_pw);
$stmt->fetch(); 

I would like to ask another question and that is How fit/good would be these lines of code in production? Is there any improvement that I could make to make this code more robust?

 

Thanks loads

[Barand]

In production you would turn off error display and turn on error logging instead.

[ajoo]

Yes Guru Barand, I agree on that but otherwise is the code block above good enough to be put in production code as is ?

 

I have also seen code such as 

$query = "SELECT Id, User, Pass, FROM $table WHERE User = ?";
if($stmt = $con->prepare($query))
{
  $stmt->bind_param('s',$user);
  if($stmt->execute())
   {
     $stmt->bind_result($db_id,$db_user,$db_pw);
     $stmt->fetch();
   } 
}

along with a comment that said since each of the statements could fail , they should all be tested. I think that that is most cumbersome especially if there were a large number of queries. Hence my confusion. Kindly suggest what's the way to go.

 

Thank you very much for all your replies. Much obliged. 

[Barand]

Better to assume that if something could wrong then, at some time, it will and handle such errors gracefully in your code.

 

if you set

report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

then exceptions will be thrown

try {
  $query = "SELECT Id, User, Pass, FROM $table WHERE User = ?";
  $stmt = $con->prepare($query);
  $stmt->bind_param('s',$user);
  $stmt->execute();
  $stmt->bind_result($db_id,$db_user,$db_pw);
  $stmt->fetch(); 
} catch (Exception $e) {
     // handle errors
}

[ajoo]

HI ! Thanks for the reply. That pretty much takes care of the issue except that it allows Warnings to go through without being caught by the Catch block.

 

For e.g. if there is a mismatch in the number of parameters in the line 

$stmt->bind_result($db_id,$db_user,$db_pw);

a warning is issued about the mismatch by php but the execution continues. This I found is solved by using MYSQLI_REPORT_ALL.  This ofcourse is just an observation. I am not saying that we should use MYSQLI_REPORT_ALL because such errors can be found and removed while in the development stage.   

 

Further BOTH ALL & STRICT fails to catch an exception if there is no connection to the server.  I included the include_once 'dataconnect.php' within the catch block but it failed to catch the exception. Instead php echoed out that there was an undefined $connection variable.

 

How can I have the try block catch this final ( I think n hope) error.

 

Thank you so much. 

Edited March 1, 2015 by ajoo