Chapter 5
Connecting PHP to a MySQL Database
In This Chapter
Building the connection string
Sending queries to a database
Retrieving data results
Formatting data output
Allowing user queries
Cleaning user-submitted data requests
Data has become the prominent feature of the web. As you build more sophisticated sites using HTML and CSS, you will eventually feel the need to incorporate data into your websites. You can do a certain amount of data work with the basic data structures built into PHP, but more sophisticated data problems require more sophisticated tools. Likewise, MySQL is great at data, but is not perfect for getting input from users or preparing HTML output. PHP and MySQL are perfect partners, with very compatible strengths and weaknesses.
This chapter assumes you have a database available and also that you have some basic knowledge of how SQL (Structured Query Language; the language of databases) works. It also assumes you're comfortable with PHP. If you need a refresher on PHP, please check Book V. Book VI covers MySQL in detail.
PHP and MySQL: A Perfect (but Geeky) Romance
PHP programmers frequently use MySQL as their preferred data back end for a number of good reasons:
- MySQL is open source and free. Like PHP, MySQL is open source, so PHP and MySQL can be used together (with Apache) to build a very powerful low-cost data solution.
- MySQL is very powerful. MySQL's capability as a data program has improved steadily, and it is now nearly as capable as commercial tools costing thousands of dollars. (And it is better than many that cost hundreds of dollars.)
- PHP has built-in support for MySQL. PHP includes a number of functions specifically designed to help programmers maintain MySQL databases.
- You probably already have MySQL. If you installed XAMPP, you probably already have an installation of MySQL ready to go. Check Book VIII, Chapter 1 for installation details.
- MySQL was designed with remote control in mind. MySQL is meant to be managed from some other program (like the code you write in PHP). It's not designed with a user interface (like Access has), but it's designed from the beginning to be controlled through a programming language like PHP.
Before diving into details, here's an overview of how you get information to and from a MySQL database:
- Establish a connection.
Before you can work with a database, you must establish a relationship between your PHP program and the database. This process involves identifying where the database is and passing it a username and password.
- Formulate a query.
Most of the time, you'll have some sort of query or request you want to pass to the database. For example, you may want to see all the data in a particular table, or you may want to update a record. In either case, you use SQL to prepare a request to pass to the database.
- Submit the query.
After you build the query, you pass it (through the connection) to the database. Assuming that the query is properly formatted, the database processes the request and returns a result.
- Process the result.
The database returns a special variable containing the results of your query. You'll generally need to pick through this complex variable to find all the data it contains. For example, it can contain hundreds of records. (For more on records, see the upcoming section “Retrieving data from the database.”)
- Display output to the user.
Most of the time, you'll process the query results and convert them to some sort of HTML display that the user can view.
As an example, take a look at contact.php in Figure 5-1.
Figure 5-1: This program gets all the contact data from a database.
The contact.php program contains none of the actual contact information. All the data was extracted from a database. Here's an overview of the code:
<!DOCTYPE html>
<html lang = "en-US">
<head>
<meta charset = "UTF-8">
<title>contact.php</title>
</head>
<body>
<p>
<?php
try {
$con= new PDO('mysql:host=localhost;dbname=dbName', "user", "pwd");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $con->query('SELECT * FROM contact'),
$result->setFetchMode(PDO::FETCH_ASSOC);
foreach($result as $row){
foreach ($row as $name=>$value){
print "<strong>$name:</strong> $value <br />";
} // end field loop
print "<br />";
} // end record loop
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}
?>
</p>
</body>
</html>
If you want to try this program at home, begin by running the buildContactAutoIncrement.sql script (available in Book VI, Chapter 2) in your copy of MySQL. Note that you'll probably have to change the database, username, and password values to make your examples work. This will ensure you have the database created. See Book VI, Chapter 2 if you need more information on creating databases.
Understanding data connections
The key to all database work is the connection. Database connections remind me of the pneumatic tubes at some bank drive-through locations. There's a little container you can stick your request into. You press a button, and the container shoots through a tube to the teller, who processes your request and sends you the results back through the tube.
In data programming, the connection is like that tube: It's the pipeline between your program (your car) and the data (the bank). To establish a data connection, you need to know four things:
- The hostname (where the server is): Often, the data server will be housed on the same physical machine as the web server and PHP program. In these cases, you can use localhost as the server name. Test servers using XAMPP almost always use localhost connections. If you're working in a production environment, you may need to ask your service provider for the server address of your database.
- Your database username: Database programs should always have some type of security enabled. (See Book VI, Chapter 1 for information on setting up database users and passwords.) Your program needs to know the username it should use for accessing the data. (I often create a special username simply for my programs. Book VI, Chapter 1 outlines this process.)
When you first install MySQL through XAMPP, it allows root access with no password. These settings allow anybody to do anything with your data. Obviously, that's not a good solution, security-wise. Be sure to set up at least one username and password combination for your database. If you're using an online hosting service, you probably don't have root access. In this case, you typically have a new user created for each database you build. Book VI explains all. - A password for the database: The username isn't secure without a password. Your PHP program also needs a password. This is established when you create the database.
If you're going to make your source code available (as I do on the companion website), be sure to change the username and password so people can't use this information to hack your live data.
- The database name: A single installation of MySQL can have many databases available. You'll typically have a separate database designed for each project you build. MySQL needs to know which particular database houses the information you're seeking.
Introducing PDO
PHP has used a number of mechanisms for connecting to databases over the years. For a long time, the standard was a series of libraries for the various database types. Many people used the mysql library, which was (stay with me here) a library of functions for working with mySQL. If you wanted to use a different database, you'd need a different library with different functions.
The other problem with the mysql library was security. The techniques used in that library opened up a number of security holes. There are techniques for closing these holes, but not every programmer used them.
The mysqli library (mysql improved) fixed a number of these problems, but was still specific to a single database, and a bit more complex to use than the older library.
PHP5.1 and later now includes a library called PDO (PHP Data Objects) and it's a significant improvement over the mysql library. Here are a few key features:
- It works with multiple backends: In the old days, changing a database engine meant re-writing all your code. With PDO, you use exactly the same mechanism with all databases, so it's much easier to change data engines.
- It uses object-oriented syntax: PHP supports object-oriented programming, but it uses a slightly different syntax than JavaScript. Object-oriented programming adds some nice features to data access, so this is generally a good thing.
- It's provides safer access to data: PDO uses a mechanism called prepared statements which prevent the most challenging kinds of data errors. More about this in the section called “Allowing User Interaction” later in this chapter.
Building a connection
With PDO, the connection is an instance of the PDO object. When you make a PDO object, you're making a connection to the database. The data connection command is chock-full of details:
$con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
There's a lot of important stuff happening in this line:
- Set up a variable to hold the connection.
The entire point of creating a PDO object is to have a connection object, with various methods for modifying the data and making queries. So the first part of the data connection process is to make a connection object. I call mine $con.
$con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
- Build a new PDO object.
Because PDO is object-oriented, use the new keyword to call the PDO object constructor. (See Book V, Chapter 7 for more on objects and constructors in PHP.)
$con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
- Specify the database type.
MySQL is the most commonly used database system for PHP programmers, so that's what I specify. However, one of the advantages of PDO is its flexibility. If you change to a different RDBMS, you (theoretically, at least) only need to make one tiny change and the code will still work.
$con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
- Indicate the host.
When you're working on a local XAMPP installation, the host will often be localhost. If you're on a remote server, you may need to investigate where your databases are hosted. They may be on a completely different machine with its own address.
$con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
- Specify the database name.
Within a connection, you might have several databases. Use this part of the connection to determine which database you're using.
$con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
- Indicate the username.
Each database will likely have a specific user determined to be that database's administrator. (See Chapter 1 of this mini-book for instructions on setting up users and databases.)
$con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
- Provide the password.
Your program is essentially logging in as the user. This is why it's good to build a specific user for each application. This allows you to tightly control access to your database.
$con = new PDO('mysql:host=localhost;dbname=dbname', "username", "password");
If you are using the root user with no password, you're setting up your computer to be hacked. Please see my instructions in Chapter 1 of minibook VII to set up a more secure installation.
Retrieving data from the database
After a PDO connection is set up, it's pretty easy to use. Here's the overall plan for retrieving data from the PDO connection:
- Put all PDO code in an exception-handler.
Data access is inherently dangerous. It's a perfect place for things to go wrong, so use an exception-handler to protect from potential errors. Use the try clause to begin your exception-handler. You can learn more about exceptions in Book V, Chapter 7.
try {
- Set up your data connection.
Create a PDO object, setting up your data connection.
$con = new PDO('mysql:host=localhost;dbname=dbname', "userName", "password");
- Turn on error-tracking.
PDO has some features for tracking errors. These are especially useful because the ordinary PHP error codes don't help with PHP problems. Turn on the PDO error-reporting mechanism with the setAttribute() method of the PDO object.
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- Execute a query.
The PDO object's query() method allows you to apply a query to the database and returns the result in a special variable.
$result = $con->query('SELECT * FROM contact'),
The query() method is one of several techniques for getting data from the database. It's a shortcut meant to be used when you're sending an SQL request that's expected to return a result (like a SELECT) statement. Use execute() when you want to pass a command that will not return a result (like a CREATE TABLE or UPDATE) statement. - Set the fetch mode.
You can tell PDO to return data in a number of formats. For now, choose FETCH_ASSOC. This format returns each record as an associative array. This is the easiest fetch mode to work with. (You can also return each record as a numerically indexed array, both numeric and associative arrays, and as a special object.)
$result->setFetchMode(PDO::FETCH_ASSOC);
- Read the data a row at a time.
The results of a data query are typically a table, so read the table one row (record) at a time. The $result variable is an ordinary array, so you can easily use a foreach loop to separate the data into rows.
foreach($result as $row){
- Each row is an associative array.
Each row can also be thought of as an array. PDO has a number of ways to extract the data, but you set the fetch mode to associative array in Step 5. This means you can use the associative variant of the foreach loop to very easily separate each row into its name/value pairs.
foreach ($row as $name=>$value){
- Print the field's name and value.
Now you can simply print out the name and value of the field. Recall you are building HTML output, so you can go with something simple (as I'm doing in this example) or encode your output in something more sophisticated like a definition list or a table.
print "<strong>$name:</strong> $value <br />";
- End all your structures.
This is a complicated set of instructions. It's really easy to forget a closing structure. Be sure to indent properly and label all your closing braces.
} // end field loop
print "<br />";
} // end record loop - Catch exceptions.
Because all this code happens inside a try block, you need some sort of catch mechanism. Mine simply reports errors.
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
} // end try
Using HTML tables for output
The basic unit of structure in SQL is called a table because it's usually displayed in a tabular format. HTML also has a table structure, which is ideal for outputting SQL data. Figure 5-2 shows contactTable.php, which displays the contact information inside an HTML table.
Figure 5-2: The contact information displayed in an HTML table.
Tables are a very common way to output SQL results. There's one big difference between table output and the basic version shown elsewhere in this chapter. In a table, you have a separate row containing field names. Here's the code:
<!DOCTYPE html>
<html lang = "en-US">
<head>
<meta charset = "UTF-8">
<title>contact.php</title>
<style type = "text/css">
table, th, td {border: 1px solid black};
</style>
</head>
<body>
<p>
<?php
try {
$con= new PDO('mysql:host=localhost;dbname=dbName', "user", "pwd");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$query = "SELECT * FROM contact";
//first pass just gets the column names
print "<table>
";
$result = $con->query($query);
//return only the first row (we only need field names)
$row = $result->fetch(PDO::FETCH_ASSOC);
print " <tr>
";
foreach ($row as $field => $value){
print " <th>$field</th>
";
} // end foreach
print " </tr>
";
//second query gets the data
$data = $con->query($query);
$data->setFetchMode(PDO::FETCH_ASSOC);
foreach($data as $row){
print " <tr>
";
foreach ($row as $name=>$value){
print " <td>$value</td>
";
} // end field loop
print " </tr>
";
} // end record loop
print "</table>
";
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
} // end try
?>
</p>
</body>
</html>
You might be confused that I'm using a table here, seeing as how I argue pretty strongly against use of tables for page layout in the HTML and CSS minibooks. Tables aren't evil: They just aren't designed to be a page layout mechanism. Tables, however, are designed to display tabular data, and the result of a data query is pretty much the definition of tabular data. You can (and should) still use CSS for specific layout details of the table. Tables are fine when used to present data, which is what I'm doing here.
This code is still very similar to the basic contact.php program. It extracts data from the database exactly the same way. The main difference is how field names are treated. The field names will go in table headings, and only the values are printed from each row. To make this work, follow these steps:
- Build a normal MySQL connection.
Begin with the standard connection. Don't worry about formatting until you're reasonably certain that you can read data from the database.
$con = new PDO('mysql:host=localhost;dbname=dbName', "user", "pwd");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); - Determine your query.
Create a query that will produce a table, view, or search result. Store it in a variable so you can use it. (You'll use the same query twice in this exercise.)
$query = "SELECT * FROM contact";
- Print the table tag before extracting any results.
All the query data will be displayed inside the table, so print the table tag before you start printing anything that should go inside the table.
print "<table> ";
- Make a first pass to extract field names.
You're actually going to query the database twice. The first time, you simply want the field names, which you'll use to build the table headers, so it only needs one row.
$result = $con->query($query);
//return only the first row (we only need field names))
$row = $result->fetch(PDO::FETCH_ASSOC);The fetch method pulls the next available record from the $result variable. You want the record data in associative array format, so pass the PDO::FETCH_ASSOC constant to indicate this.
- Print the field names as table headers.
Now that you have a single record, walk through that record as an associative array and use the $field values to print out field names.
print " <tr> ";
foreach ($row as $field => $value){
print " <th>$field</th> ";
} // end foreach
print " </tr> "; - Make a second query.
Now execute the query again with the $con->query() method. This time, you're doing an ordinary query with multiple results. Don't forget to set the fetch mode to associative array.
//second query gets the data
$data = $con->query($query);
$data->setFetchMode(PDO::FETCH_ASSOC); - Use nested loops to print out data elements.
Use the ordinary nested-loops trick to print out all of the data elements with each record taking up one row of the HTML table.
foreach($data as $row){
print " <tr> ";
foreach ($row as $name=>$value){
print " <td>$value</td> ";
} // end field loop
print " </tr> ";
} // end record loop
Allowing User Interaction
If you have a large database, you probably want to allow users to search the database. For example, the form in Figure 5-3 allows the user to search the My Contacts database.
Figure 5-3: The user can check for any value in any field.
Here are a couple of interesting things about the form in Figure 5-3:
- The search value can be anything. The first field is an ordinary text field. The user can type absolutely anything here, so you should expect some surprises.
- The user selects a field with a drop-down menu. You don't expect the user to know exactly what field names you are using in your database. Whenever possible, supply this type of information in a format that's easier for the user and less prone to error.
- This form is built to fill in a query. The back-end program (search.php) will be constructing a query from data gathered from this form. The point of the form is to request two pieces of information from the user: a field to search in and a value to look for in that field. search.php uses the data gleaned from this form to construct and submit that query to the database.
- The user doesn't know SQL. Even if the user does know SQL, don't let him use it. The SQL query should always be built on the server side. Get enough information to build an SQL query, but don't send a query to the PHP. Doing so exposes your database to significant abuse, such as the SQL injection attack described later in this chapter.
- The form uses the post mechanism. From the HTML perspective, it isn't important whether the form uses get or post, but when you're using forms to construct SQL queries, using post is a bit safer because it makes the bad guys work a little bit harder to spoof your site and send bogus requests to your database.
Building an HTML search form
This is what the HTML code for search.html looks like:
<!DOCTYPE html>
<html lang = "en-US">
<head>
<meta charset = "UTF-8">
<title>search.html</title>
<link rel = "stylesheet"
type = "text/css"
href = "search.css" />
</head>
<body>
<h1>Search my contacts</h1>
<form action = "search.php"
method = "post">
<fieldset>
<label>Search for</label>
<input type = "text"
name = "srchVal" />
<label>in</label>
<select name = "srchField">
<option value = "contactID">ID</option>
<option value = "name">contact name</option>
<option value = "company">company name</option>
<option value = "email">email address</option>
</select>
<button type = "submit">submit request</button>
</fieldset>
</form>
</body>
</html>
This is really a pretty basic form. The interesting stuff happens in the search.php program that's triggered when the user submits this form.
Responding to the search request
When the user submits search.html, a page like Figure 5-4 appears, created by search.php.
The search.php program isn't really terribly different from contactTable.php. It takes an SQL query, sends it to a database, and returns the result as an HTML table. The only new idea is how the SQL query is built. Rather than preloading the entire query into a string variable, as I did in all other examples in this chapter, I used input from the form to inform the query.
At one level, this seems pretty easy because an SQL query is just a string, and it's easy to build strings based on input data. However, you should never interpolate user input into an SQL string. If you directly include data from a form into an SQL query, you're opening yourself up to a nefarious type of attack called SQL injection. Imagine somebody entering Andy; DROP TABLE contact as the search value. This fake name could destroy parts of the database if the programmer is unwary.
Figure 5-4: The program searches the database according to the parameters in search.html.
Never directly interpolate user input into an SQL statement. Use the sanitizing mechanisms described in the next section instead.
You can use input data to build custom queries, but you must do one of two things first:
- Sanitize the data to ensure it's legit: There's a couple of ways to do this, including the PDO::quote() method. I show another technique in the next section that ensures the data is in a very specific pre-arranged set of values.
- Use a prepared statement: Prepared statements are a powerful tool. They not only sanitize your data, they can speed up data requests quite a bit. Prepared statements are described in the next section.
Before going through all the details, here's the general plan.
- Ensure the field name is a legitimate value.
The user can enter a field name through a drop-down list. Theoretically that should only allow legitimate field names (if I built the form correctly), but an evildoer could build a spoof form with any values in there they wanted. So I'll ensure the field name value matches against a list of fields I know are legit, and quit if they entered something that isn't in my list.
- Build a prepared statement.
A prepared statement is a special database structure. It's like a query, but it has some placeholders in it. For example, you could create the following line:
$stmt = $con->prepare("SELECT * FROM contact WHERE $field LIKE ?");
The database will compile the statement as-is, but will not execute it yet. The question marks indicate values that will be provided later, and you can have as many as you wish.
- Execute the prepared statement.
When you have a prepared statement, you can execute it by sending it an array of values (one per question mark in the prepared statement). I still need an array even though it has only one value in it.
$stmt->execute(array("j%"));
- The values are not considered SQL.
One advantage to a prepared statement is the values passed (in this case j%, which looks for a value beginning with J) are never compiled as SQL, so most SQL injection attacks are prevented with this technique.
- The prepared statement can be reused.
Although it's not needed for this particular application, you can reuse a prepared statement many times, and it's only compiled by the database the first time. This can be very useful because many web applications involve reading data from a form and passing the results into queries.
Theory is good, but an actual example is needed. As usual, I provide the code in its entirety here, and then I point out specific features. Look at the big picture first:
<!DOCTYPE html>
<html lang = "en-US">
<head>
<meta charset = "UTF-8">
<title>search.php</title>
</head>
<body>
<h1>My Contacts</h1>
<?php
try {
$fieldName = array("contactID", "name", "company", "email");
//get values from form
$srchField = filter_input(INPUT_POST, "srchField");
$srchValue = filter_input(INPUT_POST, "srchVal");
//don't proceed unless it's a valid field name
if (in_array($srchField, $fieldName)){
$field = $srchField;
//put value inside %% structure
$value = "%$srchValue%";
$con= new PDO('mysql:host=localhost;dbname=dbName', "user", "pwd");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $con->prepare("SELECT * FROM contact WHERE $field LIKE ?");
$stmt->execute(array($value));
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (empty($result)){
print "No matches found";
} else {
foreach($result as $row){
foreach ($row as $field => $value){
print "<strong>$field:</strong> $value <br />";
} // end field loop
print "<br />";
} // end row loop
} // end 'empty results' if
} else {
print "That is not a valid field name";
} // end if
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
} // end try
?>
</body>
</html>
There's quite a bit going on in this program, but most of it isn't new.
- Enclose the whole thing in a try block.
As usual, exception-handling is a big part of data access, so be sure to add the standard try-catch block.
try {
...
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
} // end try - Create an array for the valid field names.
The easiest way to check if something is within a range of values is to build an array of the legitimate values. I use this array to check to see that the field is legit in Step 4.
$fieldName = array("contactID", "name", "company", "email");
- Get input from the user.
Grab user input from the form using the normal filter_input mechanism. Note that you won't trust the data (yet) in your SQL, but you'll still need to extract the data.
$srchField = filter_input(INPUT_POST, "srchField");
$srchValue = filter_input(INPUT_POST, "srchVal"); - See if the field name is in your list.
The in_array() function is really useful. If you feed it a value and an array, it will return true if the value appears in the array and false if it does not. (It's kind of like a bouncer for the nightclub of SQL requests.) If the field name is not on the “cool list,” code execution jumps to an error message and nothing bad ever gets near the database.
if (in_array($srchField, $fieldName)){
..
} else {
print "That is not a valid field name";
} // end if - Create variables for $field and $value.
The $field value is copied directly from the form (because you've already established that it's legitimate). The $value variable will be protected with a different mechanism, so I simply add % to the beginning and end. (Because this value will be used in a LIKE clause, the % symbols indicate that the position of the search string doesn't matter.)
$field = $srchField;
//put value inside %% structure
$value = "%$srchValue%"; - Set up a PDO connection.
Set up the PDO connection in the typical way.
$con= new PDO('mysql:host=localhost;dbname=dbName', "user", "pwd");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); - Prepare a statement.
The main query will be a prepared statement, so set it up with the question mark placeholder. You can include the $field variable directly in the query because it's already been validated.
$stmt = $con->prepare("SELECT * FROM contact WHERE $field LIKE ?");
- Execute the statement.
Send an array of values to the execute() method to execute the prepared statement. The array should have the same number of entries as question marks in the prepared statement.
$stmt->execute(array($value));
- Fetch the results.
Use the fetchAll() method to retrieve all the results from the query. Set the result set to associative arrays with the familiar FETCH_ASSOC constant.
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
- Test for an empty result set.
If the results of the fetchAll() method are empty, there was no match to the query. Send some sort of message to the user so they know what happened.
if (empty($result)){
print "No matches found";
} else {
...
} // end if - Print out results on success.
If the result has a value in it, parse it for all the data and print it out as usual.
foreach($result as $row){
foreach ($row as $field => $value){
print "<strong>$field:</strong> $value <br />";
} // end field loop
print "<br />";
} // end row loop
You can use the same general techniques to control all SQL statements needed to create and modify a database. In fact, this is exactly how most data programs work on the Internet, maintaining databases and allowing the user to indirectly modify the data.