How do I resolve errors in my SQL

How do I resolve errors in my SQL

queries?

If something goes wrong when you try to deal with PHP and SQL together, it's

often difficult to find the cause. The trick is to get PHP to tell you where the

problem is, bearing in mind that you must be able to hide this information from

visitors when the site goes live.

PHP provides the mysql_error function, which returns a detailed error message

from the last MySQL operation performed.

It's best used in conjunction with the trigger_error function (which will be

discussed in more detail in Chapter 10), which allows you to control the output

of the error message. Let's modify the basic connection code we saw earlier:

File: 6.php (excerpt)

// Make connection to MySQL server

if (!$dbConn = mysql_connect($host, $dbUser, $dbPass)) {

trigger_error('Could not connect to server: ' . mysql_error());

die();

}

// Select the database

if (!mysql_select_db($dbName)) {

trigger_error('Could not select database: ' . mysql_error());

die();

}

The same approach can be used with queries:

File: 6.php (excerpt)

// A query to select all articles

$sql = "SELECT * FROM articles ORDER BY title";

// Run the query, identifying the connection

if (!$queryResource = mysql_query($sql, $dbConn)) {

trigger_error('Query error ' . mysql_error() . ' SQL: ' . $sql);

}

It can be a good idea to return the complete query itself, as we've done in the

above example, particularly when you've built it using PHP variables. This allows

you to see exactly what query was performed and, if necessary, execute it directly

against MySQL to identify exactly where it went wrong.

The MySQL class discussed above will automatically use mysql_error and

trigger_error should it encounter a problem.