The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

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, Error Handling), which allows you to control the output of the error message. Let's modify the basic connection code we saw earlier:

Example 3.10. 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:

Example 3.11. 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.

How do I add or modify data in my database?

Being able to fetch data from the database is a start, but how can you put it there in the first place?

Again, the answer is simple with PHP: use the mysql_query function combined with SQL commands INSERT and UPDATE. INSERT is used to create new rows in a table, while UPDATE is used to modify existing rows.

Inserting a Row

A simple INSERT, using the articles table defined at the start of this chapter, looks like this:

Example 3.12. 7.php (excerpt)      
     
// A query to INSERT data      
$sql = "INSERT INTO      
         articles      
       SET      
         title  = '$title',      
         body   = '$body',      
         author = '$author'";      
     
// Run the query, identifying the connection      
if (!$queryResource = mysql_query($sql, $dbConn)) {      
 trigger_error('Query error ' . mysql_error() . ' SQL: ' . $sql);      
}

Updating a Row

Before you can use an UPDATE query, you need to be able to identify which row(s) of the table to update. In this example, I've used a SELECT query to obtain the unique article_id value for the article entitled "How to insert data":

Example 3.13. 8.php (excerpt)      
     
// A query to select an article      
$sql = "SELECT article_id FROM articles      
       WHERE title='How to insert data'";      
     
if (!$queryResource = mysql_query($sql, $dbConn)) {      
 trigger_error('Query error ' . mysql_error() . ' SQL: ' . $sql);      
}      
     
// Fetch a single row from the result      
$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);      
     
// A new title      
$title = 'How to update data';      
     
$sql = "UPDATE      
         articles      
       SET      
         title='$title'      
       WHERE      
         article_id='" . $row['article_id'] . "'";      
     
if (!$queryResource = mysql_query($sql, $dbConn)) {      
 trigger_error('Query error ' . mysql_error() . ' SQL: ' . $sql);      
}

In the above example, we used the SELECT query to find the ID for the row we wanted to update.

In practical Web applications, the UPDATE might occur on a page which relies on input from the Web browser, after the user has entered the value(s) using an HTML form, for example. It is possible that strings in this data might contain apostrophes, which would break the SQL, and impact upon security. In light of this, make sure you read the section called "How do I solve database errors caused by quotes/apostrophes?", which covers SQL injection attacks.

Beware Global Updates
Be careful with UPDATE and remember to use a WHERE clause to indicate which rows to change.

This will update every row of the table!

Another Class Action

Using the MySQL class last seen in the section called "How do I fetch data from a table?", we can perform INSERT and UPDATE queries without any further modifications. Repeating the above examples using the class, we can first INSERT like this:

Example 3.14. 9.php (excerpt)      
     
// Connect to MySQL      
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);      
     
$title  = 'How to insert data';      
$body   = 'This is the body of the article';      
$author = 'HarryF';      
     
// A query to INSERT data      
$sql = "INSERT INTO      
         articles      
       SET      
         title  = '$title',      
         body   = '$body',      
         author = '$author'";      
     
$db->query($sql);      
     
if (!$db->isError()) {      
 echo 'INSERT successful';      
} else {      
 echo 'INSERT failed';      
}

We can UPDATE as follows:

Example 3.15. 10.php (excerpt)      
     
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);      
     
// A query to select an article      
$sql = "SELECT article_id FROM articles      
       WHERE title='How to insert data'";      
     
$result = $db->query($sql);      
     
$row = $result->fetch();      
     
// A new title      
$title = 'How to update data';      
     
$sql = "UPDATE      
         articles      
       SET      
         title='" . $title. "'      
       WHERE      
         article_id='" . $row['article_id'] . "'";      
     
$db->query($sql);      
     
if (!$db->isError()) {      
 echo 'UPDATE successful';      
} else {      
 echo 'UPDATE failed';      
}