Recommended Error Handling with PHP and MYSQL

Question

I am trying to capture database errors (MYSQL) in my PHP web application. Currently, I see that there are functions like mysqli_error (), mysqli_errno () to catch the last error that occurred. However, this still requires me to check for errors using duplicate if / else statements in my PHP code. You can check my code below to understand what I mean. Is there a better approach to this? (or) Do I have to write my own code to raise exceptions and catch them in one place? Also, does PDO make exceptions? Thanks.

function db_userexists($name, $pwd, &$dbErr) { $bUserExists = false; $uid = 0; $dbErr = ''; $db = new mysqli(SERVER, USER, PASSWORD, DB); if (!mysqli_connect_errno()) { $query = "select uid from user where uname = ? and pwd = ?"; $stmt = $db->prepare($query); if ($stmt) { if ($stmt->bind_param("ss", $name, $pwd)) { if ($stmt->bind_result($uid)) { if ($stmt->execute()) { if ($stmt->fetch()) { if ($uid) $bUserExists = true; } } } } if (!$bUserExists) $dbErr = $db->error(); $stmt->close(); } if (!$bUserExists) $dbErr = $db->error(); $db->close(); } else { $dbErr = mysqli_connect_error(); } return $bUserExists; } 

Answer 1

I think exceptions are the best approach. PDO throws exceptions, you just need to set PDO::ERRORMODE_EXCEPTION when creating the object.

Answer 2

I created my own code to execute MySQL statements and raise exceptions if they fail, including specific exceptions for various reasons for the failure. One of the most useful of these is a collision, which allows you to use try..catch and catch DatabaseCollisionException s blocks and handle them differently from other database exceptions.

What I found easiest for this was the MVC design pattern, where each table was represented by a class (models) of PHP, which I could simply assign as member variables and call the save method to save to the database, similar to

 try { $user = new User(); $user->username = 'bob'; $user->setPassword($_POST['password'); // Could do some SHA1 functions or whatever $user->save } catch(DatabaseCollisionException $ex) { displayMessage("Sorry, that username is in use"); } catch(DatabaseException $ex) { displayMessage("Sorry, a database error occured: ".$ex->message()); } catch(Exception $ex) { displayMessage("Sorry, an error occured: ".$ex->message()); } 

For more information on similar design patterns, see

• http://www.phpactiverecord.org/
• http://book.cakephp.org/view/17/Model-Extensions-Behaviors
• http://www.derivante.com/2009/05/14/php-activerecord-with-php-53/

Of course, this is not the only answer, these are just some ideas that may come in handy.

Answer 3

it still requires me to check for an error repeating if / else statements

Like this? You do not need to check all possible errors. I would only do a final check on the return value and nothing more.

For what purpose do you use these nested conditions?

Answer 4

You can rewrite it as follows:

$ bUserExists = false; $ uid = false;

 if (!mysqli_connect_errno()) { if($stmt = $db->prepare("select uid from user where uname = ? and pwd = ?") ->bind_param("ss", $name, $pwd)) { $stmt->execute(); $stmt->bind_result($uid); $stmt->fetch(); $stmt->close(); } if ($uid) $bUserExists = true; $db->close(); } else { $dbErr = mysqli_connect_error(); }