Sunday, 23 September 2012

Dealing with SQL Errors in PHP


An associate of mine was asking how to track SQL errors in PHP, and thinking that this was a common activity I turned to Google for a quick, simple, and encompassing example only to find that most solutions were over-whelming and insufficient.


I resorted to making my own set of suggestions:

The easiest way to evaluate errors in SQL is to check for potential errors prior to executing the SQL statement by testing input variables or even using test SQL statements...

Take a generic “Model” class with an Insert Function:

Model->InsertFunction(var1, var2, var3)

Here knowing var1 is constrained to be unique, check if var1 exists in database with a select statement: if result is negative, proceed with Insert. Otherwise, return null and populate the $GLOBALS[‘ERROR’] variable with “var1 already exists in database


Consider This: Creating a global variable called “ERROR” instead of throwing exceptions. This prevents yourself from ending your script prematurely, and allows failures to be utilized as desired. This may serve beneficial as coding with multiple developers does not become a practice of debugging script ending errors that derive from code you do not have access to.

Most issues are handled in the “controller” level, but database specific issues are done in the model area including maintaining relationships, unique constraints, ect...


The alternative error checking mechanism for SQL Statements in PHP, is to check for the error upon execution.

Best way to demonstrate this is to outline an example (works in PERL as well)


$value = mysql_query($your_query)
    or die("A MySQL error has occurred.<br />
        Your Query: " . $your_query . "<br /> 
        Error: (" . mysql_errno() . ") " . mysql_error());


Again, the code here also “Dies” ending the script prematurely. For the same reasons above, it would probably be better to execute a function as an alternative. Such as:

$value = mysql_query($your_query)
    or CreateError("A MySQL error has occurred.<br />
            Your Query: " . $your_query . "<br /> 
            Error: (" . mysql_errno() . ") " . mysql_error());
if $GLOBALS['hasERROR'] { return null };

You may have noted that the global variable tested for was “hasERROR” rather than “ERROR” which is a combination of Global variables I tend to use when evaluating database errors. This combination allows you to add facilities to your database needs, such as warnings, error lists, ect.


In this simple example the “CreateError” function I had in mind is written as such:


function CreateError($errorString) {
    $GLOBALS['ERROR'] = $errorString;
    $GLOBALS['hasERROR'] = true;
    return true;


For best results, use both!

The two previous methods in combination are the best way to evaluate potential database errors... The first method will let you manipulate how the database is used based on what the specific problem could be... for instance, An existing username is found for a new user registering, a detailed error gets returned to the controller, allowing the controller to manipulate the error information explaining that the “Username Already exists, pick another one”


The second method takes care of all circumstances of unforeseen errors...

And a generic “There was an error processing your request” would be shown, without the website crashing and showing some unsightly PHP error to the user.


Page Hits