By: Harry Fuecks

January 19th, 2004

Reader Rating: 8.5

--Kevin Yank

In the "old days" of the Internet, most Web pages were nothing more than text files containing HTML. When people surfed to your site, your Web server simply made the file available to their browser, which parsed the contents and rendered something a human being could read. This approach was fine to start with, but as Websites grew and issues such as design and navigation became more important, developers realized that maintaining hundreds of HTML files was going to be a massive headache. To solve this problem, it became popular to separate variable content (articles, news items, etc.) from the static elements of the site—its design and layout.

Using a database as a repository to store variable content, a server side language such as PHP performs the task of fetching the data and placing it within a uniform "framework," the design and layout elements being reused. This means that modifying the overall look and feel of a site can be handled as a separate task from the addition or maintenance of content. Suddenly, running a Website is no longer a task that consumes a developer's every waking hour.

PHP supports all relational databases worth mentioning, including those commonly used in large companies, such as Oracle, IBM's DB2 and Microsoft's SQL Server. The two most noteworthy open source alternatives are PostgreSQL and MySQL. Although PostgreSQL is arguably the better database, in that it supports more of the features that are common to relational databases, MySQL is better supported on Windows, and is a popular choice among Web hosts that provide support for PHP. These factors combine to make PHP and MySQL a very popular combination. This book is geared to the use of MySQL with PHP but it's important to remember that there are alternatives with full support for features such as stored procedures, triggers and constraints, many of which become important for applications with complex data structures.

This chapter covers all the common operations PHP developers have to perform when working with MySQL, from retrieving and modifying data, to searching and backing up a database. The examples focus on using a single table, so no discussion is made of table relationships here. For a full discussion of table relationships, see Kevin Yank's Build Your Own Database Driven Website Using PHP & MySQL (ISBN 0-9579218-1-0), or see an example of them in practice when we deal with user groups in Chapter 1, Access Control.

The examples used here work with a sample database called sitepoint, which contains the following single table:

Example 3.1. articles.sql

CREATE TABLE articles (
 article_id INT(11)       NOT NULL AUTO_INCREMENT,
 title      VARCHAR(255)  NOT NULL DEFAULT '',
 intro      TEXT          NOT NULL,
 body       TEXT          NOT NULL,
 author     VARCHAR(255)  NOT NULL DEFAULT '',
 published  VARCHAR(11)   DEFAULT NULL,
 public     ENUM('0','1') NOT NULL DEFAULT '0',
 PRIMARY KEY (article_id),
 FULLTEXT KEY art_search (title, body, author)
)

A query to construct this table along with some sample data is available in the code archive, contained in the file sql/articles.sql. The table will be used for examples in later chapters of the book.

How do I access a MySQL database?

Connecting to MySQL with PHP couldn't be easier. It's essentially a two–step process; first connect to the MySQL database server itself, then inform MySQL of the database you want to connect to.

A Basic Connection

Here is a MySQL database connection in its simplest form:

Example 3.2. 1.php  
 
<?php  
$host   = 'localhost'; // Hostname of MySQL server  
$dbUser = 'harryf';    // Username for MySQL  
$dbPass = 'secret';    // Password for user  
$dbName = 'sitepoint'; // Database name  
 
// Make connection to MySQL server  
if (!$dbConn = mysql_connect($host, $dbUser, $dbPass)) {  
 die('Could not connect to server');  
}  
 
// Select the database  
if (!mysql_select_db($dbName, $dbConn)) {  
 die('Could not select database');  
}  
 
echo 'Connection successful!';  
// ... some code here using MySQL  
 
// Close the connection when finished  
mysql_close($dbConn);  
?>

It's important to remember that MySQL is a separate server program, much like Apache. Both servers may run on the same physical computer (hence our use of $host = 'localhost'; in the above example) but it's also possible to connect to MySQL on a remote computer, for example $host = 'anothercomputer.com';. To make matters a little more interesting, MySQL also has its own port number, which by default is 3306. PHP assumes that 3306 will be the port number but should you need to use a different one, all you need is $host = 'anothercomputer.com:4321';.

The other conceptual hurdle lies in understanding that a single MySQL server may provide access to many databases, which is why you need to select your database in PHP after connecting to the server.

Returning to the code above, there are a few things to note. First, I've placed in variables the values I need in order to connect to MySQL. This simply makes our lives easier; it's common to store this kind of information in separate files that are included in every PHP script, making it possible to change many scripts at one time. We'll be looking at further tricks we can employ to make life easier in a moment.

The mysql_connect function does the work of connecting to a MySQL server. The value it returns is either a link identifier (a value supplied by PHP to identify the connection), or FALSE, meaning the connection failed.

if (!$dbConn = mysql_connect($host, $dbUser, $dbPass)) {  
 die('Could not connect to server');  
}

This if statement asks the question "Did I successfully connect to the MySQL server?" If not, it uses die to terminate the script.

Next, we've selected the database we want with mysql_select_db, using the same if statement technique:

if (!mysql_select_db($dbName, $dbConn)) {

Note that we provided the variable containing the link identifier as the second argument to mysql_select_db. We wouldn't usually need to do this (the argument is optional), but when a complex script juggles multiple database connections, this method can help ensure PHP knows which you're referring to.

Finally, we've used mysql_close to disconnect from the server again:

mysql_close($dbConn);

This occurs at the bottom of the script, once we've run some imaginary PHP code that used the connection. Closing the connection is generally optional—PHP automatically closes any connections after the script finishes.

Note that connections made with mysql_pconnect are different. This function establishes a persistent connection to the database to be reused by multiple PHP scripts. Using a persistent connection makes your scripts slightly faster, as PHP no longer has to reconnect each time, but speed comes at a price: if your Website runs on a shared server, persistent connections may monopolize that server, resulting in other sites being unable to connect at times. In such environments, it's typical to either avoid mysql_pconnect, or configure MySQL so that connections are terminated the moment they stop doing anything, using a short connection timeout value.

Reusable Code

You've just seen the most simplistic way to connect to MySQL. It's often more useful, however, to "package" the above code in a function or a class so it can be reused.

As a function we could have:

Example 3.3. 2.php  
 
<?php  
function &connectToDb($host, $dbUser, $dbPass, $dbName)  
{  
 // Make connection to MySQL server  
 if (!$dbConn = @mysql_connect($host, $dbUser, $dbPass)) {  
   return false;  
 }  
 
 // Select the database  
 if (!@mysql_select_db($dbName)) {  
   return false;  
 }  
 
 return $dbConn;  
}  
 
$host   = 'localhost'; // Hostname of MySQL server  
$dbUser = 'harryf';    // Username for MySQL  
$dbPass = 'secret';    // Password for user  
$dbName = 'sitepoint'; // Database name  
 
$dbConn = &connectToDb($host, $dbUser, $dbPass, $dbName);  
?>

This reduces the process of connecting to MySQL and selecting a database to a single line (two if you count the include statement, which would point to a separate file containing the connectToDb function):

$dbConn = &connectToDb($host, $dbUser, $dbPass, $dbName);

Note that we've used the reference operator &. This operator and the role it plays were covered in detail in Chapter 2, Object Oriented PHP.

Be Lazy: Write Good Code!

Scientists have now conclusively proven that knowledge of PHP is inversely proportional to free time but directly proportional to hair loss. The only way to prevent these effects is to learn how to write scalable, maintainable, and reusable code as early as possible. Taking advantage of classes and object orientation in PHP is a big step in the right direction. As a PHP developer, laziness is a virtue.

Going a step further, we can wrap this code in a class:

Example 3.4. Database/MySQL.php (in SPLIB) (excerpt)  
 
/**  
* MySQL Database Connection Class  
* @access public  
* @package SPLIB  
*/  
class MySQL {  
 /**  
  * MySQL server hostname  
  * @access private  
  * @var string  
  */  
 var $host;  
 
 /**  
  * MySQL username  
  * @access private  
  * @var string  
  */  
 var $dbUser;  
 
 /**  
  * MySQL user's password  
  * @access private  
  * @var string  
  */  
 var $dbPass;  
 
 /**  
  * Name of database to use  
  * @access private  
  * @var string  
  */  
 var $dbName;  
 
 /**  
  * MySQL Resource link identifier stored here  
  * @access private  
  * @var string  
  */  
 var $dbConn;  
 
 /**  
  * Stores error messages for connection errors  
  * @access private  
  * @var string  
  */  
 var $connectError;  
 
 /**  
  * MySQL constructor  
  * @param string host (MySQL server hostname)  
  * @param string dbUser (MySQL User Name)  
  * @param string dbPass (MySQL User Password)  
  * @param string dbName (Database to select)  
  * @access public  
  */  
 function MySQL($host, $dbUser, $dbPass, $dbName)  
 {  
   $this->host = $host;  
   $this->dbUser = $dbUser;  
   $this->dbPass = $dbPass;  
   $this->dbName = $dbName;  
   $this->connectToDb();  
 }  
 
 /**  
  * Establishes connection to MySQL and selects a database  
  * @return void  
  * @access private  
  */  
 function connectToDb()  
 {  
   // Make connection to MySQL server  
   if (!$this->dbConn = @mysql_connect($this->host,  
       $this->dbUser, $this->dbPass)) {  
     trigger_error('Could not connect to server');  
     $this->connectError = true;  
   // Select database  
   } else if (!@mysql_select_db($this->dbName,$this->dbConn)) {  
     trigger_error('Could not select database');  
     $this->connectError = true;  
   }  
 }  
 
 /**  
  * Checks for MySQL errors  
  * @return boolean  
  * @access public  
  */  
 function isError()  
 {  
   if ($this->connectError) {  
     return true;  
   }  
   $error = mysql_error($this->dbConn);  
   if (empty($error)) {  
     return false;  
   } else {  
     return true;  
   }  
 }

Now that may seem pretty overwhelming, but what's most important is not how the class itself is coded (the trigger_error function will be discussed in the section called "How do I resolve errors in my SQL queries?" later in this chapter), but how you use it.

What's most important is that the task of connecting to MySQL is now reduced to the following:

Example 3.5. 3.php  
 
<?php  
// Include the MySQL class  
require_once 'Database/MySQL.php';  
 
$host   = 'localhost'; // Hostname of MySQL server  
$dbUser = 'harryf';    // Username for MySQL  
$dbPass = 'secret';    // Password for user  
$dbName = 'sitepoint'; // Database name  
 
// Connect to MySQL  
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);  
?>

The point of using a class here is to get some practice using PHP's object model to deal with common tasks. If you're new to object oriented programming with PHP, the most important thing to remember at this stage is that you don't need to understand all the code you find in a class to be able to use it in your code.

We'll be making use of this class and others throughout the book to illustrate how object oriented programming aids the reuse of code and can save time when you're developing applications.

How do I fetch data from a table?

Being connected to a database is nice, sure. But what good is it if we can't get anything from it?

There are a number of ways to fetch data from MySQL, but the most widely used is probably mysql_fetch_array in conjunction with mysql_query.

We just need to add a little more to the connectToDb function we saw in the section called "How do I access a MySQL database?" to fetch data from this table:

Example 3.6. 4.php    
   
// Connect to MySQL    
$dbConn = &connectToDb($host, $dbUser, $dbPass, $dbName);    
   
// A query to select all articles    
$sql = "SELECT * FROM articles ORDER BY title";    
   
// Run the query, identifying the connection    
$queryResource = mysql_query($sql, $dbConn);    
   
// Fetch rows from MySQL one at a time    
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {    
 echo 'Title: '  . $row['title']  . '<br />';    
 echo 'Author: ' . $row['author'] . '<br />';    
 echo 'Body: '   . $row['body']   . '<br />';    
}

Essentially, there are three steps to getting to your data:

1. First, place the necessary SQL query in a string (if you are unfamiliar with Structured Query Language (SQL), I'll cover the basics throughout this chapter. For a more complete treatment, however, refer to Build Your Own Database Driven Website Using PHP & MySQL, 2nd Edition (ISBN 0–9579218–1–0)) like so:

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

   It's handy to keep it in a separate variable, as when we get into writing more complex queries and something goes wrong, we can double-check our query with this one-liner:

   echo $sql;

2. Next, tell MySQL to perform the query:

   $queryResource = mysql_query($sql, $dbConn);

   This can be confusing at first. When you tell MySQL to perform a query, it doesn't immediately give you back the results. Instead, it holds the results in memory until you tell it what to do next. PHP keeps track of the results with a resource identifier, which is what you get back from the mysql_query function. In the code above, we've stored the identifier in $queryResource.

3. Finally, use mysql_fetch_array to fetch one row at time from the set of results:

   while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC))

   This places each row of the results in turn in the variable $row. Each of these rows will be represented by an array. By using the additional argument MYSQL_ASSOC with mysql_fetch_array, we've told the function to give us an array in which the keys correspond to column names in the table. If you omit the MYSQL_ASSOC argument, each column will appear twice in the array: once with a numerical index (i.e. $row[0], $row[1], etc.), and once with a string index (i.e. $row['title'], $row['author'], etc.). While this doesn't usually cause a problem, specifying the type of array value you want will speed things up slightly.

Using a while loop, as shown above, is a common way to process each row of the result set in turn. The loop effectively says, "Keep fetching rows from MySQL until I can't get any more", with the body of the loop processing the rows as they're fetched.

Forego Buffering on Large Queries

For large queries (that is, queries that produce large result sets), you can improve performance dramatically by telling PHP not to buffer the results of the query. When a query is buffered, the entire result set is retrieved from MySQL and stored in memory before your script is allowed to proceed. An unbuffered query, on the other hand, lets MySQL hold onto the results until you request them, one row at a time (e.g. with mysql_fetch_array). Not only does this allow your script to continue running while MySQL performs the query, it also saves PHP from having to store all of the rows in memory at once.

Of course, all good things come at a price—with unbuffered queries you can no longer use the mysql_num_rows function to count the number of rows. Obviously, as PHP doesn't keep a copy of the complete result set, it is unable to count the rows it contains! You also must fetch all rows in the result set from MySQL before you can make another query.

Although other functions exist for getting rows and cells from query results, like mysql_fetch_object and mysql_result, you can achieve more or less the same things with just mysql_fetch_array, and the consistency may help keep your code simple.

Fetching with Classes

Now that you're happy with the basics of fetching data from MySQL, it's time to build some more on the MySQL class from the last solution.

First, let's add a method to run queries from the class:

Example 3.7. Database/MySQL.php (in SPLIB) (excerpt)    
   
 /**    
  * Returns an instance of MySQLResult to fetch rows with    
  * @param $sql string the database query to run    
  * @return MySQLResult    
  * @access public    
  */    
 function &query($sql)    
 {    
   if (!$queryResource = mysql_query($sql, $this->dbConn)) {    
     trigger_error('Query failed: ' . mysql_error($this->dbConn)    
                   . ' SQL: ' . $sql);    
     return new MySQLResult($this, $queryResource);    
   }    
 }

What this new method does is accept a variable containing an SQL statement, run it, then build a new object from another class, MySQLResult (described below). It then returns this object to the point where query was called.

Here's the code for that new class, MySQLResult:

Example 3.8. Database/MySQL.php (in SPLIB) (excerpt)    
   
/**    
* MySQLResult Data Fetching Class    
* @access public    
* @package SPLIB    
*/    
class MySQLResult {    
 /**    
  * Instance of MySQL providing database connection    
  * @access private    
  * @var MySQL    
  */    
 var $mysql;    
   
 /**    
  * Query resource    
  * @access private    
  * @var resource    
  */    
 var $query;    
   
 /**    
  * MySQLResult constructor    
  * @param object mysql   (instance of MySQL class)    
  * @param resource query (MySQL query resource)    
  * @access public    
  */    
 function MySQLResult(&$mysql, $query)    
 {    
   $this->mysql = &$mysql;    
   $this->query = $query;    
 }    
   
 /**    
  * Fetches a row from the result    
  * @return array    
  * @access public    
  */    
 function fetch()    
 {    
   if ($row = mysql_fetch_array($this->query, MYSQL_ASSOC)) {    
     return $row;    
   } else if ( $this->size() > 0 ) {    
     mysql_data_seek($this->query, 0);    
     return false;    
   } else {    
     return false;    
   }    
 }    
   
 /**    
  * Checks for MySQL errors    
  * @return boolean    
  * @access public    
  */    
 function isError()    
 {    
   return $this->mysql->isError();    
 }    
}

Now, hold your breath just a little longer until you've seen what using these classes is like:

Example 3.9. 5.php    
   
<?php    
// Include the MySQL class    
require_once 'Database/MySQL.php';    
   
$host   = 'localhost'; // Hostname of MySQL server    
$dbUser = 'harryf';    // Username for MySQL    
$dbPass = 'secret';    // Password for user    
$dbName = 'sitepoint'; // Database name    
   
// Connect to MySQL    
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);    
   
$sql = "SELECT * FROM articles ORDER BY title";    
   
// Perform a query getting back a MySQLResult object    
$result = $db->query($sql);    
   
// Iterate through the results    
while ($row = $result->fetch()) {    
 echo 'Title: '  . $row['title']  . '<br />';    
 echo 'Author: ' . $row['author'] . '<br />';    
 echo 'Body: '   . $row['body']   . '<br />';    
}    
?>

If you're not used to object oriented programming, this may seem very confusing, but what's most important is to concentrate on how you can use the classes, rather than the detail hidden inside them. That's one of the joys of object oriented programming, once you get used to it. The code can get very complex behind the scenes, but all you need to concern yourself with is the simple "interface" (API) with which your code uses the class.

About APIs

It's common to hear the term API mentioned around classes. API stands for Application Programming Interface. What it refers to is the set of methods that act as "doors" to the functionality contained within a class. A well-designed API will allow the developer of the class to make radical changes behind the scenes without breaking any of the code that uses the class.

Compare using the MySQL classes with the earlier procedural code; it should be easy to see the similarities. Given that it's so similar, you may ask, "Why not stick to plain, procedural PHP?" Well, in this case, it hides many of the details associated with performing the query. Tasks like managing the connection, catching errors, and deciding what format to get the query results in are all handled behind the scenes by the class. Classes also make the implementation of global modifications (such as switching from MySQL to PostgreSQL) relatively painless (i.e. you could just switch to a PostgreSQL class that provided the same API).

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';      
}

How do I solve database errors caused by quotes/apostrophes?

Consider the following SQL statement:

INSERT INTO articles SET title='The PHP Anthology';

Perhaps the PHP script that made this query contained something like this:

<?php      
$title = "The PHP Anthology";      
     
$sql = "INSERT INTO articles SET title='$title';";      
     
$result = mysql_query($sql, $dbConn);      
?>

No problem so far, but look what happens if we change the title:

$title = "PHP's Greatest Hits";

Notice the apostrophe in the title? When we place this in the SQL statement, the query MySQL receives will be as follows:

INSERT INTO articles SET title='PHP's Greatest Hits';

See the problem? When MySQL reads that statement, it will only get as far as this:

INSERT INTO articles SET title='PHP'

The rest of the statement will cause a syntax error and the query will fail. It's easy enough to avoid this problem when you write the title yourself, but what happens when your script gets the value from user input?

The Great Escape

The solution is to escape the apostrophe character by adding a backslash before the apostrophe. The following query, for example, will work:

INSERT INTO articles SET title='PHP\'s Greatest Hits';

Backslashes and the ANSI SQL Standard

Note that using the backslash as an escape character is not standard ANSI SQL. If MySQL is the only database you'll ever use, the backslash may be acceptable, but the same SQL statement run on another database may well fail. According to ANSI SQL we should escape apostrophes with another single apostrophe:

INSERT INTO articles SET title='PHP''s Greatest Hits';

The question is, how do we make sure all our apostrophes are escaped when we build a query on the fly in PHP? Dealing with this situation has become rather confusing due to the number of alternative solutions.

First we have the php.ini setting magic_quotes_gpc. Magic quotes is a feature of PHP which, when turned on, automatically escapes single and double quotes, as well as backslashes and null characters found in incoming GET, POST and cookie variables, by adding backslashes to the strings. This may sound great, but in practice it quickly makes for trouble, typically where forms are involved.

Say you have a form which is used for editing articles. Your script takes the text the user enters and inserts it into MySQL. Now, if the user fails to complete some important field, you might want to re-display the details that have been entered in the form so far. With magic quotes on you'd have to strip out all the slashes it added to the values (with PHP's stripslashes function)!

Then, what if you wanted to run the code on a server where magic_quotes_gpc is disabled? Your code would then have to check to see if magic quotes is switched on and bypass the use of stripslashes. Headaches are inevitable, and if you make a mistake and end up with spurious backslashes stored in your database, you may have a painful cleanup process ahead of you. It continually amazes me how many professionally designed sites fail to handle character escaping properly! Keep an eye out for unexpected backslashes in your own Web travels. See Chapter 1, PHP Basics for my advice on how best to avoid this on your own sites.

Indeed, magic quotes is discussed in some detail in Chapter 1, PHP Basics. If you do switch off magic_quotes_gpc as I advise, you should be aware of the potential risks to security. See the section called "SQL Injection Attacks" below and Appendix C, Security Checklist.

Next, we have the PHP function addslashes. Applied to any string, addslashes will use backslashes to escape single quotes, double quotes, backslashes and null characters. This makes it an effective means to escape strings for use in queries.

If magic quotes is on, of course, you must not use addslashes, or characters would be escaped twice! To solve this conflict, you can check if magic quotes is enabled with the function get_magic_quotes_gpc, which returns TRUE if magic quotes is enabled and FALSE if it isn't. You can bundle up this test with a function as follows:

<?php      
function safeAddSlashes($string)      
{      
 if (get_magic_quotes_gpc()) {      
   return $string;      
 } else {      
   return addslashes($string);      
 }      
}      
?>

The third way, which is very similar to addslashes, uses the function mysql_escape_string or mysql_real_escape_string (the latter was added in PHP 4.3.0). These functions use the underlying MySQL C++ API (provided by the developers of MySQL, rather than the developers of PHP) to escape special characters.

These functions escape null characters, line feeds, carriage returns, backslashes, single quotes, double quotes, and end-of-file characters. Since PHP 4.3.0, both functions have used the current character set for the connection between PHP and MySQL. There is, therefore, no difference between these two functions in the latest PHP versions, so feel free to stick with the shorter of the two, mysql_escape_string, if your server is up-to-date.

As this method is, in effect, the built-in mechanism provided by MySQL for escaping strings, I recommend it over addslashes or magic_quotes_gpc. Of course, if you want your SQL to port well to other databases, you may want to consider "hiding" the function call within a class method, which allows you to swap out the class—including the escape mechanism—when moving to a different database.

Again, if you do not otherwise handle the magic quotes issue, you'll have to check whether magic_quotes_gpc is on:

<?php      
function safeEscapeString($string)      
{      
 if (get_magic_quotes_gpc()) {      
   return $string;      
 } else {      
   return mysql_real_escape_string($string);      
 }      
}      
?>

The scripts in this book make use of the MagicQuotes/strip_quotes.php include file introduced in Chapter 1, PHP Basics and included in the code archive to effectively switch off magic quotes on servers where it is enabled, so the solutions that follow will use mysql_real_escape_string freely. I'd encourage you to do the same in your own scripts if you feel confident escaping quotes and other special characters yourself.

SQL Injection Attacks

An SQL injection attack occurs when an attacker exploits a legitimate user input mechanism on your site to send SQL code that your unsuspecting script will pass on to the database to execute. The golden rule: escape all data from external sources before letting it near your database. That rule doesn't just apply to INSERT and UPDATE queries, but also to SELECT queries.

No doubt many PHP developers have been saved from the worst SQL injection attacks by the limitations of MySQL, which will only allow a single SQL statement to be performed with each call to mysql_query. On other databases, the effect of an SQL injection can be disastrous, as an attacker can send a second query that, for example, deletes the entire contents of a table. With MySQL, however, problems can still occur, as the following code demonstrates:

$sql = "SELECT * FROM users        
       WHERE username='" . $_POST['username'] . "'      
       AND password='" . $_POST['password'] . "'";      
     
echo 'Query: ' . $sql . '<br />';      
     
$result = mysql_query($sql);      
     
$rows = mysql_num_rows($result);      
     
if ($rows > 0) {      
 echo 'You are logged in!<br />';      
} else {      
 echo 'You are not allowed here!<br />';      
}      
?>      
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">      
<input type="text" name="username" /><br />      
<input type="text" name="password" /><br />      
<input type="submit" />      
</form>

A savvy attacker could simply enter the following in the form's password field:

' OR username LIKE '%

Assuming magic quotes is disabled on your server, and you have no other measures in place to prevent it, this clever attack alters the meaning of the query:

SELECT * FROM users      
WHERE username='' AND password='' OR username LIKE '%'

The modified query will select all records in the user table! When the script checks whether any users matched the supplied user name and password combination, it will see this big result set and grant access to the site!

This can be prevented if we escape the incoming variables:

$sql = "SELECT * FROM users        
   WHERE username='" . safeEscapeString($_POST['username']) . "'      
   AND password='" . safeEscapeString($_POST['password']) . "'";

In some cases, depending on the circumstances, this may not be necessary. But if you value your sleep, remember that golden rule: escape all data from external sources.

How do I create flexible SQL statements?

SQL is a powerful language for manipulating data. Using PHP, we can construct SQL statements out of variables, which can be useful for sorting a table by a single column or displaying a large result set across multiple pages.

Here is a simple example that lets us sort the results of a query by a table column:

Example 3.16. 11.php (excerpt)        
       
// A query to select all articles        
$sql = "SELECT * FROM articles";        
       
// Initialize $_GET['order'] if it doesn't exist        
if (!isset($_GET['order']))        
 $_GET['order'] = FALSE;        
       
// Use a conditional switch to determine the order        
switch ($_GET['order']) {        
 case 'author':        
   // Add to the $sql string        
   $sql .= " ORDER BY author";        
   break;        
 default:        
   // Default sort by title        
   $sql .= " ORDER BY title";        
   break;        
}        
       
// Run the query, identifying the connection        
if (!$queryResource = mysql_query($sql, $dbConn)) {        
 trigger_error('Query error ' . mysql_error() . ' SQL: ' . $sql);        
}        
?>        
<table>        
<tr>        
<th><a href="<?php echo $_SERVER['PHP_SELF']; ?>?order=title"        
 >Title</a></th>        
<th><a href="<?php echo $_SERVER['PHP_SELF']; ?>?order=author"        
 >Author</a></th>        
</tr>        
<?php        
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {        
 echo "<tr>\n";        
 echo "<td>" . $row['title'] . "</td>";        
 echo "<td>" . $row['author'] . "</td>";        
 echo "</tr>\n";        
}        
?>        
</table>

Within the switch statement, I've generated part of the SQL statement "on the fly," depending on a GET variable the script receives from the browser.

This general approach can be extended to WHERE clauses, LIMIT clauses, and anything else you care to consider with SQL. We'll look at this in more detail when we construct a paged result set in Chapter 9, Web Page Elements.

Persistence Layers: Database Interaction Without SQL

Persistence layers are becoming popular, and are well supported in PHP today. A persistence layer is a collection of classes that represents the tables in your database, providing you with an API through which all data exchanged between the database and the PHP application passes. This generally takes away the need for you to write SQL statements by hand, as the queries are generated and executed automatically by the PHP classes that represent the data.

Because SQL is a fairly well defined standard, it also becomes possible to have a persistence layer generated automatically. A program can examine your database schema and produce the classes that will automatically read and update it. This can be a very significant time saver; simply design your database, run the code generation tool, and the rest is "just" a matter of formatting a little (X)HTML.

A prime example of a persistence layer is PEAR::DB_DataObject, which builds on top of the PEAR::DB database abstraction library, and automatically generates a layer of classes with which to access your tables.

Persistence layers in general, and PEAR::DB_DataObject in particular, are discussed in the section called "Do I really need to write SQL?".

How do I find out how many rows I've selected?

It's often useful to be able to count the number of rows returned by a query before you do anything with them, such as when you're splitting results across pages or producing statistical information. When selecting results, you can use either PHP or MySQL to count the number of rows for you.

Counting Rows with PHP

With PHP, the function mysql_num_rows returns the number of rows selected, but its application can be limited when you use unbuffered queries (see the section called "How do I fetch data from a table?"). The following code illustrates the use of mysql_num_rows:

Example 3.17. 12.php (excerpt)        
       
// A query to select all articles        
$sql = "SELECT * FROM articles ORDER BY title";        
       
// Run the query, identifying the connection        
$queryResource = mysql_query($sql, $dbConn);        
       
// Fetch the number of rows selected        
$numRows = mysql_num_rows($queryResource);        
       
echo $numRows . ' rows selected<br />';        
       
// Fetch rows from MySQL one at a time        
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {        
   echo 'Title: '  . $row['title']  . '<br />';        
   echo 'Author: ' . $row['author'] . '<br />';        
   echo 'Body: '   . $row['body']   . '<br />';        
}

The mysql_num_rows function, demonstrated in the above example, takes a result set resource identifier and returns the number of rows in that result set.

Note that the related function, mysql_num_fields, can be used to find out how many columns were selected. This can be handy when you're using queries like SELECT * FROM table, but you don't know how many columns you've selected.

Counting Rows with MySQL

The alternative approach is to use MySQL's COUNT function within the query. This requires that you perform two queries—one to count the results and one to actually get the results—which will cost you a little in terms of performance.

Here's how you could use the MySQL COUNT function:

Example 3.18. 13.php (excerpt)        
       
// A query to select all articles        
$sql = "SELECT COUNT(*) AS numrows FROM articles";        
       
// Query to count the rows returned        
$queryResource = mysql_query($sql, $dbConn);        
       
$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);        
       
echo $row['numrows'] . " rows selected<br />";        
       
// A query to select all articles        
$sql = "SELECT * FROM articles ORDER BY title";        
       
// Run the query, identifying the connection        
$queryResource = mysql_query($sql, $dbConn);        
       
// Fetch rows from MySQL one at a time        
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {        
 echo 'Title: '  . $row['title']  . '<br />';        
 echo 'Author: ' . $row['author'] . '<br />';        
 echo 'Body: '   . $row['body']   . '<br />';        
}

Notice we used an alias to place the result of the COUNT function?

SELECT COUNT(*) AS numrows FROM articles

We do this so that the number of rows can be identified later using $row['numrows']. The alternative would have been to omit the alias:

SELECT COUNT(*) FROM articles

This would require that we access the information as $row['COUNT(*)']/#epc#/, which can make the code confusing to read.