36 PHP Data Objects (PDO)

When a web app logs a user in, displays their trophies, or offers a list of products and allows a them to build a shopping cart, it is usually a result of server-side scripts on the Logic Tier interacting with a Database Management System on the Data Tier. A server-side program communicates with the database by sending SQL commands, then writes the results into the web page to send back to the Presentation Tier. In PHP, the latest and best way of accomplishing this is to use PHP Data Objects (PDO).

In the sections below, you’ll see how to connect to the database from a PHP program, and how to issue UPDATE, DELETE and INSERT queries.

SELECT queries are a little bit more complicated, so we’ll leave them until the next chapter.

How to Send SQL Queries from PHP

To send UPDATE, DELETE or INSERT commands from a PHP script, you need to go through four steps:

1. Connect to the Database
2. Prepare the SQL query
3. Execute the SQL query
4. Check to make sure it worked

Here are the details…

1. Connect to the Database

The following code will create a Database Handler object that represents a connection to a database on localhost. If you are using XAMPP on your own machine or running the database on a small server, the database will often be on the same machine as the PHP interpreter, and localhost is an alias for the current machine. In a larger scale hosting environment, the database is likely to be on a separate machine, in which case you would put that machine’s IP address and port number here. Either way, you should fill in the name of your database along with your personal database id and password where indicated:

try {
    $dbh = new PDO("mysql:host=localhost;dbname=MYDB", "MYID", "MYPASSWORD");
} catch (Exception $e) {
    die("ERROR: Couldn't connect. {$e->getMessage()}");
}

The blue command above is the one that establishes the connection and creates the database handler object. The rest of the code is boilerplate exception handling. If something goes wrong with the connection, it will raise an exception, which will be caught and handled by the catch code block. The catch block executes the PHP die function to immediately terminate the script with an error message written into the HTTP Response. If your database connection fails, there’s likely not much you can do for the user right now, so immediately terminating is probably the best option.

try {
    $dbh = new PDO("mysql:host=localhost;dbname=YOUR_DATABASE", "root", "");
} catch (Exception $e) {
    die("ERROR: Couldn't connect. {$e->getMessage()}");
}

echo "<p>It worked!!!</p>"; // success message

2. Prepare the SQL Query

You prepare an SQL query by calling the prepare method of the database handler object. This method returns a statement object containing the compiled version of the command. Your original command string should contain question marks instead of data values. These question marks become parameters, to be filled in when you execute the statement.

$command = "INSERT into grades (firstname, lastname) VALUES (?, ?)";
$stmt = $dbh->prepare($command);

Note that you are creating a string that contains an SQL command. You likely won’t get any any context-sensitive help from your IDE when you’re doing this, so it’s easy to get the syntax wrong and have your command fail. It’s a good idea to work out command syntax in the SQL tab of phpMyAdmin first and then copy it into your code, replacing values with question marks. It’s also a good idea to use echo statements when debugging so that you can see the command string, like this:

echo "<p>$command</p>";

3. Execute the SQL Query

You execute the prepared statement by calling its execute method, passing an array of arguments. These arguments will fill in the question mark parameters in the order that they appeared in the original command string. The execute method returns true on success or false on failure.

$args= ["Drizzt", "Do'Urden"];
$success = $stmt->execute($args);

4. Check to Make Sure it Worked

You can check the return value to determine whether or not the command succeeded, and you can use the rowCount() method of the Statement object to find out how many rows were affected. For a simple INSERT, the rowCount should be 1. Here’s some example code for checking that everything worked:

if ($success) {
    echo "<p>Win!</p>";
    echo"<p>{$stmt->rowCount()} rows were affected.</p>";
} else {
    echo "<p>Fail…</p>";
}

It’s always possible that a query was “successful” (it completed with no errors) but still had no effect on the table (rowCount() is 0). For example, an UPDATE method might have a WHERE clause that doesn’t match any rows in the database. In this case, the query will succeed but will not update any rows. So it’s a good idea to check rowCount() as well as the return value of the execute method.

For more sophisticated error handling, you can also use a try… catch statement as we did for the connect script.

Getting Data from a Form

For many PHP applications, the data for INSERT, UPDATE, and DELETE comes from HTTP GET and POST parameters, often sent by a user interacting with an HTML form. The PHP script receives the parameters in the usual way, checks to make sure the values are ok, and then substitutes them into the prepared expression as above. Here’s an example of what that might look like:

// receive two parameters
$fn = filter_input(INPUT_POST, "first", FILTER_SANITIZE_STRING);
$exam = filter_input(INPUT_POST, "exam", FILTER_VALIDATE_INT);

// make sure they're ok
if ($fn !== null && $exam !== null && $exam !== false) {
    // prepare the query
    $command = "UPDATE grades SET final_exam=? WHERE firstname=?";
    $stmt = $dbh->prepare($command);

    // use the parameters to execute the command
    $args= [$exam, $fn];
    $success = $stmt->execute($args);

    // check $success and rowCount then continue the program...
    ...
}

Note that you should always use POST for INSERT, UPDATE, and DELETE. If you use GET, a user can bookmark the URL along with all the form data in it and then repeat the operation indefinitely. This could lead to unexpected changes to the database. (Technically, you should actually use a third HTTP Request method, PUT, for INSERT operations, not POST. But we’re going to ignore that complication here.)