Best Practices: Database Abstraction Page 4

What about error checking ?

Say we ran the MySQL script again, and made a bit of a typo in the SQL statement – $sql = "SELECT * FROM demo2";

Warning: Supplied argument is not a valid MySQL result resource in c:tempallandbmysql.php on line 7

Messy. So we could add some error checking to the line that does the query:

$demoResult = mysql_query( $sql ) or die( $sql );

which now will output

SELECT * FROM demo2

Which means we have an error and we can go and debug it. Course if you’ve got a couple of inner joins happening you may not easily be able to work it out. And if I made a typo in one of the field names I was selecting:

$sql = “SELECT bogus_field FROM demo”;

I would get the same error message.

MySQL has a useful function called mysql_error() which will return the last error that was generated, so
changing the line:

$demoResult = mysql_query( $sql ) or die( $sql );

to

$demoResult = mysql_query( $sql ) or die( mysql_error() );

will now result in

Table ‘phptst.demo2’ doesn’t exist

in the first case and

Unknown column ‘bogus_field’ in ‘field list’

in the second.

So what does PEAR have to offer in the way of error reporting ?

The function DB::isError() will check to see whether the result that has been returned to you is an error or not. If it is an error you can use DB::errorMessage() to return a text description of the error that was generated. You need to pass DB::errorMessage() the return value from your function as an argument.

Rewriting the PEAR code to use the error checking:

<?php
if ( DB::isError( $demoResult = $db->query( $sql ) ) ) {
    echo DB::errorMessage($demoResult);
} else {
    while ($demoRow = $demoResult->fetchRow()) {
        echo $demoRow[2] . '<br>';
    }
} 
?>