Jump to content

how to handle errors in mysqli prepared statements efficiently


ajoo

Recommended Posts

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.

Link to comment
Share on other sites

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();
Link to comment
Share on other sites

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 by ajoo
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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
}
Link to comment
Share on other sites

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 by ajoo
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.