Recommended approach for error handling with PHP and MYSQL

Question

I am trying to capture database (MYSQL) errors in my PHP web application. Currently, I see that there are functions like mysqli_error(), mysqli_errno() for capturing the last occurred error. However, this still requires me to check for error occurrence using repeated if/else statements in my php code. You may check my code below to see what I mean. Is there a better approach to doing this? (or) Should I write my own code to raise exceptions and catch them in one single place? Also, does PDO raise 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 have created my own code to execute MySQL statements and raise exceptions if they fail, including specific exceptions for different causes of failure. One of the most helpful of these is when collisions occur, allowing me to use try..catch blocks and catch DatabaseCollisionExceptions, and handle those differently from other database exceptions.

What I found easiest for doing this was an MVC design pattern where every table was represented by a PHP class (models) which I could just assign member variables to and call a 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 isn't the only answer, it's just some ideas you might find helpful.

Answer 2

this still requires me to check for error occurrence using repeated if/else statements

How's that? You don't need to check for every possible error. I'd make just final check for the returned value and nothing else.

For what purpose do you use these nested conditions?

Answer 3

You may rewrite it in the following manner:

$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();
    }

Answer 4

I think exceptions are the best approach. PDO does throw exceptions you just need to set PDO::ERRORMODE_EXCEPTION when you create the object.