On the Web today, content is king.
–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:
- 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;$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.
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.
PHP lets you perform unbuffered queries with mysql_unbuffered_query:
$queryResource = mysql_unbuffered_query($sql, $dbConn);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.
For example, consider this query:
UPDATE articles SET title = 'How NOT to update data'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 articlesWe 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 articlesThis would require that we access the information as $row['COUNT(*)']/#epc#/, which can make the code confusing to read.
When we use the COUNT function, it becomes important to construct queries on the fly as we saw in the section called “How do I create flexible SQL statements?”. You need to make sure your COUNT query contains the same WHERE or LIMIT clauses you used in the “real” query. For example, if the query we’re actually using to fetch data is:
SELECT * FROM articles WHERE author='HarryF'In PHP, we’ll probably want something like this:
Example 3.19. 14.php (excerpt)
// Define reusable "chunks" of SQL
$table = " FROM articles";
$where = " WHERE author='HarryF'";
$order = " ORDER BY title";
// Query to count the rows returned
$sql = "SELECT COUNT(*) as numrows" . $table . $where;
// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);
$row = mysql_fetch_array($queryResource, MYSQL_ASSOC);
echo $row['numrows'] . " rows selected<br />";
// A query to fetch the rows
$sql = "SELECT * " . $table . $where . $order;
// 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 />';
}Row Counting with Classes
Let’s look again at the classes we’ve been developing throughout this section. We can add the ability to find out the number of rows selected by introducing the following method to the MySQLResult class:
Example 3.20. Database/MySQL.php (in SPLIB) (excerpt)
/**
* Returns the number of rows selected
* @return int
* @access public
*/
function size()
{
return mysql_num_rows($this->query);
}Here’s how to use it:
Example 3.21. 15.php (excerpt)
// Connect to MySQL
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);
// Select all results for a particular author
$sql = "SELECT * FROM articles WHERE author='HarryF'";
$result = $db->query($sql);
echo "Found " . $result->size() . " rows";Counting Affected Rows
It’s also possible to find out how many rows were affected by an UPDATE, INSERT or DELETE query, using the PHP function mysql_affected_rows. Use of mysql_affected_rows is not common in typical PHP applications, but it could be a good way to inform users that, “You’ve just deleted 1854 records from the Customers table. Have a nice day!”
Unlike mysql_num_rows, which takes a result set resource identifier as its argument, mysql_affected_rows takes the database connection identifier. It returns the number of rows affected by the last query that modified the database, for the specified connection.
Here’s how mysql_affected_rows can be used:
Example 3.22. 16.php (excerpt)
// Connect to MySQL
$dbConn = &connectToDb($host, $dbUser, $dbPass, $dbName);
// A query which updates the database
$sql = "UPDATE
articles
SET
author='The Artist Formerly Known as...'
WHERE
author='HarryF'";
// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);
// Fetch the number of rows affected
$changedRows = mysql_affected_rows($dbConn);
echo $changedRows . ' rows changed<br />';As situations in which mysql_affected_rows is needed are uncommon, I’ll omit this from the MySQLResult class in the interests of keeping things simple.
After inserting a row, how do I find out its row number?
When you’re dealing with AUTO_INCREMENT columns in database tables, it’s often useful to be able to find out the ID of a row you’ve just inserted, so that other tables can be updated with this information. That, after all, is how relationships between tables are built. PHP provides the function mysql_insert_id, which, when given a link identifier, returns the ID generated by the last INSERT performed with that connection. Here’s how mysql_insert_id can be used:
Example 3.23. 17.php (excerpt)
// A query to insert a row
$sql = "INSERT INTO
articles
SET
title='How to use mysql_insert_id()',
body='This is an example',
author='HarryF'";
// Run the query, identifying the connection
$queryResource = mysql_query($sql, $dbConn);
// Fetch the inserted ID
$insertID = mysql_insert_id($dbConn);
echo 'The new row has ID: ' . $insertID;Class Insert ID
To use this functionality in our MySQLResult class, add the following method:
Example 3.24. Database/MySQL.php (in SPLIB) (excerpt)
/**
* Returns the ID of the last row inserted
* @return int
* @access public
*/
function insertID()
{
return mysql_insert_id($this->mysql->dbConn);
}As you might guess, using this method is quite straightforward:
Example 3.25. 18.php (excerpt)
// 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
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);
// A query to insert a row
$sql="INSERT INTO
articles
SET
title='How to use mysql_insert_id()',
body='This is an example',
author='HarryF'";
$result = $db->query($sql);
echo 'The new row as ID: ' . $result->insertID();How do I search my table?
Some people are just impatient; rather than trawling your site with the friendly navigation system you’ve provided, they demand information now! Hence PHP developers like you and I are required to implement search features to provide visitors a “short cut” to find the information they want. In the days of storing all content in the form of HTML files, this could be quite a problem, but now that you’re using a database to store content, searching becomes much easier.
Select What You LIKE
The most basic form of search occurs against a single column, with the LIKE operator:
SELECT * FROM articles WHERE title LIKE 'How %'The % is a wildcard character. The above statement will select all articles in which the title begins with the word “How.” MySQL also has support for POSIX regular expressions (the same as PHP’s ereg functions). Using the RLIKE operator, we can compare a column using a regular expression:
SELECT * FROM articles WHERE title RLIKE '^How '
The above statement also selects every article in which the title begins with "How" followed by a space.
With some work, these operators provide everything needed to explore your data. Where the above approach becomes a burden is in performing a search against multiple columns. For example,
SELECT * FROM articles
WHERE title LIKE '%how%' OR body LIKE '%how%'For larger tables, this can require you to write some very complicated and unpleasant queries.
FULLTEXT Searches
MySQL provides an alternative that does most of the work for you – the FULLTEXT index. Indexes in a database are much like the index of a book; they provide a means to locate information within the database quickly from an organized list. A FULLTEXT index allows you to search a table for particular words.
FULLTEXTindexes were introduced to MySQL with version 3.23. The implementation at this point was fairly limited but still useful for basic searching, which is what I'll demonstrate here. In MySQL version 4.0.1, this functionality was extended to provide a full Boolean search mechanism that gives you the ability to build something like Googleâ„¢'s advanced search features.FULLTEXTindexes also allow each result to be returned with a "relevance" value so that, for example, the results of multiple word searches can be displayed in terms of how well each result matches that user's particular search.
To take advantage of FULLTEXT indexes, you first need to instruct MySQL to begin building an index of the columns you want to search:
ALTER TABLE articles ADD FULLTEXT art_search (title, body, author)Once you’ve done that, you need to INSERT a new record (or modify an existing one) to get MySQL to build the index. You also need at least three records in the database for FULLTEXT searches to work, because non-Boolean searches will only return results if the search string occurred in less than 50% of the rows in the table (if there are only two rows in the table, and your search matches one row, that makes 50%). One final thing to be aware of is that FULLTEXT searches will only match searches of more than three letters; the indexing mechanism ignores words of three characters or less, to avoid having to build a massive index. This is much like the index of a book; you’d be pretty surprised to discover in a book’s index exactly which pages the word “the” appeared on!
Here’s a basic FULLTEXT search:
SELECT * FROM articles
WHERE MATCH (title,body,author) AGAINST ('MySQL');This search will return all rows where either the title, body or author contained the word “MySQL.”
Another use for FULLTEXT indexes is in a search which returns the relevance for each result. For example:
Example 3.26. 19.php (excerpt)
// Select all rows but display relvance
$sql = "SELECT
*, MATCH (title, body, author)
AGAINST
('The PHP Anthology Released Long Word Matching')
AS
score
FROM
articles
ORDER BY score DESC";
// 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 />';
echo 'Score: ' . $row['score'] . '<br />';
}The alias score now contains a value that identifies how relevant the row is to the search. The value is not a percentage, but simply a measure; 0 means no match was made at all. Matching a single word will produce a value around 1. The more words that match, the bigger the number gets, so a five word match ranking will produce a relevance score around 13. MySQL’s relevance algorithm is designed for large tables, so the more data you have, the more useful the relevance value becomes.
Overall, MySQL’s FULLTEXT search capabilities provide a mechanism that’s easy to implement and delivers useful results.
How do I back up my database?
The bigger a database becomes, the more nerve wracking it can be not to have a backup of the data it contains. What if your server crashes and everything is lost? Thankfully, MySQL comes with two alternatives: a command line utility called mysqldump, and a query syntax for backing up tables.
Here’s how you can export the contents of a database from the command line with mysqldump:
mysqldump -uharryf -psecret sitepoint > sitepoint.sqlThis command will log in to MySQL as user “harryf” (-uharryf) with the password “secret” (-psecret) and output the contents of the sitepoint database to a file called sitepoint.sql. The contents of sitepoint.sql will be a series of queries that can be run against MySQL, perhaps using the mysql utility to perform the reverse operation from the command line:
mysql -uharryf -psecret sitepoint < sitepoint.sqlUsing the PHP function system, you can execute the above command from within a PHP script (this requires you to be logged in and able to execute PHP scripts from the command line). The following class puts all this together in a handy PHP form that you can use to keep regular backups of your site.
Example 3.27. Database/MySQLDump.php (in SPLIB)
/**
* MySQLDump Class<br />
* Backs up a database, creating a file for each day of the week,
* using the mysqldump utility.<br />
* Can compress backup file with gzip of bzip2<br />
* Intended for command line execution in conjunction with
* cron<br />
* Requires the user executing the script has permission to execute
* mysqldump.
* <code>
* $mysqlDump = new MySQLDump('harryf', 'secret', 'sitepoint',
* '/backups');
* $mysqlDump->backup();
* </code>
* @access public
* @package SPLIB
*/
class MySQLDump {
/**
* The backup command to execute
* @private
* @var string
*/
var $cmd;
/**
* MySQLDump constructor
* @param string dbUser (MySQL User Name)
* @param string dbPass (MySQL User Password)
* @param string dbName (Database to select)
* @param string dest (Full dest. directory for backup file)
* @param string zip (Zip type; gz - gzip [default], bz2 - bzip)
* @access public
*/
function MySQLDump($dbUser, $dbPass, $dbName, $dest,
$zip = 'gz')
{
$zip_util = array('gz'=>'gzip','bz2'=>'bzip2');
if (array_key_exists($zip, $zip_util)) {
$fname = $dbName . '.' . date("w") . '.sql.' . $zip;
$this->cmd = 'mysqldump -u' . $dbUser . ' -p' . $dbPass .
' ' . $dbName . '| ' . $zip_util[$zip] . ' >' .
$dest . '/' . $fname;
} else {
$fname = $dbName . '.' . date("w") . '.sql';
$this->cmd = 'mysqldump -u' . $dbUser . ' -p' . $dbPass .
' ' . $dbName . ' >' . $dest . '/' . $fname;
}
}
/**
* Runs the constructed command
* @access public
* @return void
*/
function backup()
{
system($this->cmd, $error);
if ($error) {
trigger_error('Backup failed: ' . $error);
}
}
}Note
The MySQLDump class makes some assumptions about your operating system configuration. It assumes the mysqldump utility is available in the path of the user that executes this script. If the gzip or bzip2 utilities are used, they also need to be present in the path of the user who executes this script. bzip2 provides better compression than gzip, helping save disk space.
The following code demonstrates how this class can be used:
Example 3.28. 20.php
<?php
// Include the MySQLDump class
require_once 'Database/MySQLDump.php';
$dbUser = 'harryf'; // db User
$dbPass = 'secret'; // db User Password
$dbName = 'sitepoint'; // db name
$dest = '/home/harryf/backups'; // Path to directory
$zip = 'bz2'; // ZIP utility to compress with
// Instantiate MySQLDump
$mysqlDump = new MySQLDump($dbUser, $dbPass, $dbName, $dest,
$zip);
// Perform the backup
$mysqlDump->backup();
?>The $dest variable specifies the path to the directory in which the backup file should be placed. The filename that’s created will be in this format:
databaseName.dayOfWeek.sql.zipExtensionFor example:
sitepoint.1.sql.bz2The dayOfWeek element can be any number from 0 to 6 (0 being Sunday and 6 being Saturday). This provides a weekly “rolling” backup, the files for the following week overwriting those from the previous week. This should provide adequate backups, giving you a week to discover any serious problems, and without requiring excessive disk space to store the files.
The use of a ZIP utility is optional. If the value of the $zip variable is not one of gz or bz2, then no compression will be made, although for large databases it’s obviously a good idea to use a compression tool to minimize the amount of disk space required.
This class is intended for use with the crontab utility, which is a Unix feature that allows you to execute scripts on a regular (for example, daily) basis.
MySQL also provides the SQL statements BACKUP TABLE and RESTORE TABLE, which allow you to copy the contents of a table to another location on your file system. Unlike the mysqldump utility, tables backed up in this way preserve their original format (which is not human-readable) but this mechanism does not require access to a command line utility, so it could be executed via a Web page.
The general syntax for these statements is as follows:
BACKUP TABLE tbl_name[, tbl_name ...]
TO '/path/to/backup/directory'
RESTORE TABLE tbl_name[, tbl_name ...]
FROM '/path/to/backup/directory'Note that on Windows systems it’s best to specify paths using forward slashes (e.g. C:/backups).
By combining these with some of the “introspection” statements MySQL provides, we can backup our database using the MySQL class we built in this chapter. To start with, we need to get a list of tables in the database, which is quickly achieved using the SHOW TABLES query syntax:
Example 3.29. 21.php (excerpt)
<?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
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);
// A query to show the tables in the database
$sql = "SHOW TABLES FROM sitepoint";
// Execute query
$result = $db->query($sql);We also store the number of rows returned by this query to help us format the string we’ll use to build the BACKUP query:
Example 3.30. 21.php (excerpt)
// Get the number of tables found
$numTables = $result->size();Next, we loop through the results, building a comma-separated list of tables to back up:
Example 3.31. 21.php (excerpt)
// Build a string of table names
$tables = '';
$i = 1;
while ($table = $result->fetch()) {
$tables .= $table['Tables_in_sitepoint'];
if ($i < $numTables) {
$tables .= ', ';
}
$i++;
}Finally, we use the BACKUP TABLE query syntax to copy the tables to a directory of our choice (to which, of course, the script that executes this query needs permission to write):
Example 3.32. 21.php (excerpt)
// Build the backup query
$sql = "BACKUP TABLE $tables TO '/home/harryf/backup'";
// Perform the query
$db->query($sql);
if (!$db->isError()) {
echo 'Backup succeeded';
} else {
echo 'Backup failed';
}
?>How do I repair a corrupt table?
Although it shouldn’t happen, occasionally data stored in MySQL becomes corrupted. The are a number of (rare) circumstances where this can happen; Windows is particularly susceptible as it doesn’t have the robust file locking mechanism of Unix-based systems. Servers with heavy loads, on which INSERT and UPDATE queries are common alongside SELECTs are also likely to suffer occasional corruption. Assuming you’re using the MyISAM table type (you’ll be using this unless you’ve specified otherwise), there’s good news; in general , you should be able to recover all the data in a corrupt table.
Note that the information provided here represents a quick reference for those times when you need help fast. It’s well worth reading the MySQL manual on Disaster Prevention and Recovery so that you know exactly what you’re doing.
MySQL provides two important utilities to deal with corrupt tables, as well as a handy SQL syntax for those who can get to the MySQL command line.
First, the perror utility can be run from the command line to give you a rough idea of what MySQL error codes mean. The utility should be available from the bin subdirectory of your MySQL installation. Typing perror 145, for example, will tell you:
145 = Table was marked as crashed and should be repairedFrom the command line, you can then use the utility myisamchk to check the database files themselves:
myisamchk /path/to/mysql/data/table_nameTo repair a corrupt table with myisamchk, the syntax is as follows:
myisamchk -r /path/to/mysql/data/table_nameUsing SQL, you can also check and fix tables using a query like this:
CHECK TABLE articlesAnd this:
REPAIR TABLE articlesWith luck, you’ll need to use these commands only once or twice, but it’s worth being prepared in advance so you can react effectively (without even a hint of panic creeping into your actions).
Do I really need to write SQL?
A good quality to posses as a programmer is laziness – the desire to do as much as possible with the minimum amount of effort. Although you may not want to cite it as one of your strong points in a job interview, being motivated to make life easier for yourself is a significant boon in developing a well designed application.
Now that you’ve read this chapter on PHP and MySQL, I think it’s a good time to reveal that I hate SQL not because there’s anything wrong with it, as such, but because it always causes me grief. If there’s a syntax error in my PHP, for example, PHP will find it for me. But PHP won’t find errors in SQL statements, and MySQL error messages can be less than revealing. If I’m hand coding SQL in an application, I’ll spend a fair amount of time debugging it – time I could have spent taking it easy!
What if you could avoid having to write SQL statements altogether? If you think back to the section called “How do I create flexible SQL statements?”, where we constructed SQL strings “on the fly” based on incoming variables, you may have had an inkling that there would be some kind of generic solution to make generating SQL even easier. Well, there is! It’s called PEAR::DB_DataObject.
DB_DataObjectis a class that encapsulates the process of writing SQL statements in a simple API. It takes advantage of the native "grammar" of SQL and presents you with a mechanism that removes almost any need to write any SQL yourself. As an approach to dealing with databases, it's usually described as a database persistence layer, or, alternatively, as using the Data Access Objects (DAO) design pattern. You'll find further discussion of the general techniques used byDB_DataObjectat the end of this chapter.
Here, I’ll provide a short introduction to DB_DataObject to get you started, as it’s a subject that could easily absorb a whole chapter if examined in depth. The DB_DataObject documentation on the PEAR Website should provide you with plenty of further help. The version we used here was 1.1; note that it requires that you have the PEAR::DB database abstraction library installed (see Appendix D, Working with PEAR for more information on installing PEAR libraries).
The first step in getting started with DB_DataObject is to point it at your database and tell it to generate the DataObject classes that will constitute your interface with the tables. DB_DataObject automatically examines your database, using MySQL’s introspection functionality, and generates a class for each table in the database, as well as a configuration file containing the details of the columns defined by the table. To let DB_DataObject know where your database can be found, you need to provide it a configuration file like this one:
Example 3.33. db_dataobject.ini
[DB_DataObject]
; PEAR::DB DSN
database = mysql://harryf:secret@localhost/sitepoint
; Location where sitepoint.ini schema file should be created
schema_location = /htdocs/phpanth/SPLIB/ExampleApps/DataObject
; Location where DataObject classes should be created
class_location = /htdocs/phpanth/SPLIB/ExampleApps/DataObject
; Prefix for including files from your code
require_prefix = ExampleApps/DataObject
; Classes should be prefixed with this string e.g. DataObject_User
class_prefix = DataObject_
; Debugging information: 0=off, 1=display sql, 2=display results,
; 3=everything
debug = 0
; Prevent SQL INSERT, UPDATE or DELETE from being performed
debug_ignore_updates = false
; Whether to die of error with a PEAR_ERROR_DIE or not
dont_die = falseThe above ini file obeys the same formatting rules as php.ini. Most important is the first line, which is a PEAR::DB DSN string that defines the variables needed to connect to the database. This file is used both to generate the DataObject classes, and to use them in performing queries.
With that in place, we can use this script (which must be run from the command line) to generate the classes:
Example 3.34. 22.php
<?php
// Builds the DataObjects classes
$_SERVER['argv'][1] = 'db_dataobject.ini';
require_once 'DB/DataObject/createTables.php';
?>This script automatically creates the class files we need in order to access the database. Here’s an example developed for the articles table:
Example 3.35. ExampleApps/DataObject/Articles.php (in SPLIB)
<?php
/**
* Table Definition for articles
*/
require_once 'DB/DataObject.php';
class DataObject_Articles extends DB_DataObject
{
###START_AUTOCODE
/* the code below is auto generated do not remove the above tag
*/
var $__table = 'articles'; // table name
var $article_id; // int(11) not_null primary_key auto_increment
var $title; // string(255) not_null multiple_key
var $intro; // blob(65535) not_null blob
var $body; // blob(65535) not_null blob
var $author; // string(255) not_null
var $published; // string(11)
var $public; // string(1) not_null enum
/* ZE2 compatibility trick*/
function __clone() { return $this;}
/* Static get */
function staticGet($k,$v=NULL) {
return DB_DataObject::staticGet('DataObject_Articles',$k,$v); }
/* the code above is auto generated do not remove the tag below */
###END_AUTOCODE
}
?>Let’s now use this class to access the articles table:
Example 3.36. 23.php
<?php
// Include the DataObjects_Articles class
require_once 'ExampleApps/DataObject/Articles.php';
// Parse the database ini file
$dbconfig = parse_ini_file('db_dataobject.ini', true);
// Load Database Settings
// (note main PEAR class is loaded by Articles.php)
foreach ($dbconfig as $class => $values) {
$options = &PEAR::getStaticProperty($class, 'options');
$options = $values;
}
// Instantiate the DataObject_Articles class
$articles = new DataObject_Articles();
// Assign a value to use to search the 'Author' column
$articles->author = 'Kevin Yank';
// Perform the query
$articles->find();
echo 'Kevin has written the following articles:<br />';
// Loop through the articles
while ($articles->fetch()) {
echo ' - ' . $articles->title . ', published: ' .
date('jS M Y', $articles->published) . '<br />';
}
?>First of all, where’s the SQL? There isn’t any – great! The parse_ini_file function is provided by PHP (see Chapter 4, Files for details) and deals with getting the variables from our db_dataobject.ini configuration file. The foreach loop makes the required variables available to DB_DataObject when we instantiate its auto-generated subclass DataObject_Articles. By assigning a value to the author property of the $articles object, we prepare a WHERE condition that DataObject_Articles should use when it queries the database. The query is actually performed by calling the find method (see the DB_DataObject documentation for full details), which in turn executes the following query:
SELECT * FROM articles WHERE articles.author = 'Kevin Yank'To loop through the results, we use the fetch method. When it’s called, fetch populates the properties of the $articles object with the current row result. This allows us to access them again via the property names, as with $articles->title.
Further methods are provided to make the query more complex, for example, the whereAdd method:
Example 3.37. 24.php (excerpt)
// Instantiate the DataObject_Articles class
$articles = new DataObject_Articles();
// Assign a value to use to search the 'Author' column
$articles->author = 'Kevin Yank';
// Add a where clause
$articles->whereAdd('published > ' . mktime(0, 0, 0, 5, 1, 2002));
// Perform the query
$articles->find();This allows us to add a further condition to the WHERE clause:
SELECT * FROM articles
WHERE published > 1020204000 AND articles.author = 'Kevin Yank'There are other similar methods, so if these fail to provide what you need, you can use the query method to execute a hand-coded query. Note that if you find yourself needing to use the query method, it may be a good idea to create a subclass of the generated DataObject class, and wrap the query in a useful method name that describes it accurately.
DB_DataObject also deals effectively with table joins, which, although slightly more detailed than the example above, is certainly a time saver when compared with writing complex join queries by hand.That concludes our short introduction to DB_DataObject, but this section should have given you a taste of what it can do for you. The big advantage is that it makes querying your database with SQL far less exhausting and error-prone. Also, by centralizing access to a particular table in a single class, it helps simplify dealing with changes to the table structure.
Further Reading
- Beginning MySQL
This article provides a solid summary of how to use SQL with MySQL.
- Give me back my MySQL Command Line!
Kevin Yank shows how to put together a PHP script which can be used to simulate the MySQL command line via a Web page.
- Optimizing your MySQL Application
This handy tutorial discusses the use of indexes in MySQL and how they can be used to improve performance.
- Generating PHP Database Access Layers
This article provides an overview of persistence layers and related code generation with pointers to some useful tools.
- Zend Tutorial on Fulltext Searches
This tutorial provides a detailed look at FULLTEXT searches.
- Getting Started with MySQL Fulltext Searches
This good tutorial delivers another detailed look at FULLTEXT searching.
- Backing Up with MySQLDump
This tutorial that explores the ins and outs of the mysqldump utility.
Look out for more chapters from The PHP Anthology on SitePoint in coming weeks! If you can’t wait, download the sample chapters, or order your very own copy now!
Harry FuecksHarry Fuecks is the Engineering Project Lead at Tamedia and formerly the Head of Engineering at Squirro. He is a data-driven facilitator, leader, coach and specializes in line management, hiring software engineers, analytics, mobile, and marketing. Harry also enjoys writing and you can read his articles on SitePoint and Medium.
