11 Accessing Your MySQL Database from the Web with PHP
PREVIOUSLY, IN OUR WORK WITH PHP, WE used a flat file to store and retrieve data. When we looked at this file in Chapter 2, “Storing and Retrieving Data,” we mentioned that relational database systems make a lot of these storage and retrieval tasks easier, safer, and more efficient in a web application. Now, having worked with MySQL to create a database, we can begin connecting this database to a web-based front end.
In this chapter, we explain how to access the Book-O-Rama database from the Web using PHP. You learn how to read from and write to the database and how to filter potentially troublesome input data.
Key topics covered in this chapter include
How web database architectures work
Querying a database from the Web using the basic steps
Getting information about available databases
Disconnecting from the database
Putting new information in the database
Using other PHP-database interfaces
Using a generic database interface: PEAR MDB2
How Web Database Architectures Work
In Chapter 8, “Designing Your Web Database,” we outlined how web database architectures work. Just to remind you, here are the steps:
1. A user’s web browser issues an HTTP request for a particular web page. For example, the user might have requested a search for all the books written by Michael Morgan at Book-O-Rama, using an HTML form. The search results page is called results.php.
2. The web server receives the request for results.php, retrieves the file, and passes it to the PHP engine for processing.
3. The PHP engine begins parsing the script. Inside the script is a command to connect to the database and execute a query (perform the search for books). PHP opens a connection to the MySQL server and sends on the appropriate query.
4. The MySQL server receives the database query, processes it, and sends the results—a list of books—back to the PHP engine.
5. The PHP engine finishes running the script. This usually involves formatting the query results nicely in HTML. It then returns the resulting HTML to the web server.
6. The web server passes the HTML back to the browser, where the user can see the list of books she requested.
Now you have an existing MySQL database, so you can write the PHP code to perform the preceding steps. Begin with the search form. The code for this plain HTML form is shown in Listing 11.1.
Listing 11.1 search.html—Book-O-Rama’s Database Search Page
<html>
<head>
<title>Book-O-Rama Catalog Search</title>
</head>
<body>
<h1>Book-O-Rama Catalog Search</h1>
<form action="results.php" method="post">
Choose Search Type:<br />
<select name="searchtype">
<option value="author">Author</option>
<option value="title">Title</option>
<option value="isbn">ISBN</option>
</select>
<br/>
Enter Search Term:<br />
<input name="searchterm" type=" "text" size="40"/>
<br/>
<input type="submit" name="submit" value="Search"/>
</form>
</body>
</html>
This HTML form is reasonably straightforward. The output of this HTML is shown in Figure 11.1.
Figure 11.1 The search form is quite general, so you can search for a book by its title, author, or ISBN.
The script that will be called when the Search button is clicked is results.php. It is listed in full in Listing 11.2. Through the course of this chapter, we discuss what this script does and how it works.
Listing 11.2 results.php—This Script Retrieves Search Results from the MySQL Database and Formats Them for Display
<html>
<head>
<title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>
<?php
// create short variable names
$searchtype=$_POST['searchtype'];
$searchterm=trim($_POST['searchterm']);
if (!$searchtype || !$searchterm) {
echo 'You have not entered search details. Please go back and try again.';
exit;
}
if (!get_magic_quotes_gpc()){
$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);
}
@ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books'),
if (mysqli_connect_errno()) {
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
$result = $db->query($query);
$num_results = $result->num_rows;
echo "<p>Number of books found: ".$num_results."</p>";
for ($i=0; $i <$num_results; $i++) {
$row = $result->fetch_assoc();
echo "<p><strong>".($i+1).". Title: ";
echo htmlspecialchars(stripslashes($row['title']));
echo "</strong><br />Author: ";
echo stripslashes($row['author']);
echo "<br />ISBN: ";
echo stripslashes($row['isbn']);
echo "<br />Price: ";
echo stripslashes($row['price']);
echo "</p>";
}
$result->free();
$db->close();
?>
</body>
</html>
Note that this script allows you to enter the MySQL wildcard characters % and _ (underscore). This capability can be useful for the user, but you can escape these characters if they will cause a problem for your application.
Figure 11.2 illustrates the results of using this script to perform a search.
Figure 11.2 The results of searching the database for books about Java are presented in a web page using the results.php script.
Querying a Database from the Web
In any script used to access a database from the Web, you follow some basic steps:
1. Check and filter data coming from the user.
2. Set up a connection to the appropriate database.
3. Query the database.
4. Retrieve the results.
5. Present the results back to the user.
These are the steps we followed in the script results.php, so now let’s go through each of them in turn.
Checking and Filtering Input Data
You begin the script by stripping any whitespace that the user might have inadvertently entered at the beginning or end of his search term. You do this by applying the function trim() to the value of $_POST['searchterm'] when giving it a shorter name:
$searchterm=trim($_POST['searchterm']);
The next step is to verify that the user has entered a search term and selected a search type. Note that you check whether the user entered a search term after trimming whitespace from the ends of $searchterm. If you arrange these lines in the opposite order, you could encounter situations in which a user’s search term is not empty and therefore does not create an error message; instead, it is all whitespace, so it is deleted by trim():
if (!$searchtype || !$searchterm) {
echo "You have not entered search details. Please go back and try again.";
exit;
}
You check the $searchtype variable, even though in this case it’s coming from an HTML SELECT. You might ask why you should bother checking data that has to be filled in. It’s important to remember that there might be more than one interface to your database. For example, Amazon has many affiliates who use its search interface. Also, it’s sensible to screen data in case of any security problems that can arise because of users coming from different points of entry.
When you plan to use any data input by a user, you need to filter it appropriately for any control characters. As you might remember, in Chapter 4, “String Manipulation and Regular Expressions,” we described the functions addslashes(), stripslashes(), and get_magic_quotes_gpc(). You need to escape data when submitting any user input to a database such as MySQL.
In this case, you check the value of the get_magic_quotes_gpc() function. It tells you whether quoting is being done automatically. If it is not, you use addslashes() to escape the data:
if (!get_magic_quotes_gpc()) {
$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);
}
You also use stripslashes() on the data coming back from the database. If the magic quotes feature is turned on, the data will have slashes in it when it comes back from the database, so you need to take them out.
Here you use the function htmlspecialchars() to encode characters that have special meanings in HTML. The current test data does not include any ampersands (&), less than (<), greater than (>), or double quotation mark (") symbols, but many fine book titles contain an ampersand. By using this function, you can eliminate future errors.
Setting Up a Connection
The PHP library for connecting to MySQL is called mysqli (the i stands for improved). When using the mysqli library in PHP, you can use either an object-oriented or procedural syntax.
You use the following line in the script to connect to the MySQL server:
@ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books'),
This line instantiates the mysqli class and creates a connection to host localhost with username bookorama, and password bookorama123. The connection is set up to use the database called books.
Using this object-oriented approach, you can now invoke methods on this object to access the database. If you prefer a procedural approach, mysqli allows for this, too. To connect in a procedural fashion, you use
@ $db = mysqli_connect('localhost', 'bookorama', 'bookorama123', 'books'),
This function returns a resource rather than an object. This resource represents the connection to the database, and if you are using the procedural approach, you will need to pass this resource in to all the other mysqli functions. This is very similar to the way the file-handling functions, such as fopen(), work.
Most of the mysqli functions have an object-oriented interface and a procedural interface. Generally, the differences are that the procedural version function names start with mysqli_ and require you to pass in the resource handle you obtained from mysqli_connect(). Database connections are an exception to this rule because they can be made by the mysqli object’s constructor.
The result of your attempt at connection is worth checking because none of the rest of code will work without a valid database connection. You do this using the following code:
if (mysqli_connect_errno()) {
echo 'Error: Could not connect to database. Please try again later.';
exit;
}
(This code is the same for the object-oriented and procedural versions.) The mysqli_connect_errno() function returns an error number on error, or zero on success.
Note that when you connect to the database, you begin the line of code with the error suppression operator, @. This way, you can handle any errors gracefully. (This could also be done with exceptions, which we have not used in this simple example.)
Bear in mind that there is a limit to the number of MySQL connections that can exist at the same time. The MySQL parameter max_connections determines what this limit is. The purpose of this parameter and the related Apache parameter MaxClients is to tell the server to reject new connection requests instead of allowing machine resources to be completely used up at busy times or when software has crashed.
You can alter both of these parameters from their default values by editing the configuration files. To set MaxClients in Apache, edit the httpd.conf file on your system. To set max_connections for MySQL, edit the file my.conf.
Choosing a Database to Use
Remember that when you are using MySQL from a command-line interface, you need to tell it which database you plan to use with a command such as
use books;
You also need to do this when connecting from the Web. The database to use is specified as a parameter to the mysqli constructor or the mysqli_connect() function. If you want to change the default database, you can do so with the mysqli_select_db() function. It can be accessed as either
$db->select_db(dbname)
or as
mysqli_select_db(db_resource, db_name)
Here, you can see the similarity between the functions that we described before: The procedural version begins with mysqli_ and requires the extra database handle parameter.
Querying the Database
To actually perform the query, you can use the mysqli_query() function. Before doing this, however, it’s a good idea to set up the query you want to run:
$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
In this case, you search for the user-input value ($searchterm) in the field the user specified ($searchtype). Notice the use of like for matching rather than equal: it’s usually a good idea to be more tolerant in a database search.
You can now run the query:
$result = $db->query($query);
Or, if you want to use the procedural interface, you use
$result = mysqli_query($db, $query);
You pass in the query you want to run and, in the procedural interface, the database link (again, in this case $db).
The object-oriented version returns a result object; the procedural version returns a result resource. (This is similar to the way the connection functions work.) Either way, you store the result in a variable ($result) for later use. This function returns false on failure.
Retrieving the Query Results
A large variety of functions is available to break the results out of the result object or identifier in different ways. The result object or identifier is the key to accessing the rows returned by the query.
In this example, you counted the number of rows returned and also used the mysqli_fetch_assoc() function.
When you use the object-oriented approach, the number of rows returned is stored in the num_rows member of the result object, and you can access it as follows:
$num_results = $result->num_rows;
When you use a procedural approach, the function mysqli_num_rows() gives you the number of rows returned by the query. You should pass it the result identifier, like this:
$num_results = mysqli_num_rows($result);
It’s useful to know this if you plan to process or display the results, because you now know how many there are and can loop through them:
for ($i=0; $i <$num_results; $i++) {
// process results
}
In each iteration of this loop, you call $result->fetch_assoc() (or mysqli_fetch_assoc()). The loop does not execute if no rows are returned. This is a function that takes each row from the resultset and returns the row as an array, with each key an attribute name and each value the corresponding value in the array:
$row = $result->fetch_assoc();
Or you can use a procedural approach:
$row = mysqli_fetch_assoc($result);
Given the array $row, you can go through each field and display it appropriately, as shown in this example:
echo "<br />ISBN: ";
echo stripslashes($row['isbn']);
As previously mentioned, you call stripslashes() to tidy up the value before displaying it.
Several variations can be used to get results from a result identifier. Instead of an array with named keys, you can retrieve the results in an enumerated array with mysqli_fetch_row(), as follows:
$row = $result->fetch_row($result);
or
$row = mysqli_fetch_row($result);
The attribute values are listed in each of the array values $row[0], $row[1], and so on. (The mysqli_fetch_array() function allows you to fetch a row as either or both kinds of array.)
You could also fetch a row into an object with the mysqli_fetch_object() function:
$row = $result->fetch_object();
or
$row = mysqli_fetch_object($result);
You can then access each of the attributes via $row->title, $row->author, and so on.
Disconnecting from the Database
You can free up your resultset by calling either
$result->free();
or
mysqli_free_result($result);
You can then use
$db->close();
or
mysqli_close($db);
to close a database connection. Using this command isn’t strictly necessary because the connection will be closed when a script finishes execution anyway.
Putting New Information in the Database
Inserting new items into the database is remarkably similar to getting items out of the database. You follow the same basic steps: make a connection, send a query, and check the results. In this case, the query you send is an INSERT rather than a SELECT.
Although this process is similar, looking at an example can sometimes be useful. In Figure 11.3, you can see a basic HTML form for putting new books into the database.
The HTML for this page is shown in Listing 11.3.
Figure 11.3 This interface for putting new books into the database could be used by Book-O-Rama’s staff.
Listing 11.3 newbook.html—HTML for the Book Entry Page
<html>
<head>
<title>Book-O-Rama - New Book Entry</title>
</head>
<body>
<h1>Book-O-Rama - New Book Entry</h1>
<form action="insert_book.php" method="post">
<table border="0">
<tr>
<td>ISBN</td>
<td><input type="text" name="isbn" maxlength="13" size="13"></td>
</tr>
<tr>
<td>Author</td>
<td> <input type="text" name="author" maxlength="30" size="30"></td>
</tr>
<tr>
<td>Title</td>
<td> <input type="text" name="title" maxlength="60" size="30"></td>
</tr>
<tr>
<td>Price $</td>
<td><input type="text" name="price" maxlength="7" size="7"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="Register"></td>
</tr>
</table>
</form>
</body>
</html>
The results of this form are passed along to insert_book.php, a script that takes the details, performs some minor validations, and attempts to write the data into the database. The code for this script is shown in Listing 11.4.
Listing 11.4 insert_book.php—This Script Writes New Books into the Database
<html>
<head>
<title>Book-O-Rama Book Entry Results</title>
</head>
<body>
<h1>Book-O-Rama Book Entry Results</h1>
<?php
// create short variable names
$isbn=$_POST['isbn'];
$author=$_POST['author'];
$title=$_POST['title'];
$price=$_POST['price'];
if (!$isbn || !$author || !$title || !$price) {
echo "You have not entered all the required details.<br />"
."Please go back and try again.";
exit;
}
if (!get_magic_quotes_gpc()) {
$isbn = addslashes($isbn);
$author = addslashes($author);
$title = addslashes($title);
$price = doubleval($price);
}
@ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books'),
if (mysqli_connect_errno()) {
echo "Error: Could not connect to database. Please try again later.";
exit;
}
$query = "insert into books values
(' " .$isbn."', ' " .$author."', ' " .$title."', ' " .$price."')";
$result = $db->query($query);
if ($result) {
echo $db->affected_rows." book inserted into database.";
} else {
echo "An error has occurred. The item was not added.";
}
$db->close();
?>
</body>
</html>
The results of successfully inserting a book are shown in Figure 11.4.
Figure 11.4 The script completes successfully and reports that the book has been added to the database.
If you look at the code for insert_book.php, you can see that much of it is similar to the script you wrote to retrieve data from the database. You check that all the form fields were filled in, and you format them correctly for insertion into the database (if required) with addslashes():
if (!get_magic_quotes_gpc()) {
$isbn = addslashes($isbn);
$author = addslashes($author);
$title = addslashes($title);
$price = doubleval($price);
}
Because the price is stored in the database as a float, you don’t want to put slashes into it. You can achieve the same effect of filtering out any odd characters on this numerical field by calling doubleval(), which we discussed in Chapter 1, “PHP Crash Course.” This also takes care of any currency symbols that the user might have typed into the form.
Again, you connect to the database by instantiating the mysqli object and setting up a query to send to the database. In this case, the query is an SQL INSERT:
$query = "insert into books values
(' " .$isbn."', ' " .$author."', ' " .$title."', ' " .$price."')";
$result = $db->query($query);
This query is executed on the database by calling $db->query() (or mysqli_query() if you want to do things procedurally).
One significant difference between using INSERT and SELECT is in the use of mysqli_affected_rows(). This is a function in the procedural version or a class member variable in the object-oriented version:
echo $db->affected_rows." book inserted into database.";
In the previous script, you used mysqli_num_rows() to determine how many rows were returned by a SELECT. When you write queries that change the database, such as INSERTs, DELETEs, and UPDATEs, you should use mysqli_affected_rows() instead.
We’ve now covered the basics of using MySQL databases from PHP.
Using Prepared Statements
The mysqli library supports the use of prepared statements. They are useful for speeding up execution when you are performing large numbers of the same query with different data. They also protect against SQL injection-style attacks.
The basic concept of a prepared statement is that you send a template of the query you want to execute to MySQL and then send the data separately. You can send multiple lots of the same data to the same prepared statement; this capability is particularly useful for bulk inserts.
You could use prepared statements in the insert_book.php script, as follows:
$query = "insert into books values(?, ?, ?, ?)";
$stmt = $db->prepare($query);
$stmt->bind_param("sssd", $isbn, $author, $title, $price);
$stmt->execute();
echo $stmt->affected_rows.' book inserted into database.';
$stmt->close();
Let’s consider this code line by line.
When you set up the query, instead of substituting in the variables as done previously, you put in question marks for each piece of data. You should not put any quotation marks or other delimiters around these question marks.
The second line is a call to $db->prepare(), which is called mysqli_stmt_prepare() in the procedural version. This line constructs a statement object or resource that you will then use to do the actual processing.
The statement object has a method called bind_param(). (In the procedural version, it is called mysqli_stmt_bind_param().) The purpose of bind_param() is to tell PHP which variables should be substituted for the question marks. The first parameter is a format string, not unlike the format string used in printf(). The value you are passing here ("sssd") means that the four parameters are a string, a string, a string, and a double, respectively. Other possible characters in the format string are i for integer and b for blob. After this parameter, you should list the same number of variables as you have question marks in your statement. They will be substituted in this order.
The call to $stmt->execute() (mysqli_stmt_execute() in the procedural version) actually runs the query. You can then access the number of affected rows and close the statement.
So how is this prepared statement useful? The clever thing is that you can change the values of the four bound variables and re-execute the statement without having to reprepare. This capability is useful for looping through bulk inserts.
As well as binding parameters, you can bind results. For SELECT type queries, you can use $stmt->bind_result() (or mysqli_stmt_bind_result()) to provide a list of variables that you would like the result columns to be filled into. Each time you call $stmt->fetch() (or mysqli_stmt_fetch()), column values from the next row in the resultset are filled into these bound variables. For example, in the book search script you looked at earlier, you could use
$stmt->bind_result($isbn, $author, $title, $price);
to bind these four variables to the four columns that will be returned from the query. After calling
$stmt->execute();
you can call
$stmt->fetch();
in the loop. Each time this is called, it fetches the next result row into the four bound variables.
You can also use mysqli_stmt_bind_param() and mysqli_stmt_bind_result() in the same script.
Using Other PHP-Database Interfaces
PHP supports libraries for connecting to a large number of databases, including Oracle, Microsoft SQL Server, and PostgreSQL.
In general, the principles of connecting to and querying any of these databases are much the same. The individual function names vary, and different databases have slightly different functionality, but if you can connect to MySQL, you should be able to easily adapt your knowledge to any of the others.
If you want to use a database that doesn’t have a specific library available in PHP, you can use the generic ODBC functions. ODBC, which stands for Open Database Connectivity, is a standard for connections to databases. It has the most limited functionality of any of the function sets, for fairly obvious reasons. If you have to be compatible with everything, you can’t exploit the special features of anything.
In addition to the libraries that come with PHP, available database abstraction classes such as MDB2 allow you to use the same function names for each type of database.
Using a Generic Database Interface: PEAR MDB2
Let’s look at a brief example using the PEAR MDB2 abstraction layer. This is one of the most widely used of all the PEAR components. Instructions for installing the MDB2 abstraction layer can be found in the “PEAR Installation” section in Appendix A, “Installing PHP and MySQL.”
For comparative purposes, let’s look at how you could write the search results script differently using MDB2.
Listing 11.5 results_generic.php—Retrieves Search Results from the MySQL Database and Formats Them for Display
<html>
<head>
<title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>
<?php
// create short variable names
$searchtype=$_POST['searchtype'];
$searchterm=trim($_POST['searchterm']);
if (!$searchtype || !$searchterm) {
echo "You have not entered search details. Please go back and try again.";
exit;
}
if (!get_magic_quotes_gpc()) {
$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);
}
// set up for using PEAR MDB2
require_once('MDB2.php'),
$user = 'bookorama';
$pass = 'bookorama123';
$host = 'localhost';
$db_name = 'books';
// set up universal connection string or DSN
$dsn = "mysqli://".$user.":".$pass."@".$host."/".$db_name;
// connect to database
$db = &MDB2::connect($dsn);
// check if connection worked
if (MDB2::isError($db)) {
echo $db->getMessage();
exit;
}
// perform query
$query = "select * from books where ".$searchtype." like '%".$searchterm."%'";
$result = $db->query($query);
// check that result was ok
if (MDB2::isError($result)) {
echo $db->getMessage();
exit;
}
// get number of returned rows
$num_results = $result->numRows();
// display each returned row
for ($i=0; $i <$num_results; $i++) {
$row = $result->fetchRow(MDB2_FETCHMODE_ASSOC);
echo "<p><strong>".($i+1).". Title: ";
echo htmlspecialchars(stripslashes($row['title']));
echo "</strong><br />Author: ";
echo stripslashes($row['author']);
echo "<br />ISBN: ";
echo stripslashes($row['isbn']);
echo "<br />Price: ";
echo stripslashes($row['price']);
echo "</p>";
}
// disconnect from database
$db->disconnect();
?>
</body>
</html>
Let’s examine what you do differently in this script.
To connect to the database, you use the line
$db = MDB2::connect($dsn);
This function accepts a universal connection string that contains all the parameters necessary to connect to the database. You can see this if you look at the format of the connection string:
$dsn = "mysqli://".$user.":".$pass."@".$host."/".$db_name;
After this, you check to see whether the connection was unsuccessful using the isError() method and, if so, print the error message and exit:
if (MDB2::isError($db)) {
echo $db->getMessage();
exit;
}
Assuming everything has gone well, you then set up a query and execute it as follows:
$result = $db->query($query);
You can check the number of rows returned:
$num_results = $result->numRows();
You retrieve each row as follows:
$row = $result->fetchRow(DB_FETCHMODE_ASSOC);
The generic method fetchRow()can fetch a row in many formats; the parameter MDB2_FETCHMODE_ASSOC tells it that you would like the row returned as an associative array.
After outputting the returned rows, you finish by closing the database connection:
$db->disconnect();
As you can see, this generic example is similar to the first script.
The advantages of using MDB2 are that you need to remember only one set of database functions and that the code will require minimal changes if you decide to change the database software.
Because this is a MySQL book, we use the MySQL native libraries for extra speed and flexibility. You might want to use the MDB2 package in your projects because sometimes the use of an abstraction layer can be extremely helpful.
Further Reading
For more information on connecting MySQL and PHP together, you can read the appropriate sections of the PHP and MySQL manuals.
For more information on ODBC, visit http://www.webopedia.com/TERM/O/ODBC.html.
Next
In the next chapter, we go into more detail about MySQL administration and discuss how to optimize databases.