The PHP API for MySQL
PHP has a suite of built-in functions for interfacing with MySQL database servers. You can get by with a small subset of them, but we'll try to cover the full function list for completeness.
Connecting to a MySQL Database
Table 11.2 shows the functions for connecting to and disconnecting from a MySQL database server and for selecting a database.
You can use the following syntax to connect to a MySQL server:
mysql_connect ([server[,username[,password]]]);
Or, to make a persistent connection:
mysql_pconnect ([server[,username[,password]]]);
where server is of the form hostname[:port][:/path/to/socket]. Calling the function returns a link identifier if a successful connection is made or FALSE if there is a failure. You typically precede the function with the @ symbol; this allows you to handle a connection failure elegantly in your own code rather than have the default PHP warning text printed to your output. (You can place the @ symbol before any expression in PHP, the effect being that any error will be suppressed.)
For example, to connect to a database on localhost, with the database username and password in the variables $dbuser and $dbpass, respectively, you might do this:
$link_id = @mysql_pconnect ("localhost", $dbuser, $dbpass);
if ($link_id) {
echo "Link id is $link_id<BR>
";
} else {
echo "Failed to connect<BR>
";
}
If you use valid connection parameters, this prints:
Link id is Resource id #1
You can use either mysql_connect() or mysql_pconnect(). The former makes a simple database connection that is closed when the PHP script exits or when mysql_close() is encountered. mysql_pconnect() creates a persistent connection.
A persistent connection will not be closed when your script exits or when a mysql_close() is encountered. The next time a script tries to do a mysql_pconnect() using the same connection parameters, if it can find an existing link with these parameters, that link will be used again.
This is more efficient because it saves having to open a new connection each time your script is run and close it when the script finishes. However, beware that on a system that has busy peak periods, a number of persistent connections will be left open after the peak activity is over. You may thus want to set a low wait_timeout period in MySQL's variables rather than have inactive connections stay open for a long time after they are actually required. (See Day 15, “Administration,” for more about administering MySQL and setting variables that govern its behavior.)
As well as connecting to the MySQL server, you will need to select a MySQL database for use. You can do this by specifying the name of the database to the function mysql_select_db(). For example, to select the content management system database cms:
if (mysql_select_db ("cms")) {
echo "Connected to cms!<BR>
";
}
In practice, you will probably find it most useful to keep your database connection script as an included file for a variety of scripts to use rather than embed the connection parameters and error handling in each PHP script that you write. This has a number of advantages:
You keep the database name, username, and password in one place; if you change the password, you only need to change one file.
You can keep the connection parameters outside the Web tree where your main scripts reside, making it more difficult for them to be revealed should a server misconfiguration ever arise.
You keep your main scripts simpler and can have a standardized routine in the included file for handling connection errors.
You might want to have an include called mysqlsubs.inc, which is placed in a non-Web directory (in other words, it can never be viewed via a Web browser) and contains all database connection and selection routines (and possibly other shared database routines). It might look like Listing 11.3.
Listing 11.3. mysqlsubs.inc
In your main script, you then have the following lines:
include ("/nonweb/mysqlsubs.inc");
connect_to_cms ();
As you can see, by using the include statement and a user-defined function, you can improve the security of your site and keep the code tidier in your main PHP pages.
Tip
mysqlsubs.inc could be improved by passing a link identifier back to the calling program, using return in a line like this, inserted just before the end of the function definition:
return $link_id;
It could also do error handling in more elegant ways. For example, instead of simply printing an error message onscreen and exiting rather abruptly, it might perform a browser redirect to take the user to a completely different Web page. You could do this using the header() function, like this:
header ("Location: http://www.mysite.com/errorpage.php");
exit;
Running Database Queries
The most commonly used function for running queries is mysql_query(), which runs a query on the current database (the one already selected with mysql_select_db()). The basic syntax is as follows:
mysql_query (querystring)
where querystring is the SQL query to be run. Alternatively, if you want to specify the name of the database within the statement, you can use mysql_db_query():
mysql_db_query (database_name, querystring)
The first form, mysql_query(), is probably the most convenient unless your script jumps between several different databases.
If your query is a SELECT statement, the function returns a resource identifier if the query is processed successfully, or FALSE if it fails. In a moment, you'll learn how to use this resource identifier to access the resultset of data.
For other kinds of queries, the result is either TRUE for success or FALSE for failure.
Note
By “success” we mean that the query was successfully executed. It does not mean that any data was affected (for example, in an UPDATE statement). Equally, you could run a SELECT statement that returns no data, but it would still be successful. A query can succeed without it necessarily having the effect the programmer intended!
There may be a number of reasons leading to the failure of a query:
The SQL may contain syntax errors.
The query may be meaningless, such as trying to select from a table that does not exist.
The database user may not have sufficient privileges to perform the query (although that user may have successfully connected, other actions may be prohibited).
In a moment, you'll learn how to determine the effects of a query that modifies data and how to determine the nature of any errors that occur.
Running Queries That Return a Resultset
When you run a SELECT query, a resultset is created, provided that the query is run successfully. Table 11.3 lists the most useful functions for handling a resultset.
Consider a simple example of querying articles for display from the content management system. First we'll use mysql_query() to run the query and mysql_fetch_row() within a while loop to fetch the resulting rows one at a time, as shown in Listing 11.4.
Listing 11.4. Displaying the Resultset from a SELECT Query
This code executes the query using mysql_query() and puts the resource identifier into $result. (The code should really do some error checking at this point, but for now just assume that the query executes successfully.)
Then we used a while loop with mysql_fetch_row() in its expression (line 5). This fetches one row of the resultset each time it is called and places the result into the array $row. The elements in the row are numerically referenced, so we can access the two fields using $row[0] and $row[1].
After iterating through the resultset, when we try to go beyond the last row, mysql_fetch_row() returns FALSE, and the while loop terminates.
You could use the following while loop in place of the one in Listing 11.4. It does the same thing, but this time using mysql_fetch_array() to fetch each row of the resultset into an associative array:
while ($row = mysql_fetch_array ($result)) {
echo "Date ".$row["date_post"].": ".$row["headline"]."<BR>
";
}
Using mysql_fetch_array() is more convenient and is really an extension of mysql_fetch_row(). It fetches the data into an array that you can access either associatively, using the field names as keys (for example, $row["headline"]) or numerically (for example, $row[1]).
If your SELECT query references column names that are preceded by the table name and a period (for example, SELECT articles.headline...), such as may occur when performing a join across tables with columns with identical names, you will have to use either an alias in your SQL (such as SELECT articles.headline AS arthead...) or use a numerical reference to that element in the array (for example, $row[0]).
A third way of fetching the resultset is by using mysql_fetch_object() to fetch each row as an object. To use the preceding example again, the code for the fetch looks like this:
while ($row = mysql_fetch_object ($result)) {
echo "Date $row->date_post: $row->headline<BR>
";
}
Here you can access the data by the field names (such as $row->headline), but you can no longer access them by their numeric indexes. You will therefore find that mysql_fetch_array() is the most versatile of these functions because it gives both ways of accessing the resultset.
The final function considered here is mysql_num_rows(). Rather than return an array of data, this returns just the number of rows retrieved as a result of the query.
You can use it like this:
Running Queries That Return No Resultset
If you run a query that returns no resultset, such as one that performs an UPDATE, INSERT, or DELETE, you may want to check what effect the query had on your data.
Although mysql_query() returns a TRUE for success, this only indicates whether the query succeeded, not whether it made any change. To see what effect the query had, you need to use the function mysql_affected_rows().
You can use mysql_affected_rows() like this:
$sql = "DELETE FROM articles
WHERE article_id=2";
$result = mysql_query ($sql);
if ($aff = mysql_affected_rows ()) echo "$aff row(s) deleted<BR>
";
else echo "Nothing deleted<BR>
";
In the preceding code (assuming that mysql_query() is successful), the scalar variable $aff will be populated with the number of rows deleted as a result. You can do the same with any kind of query that performs some kind of modification to a database.
If you run a query that performs an INSERT on a table with an auto-increment primary key, you may want to determine which key value was generated as a result of the INSERT.
PHP has a function that tells you exactly this: mysql_insert_id(). You can use it like this:
$sql = "INSERT INTO articles
SET headline='Some headline'";
$result = mysql_query ($sql);
if ($id = mysql_insert_id ()) echo "Insert ID = $id<BR>
";
The preceding examples left out one vital aspect: how to know whether an error occurred when running the query and how to handle it. Let's look at this now.
Handling Query Errors
Correct handling of errors is imperative when writing almost any database query. You cannot assume that your query will execute successfully, and your code must handle the error condition in a suitable way.
In the preceding examples, we wrote something like this:
$result = mysql_query ($sql); # no error handling!
whereas we should have written something like this:
if ($result = mysql_query ($sql)) {
# query worked okay, keep going
} else {
# handle error
}
Because mysql_query() returns FALSE when a query fails, you can place it within an if condition as shown here and handle the error in a predictable way.
If an error occurs, you may want to know what went wrong. This is useful for debugging, and you may even build in functions for sending the user to a different Web page or even sending an alert to a system administrator.
Table 11.4 shows the functions available for reporting on an error that occurred.
Thus, you might have an error routine like this:
if ($result = mysql_query ($sql)) {
# query worked okay, keep going
} else {
echo "A database error occurred!<BR>
";
echo mysql_error() ." (error no. ". mysql_errno() .")<BR>
";
exit;
}
The preceding code produces an error report like this:
A database error occurred! Table 'cms.notable' doesn't exist (error no. 1146)
Tip
It's good practice to always store your SQL in a variable (say $sql, as in the preceding example) rather than to pass it directly to mysql_query(). You can then display your SQL in your error report just by printing $sql along with mysql_error().
Remember, always put error handling into your code, and you will benefit from easier debugging and safer, more resilient scripts.