Advanced PEAR::DB

So far we've only looked at basic querying with PEAR::DB, but you should now be getting an idea of the power it offers when working with multiple databases. However, perhaps its most interesting capability is statement preparation, which is a cunning technique to handle repetitive SQL statements. Essentially, prepared statements let you treat an SQL query somewhat like a function - you define roughly what the query will do, without actually passing it any values, then later you "call" the query, passing in the values to use.

This might sound complicated at first, but I recommend you do not skip over this chapter - prepared statements are very easy to use, and eliminate a lot of the fuss of SQL because you no longer need very long and complicated queries to achieve your goals, and you do not even need to worry about escaping quotes and the like.

A prepared statement will look something like this:

INSERT INTO people VALUES (?, ?);

Once we have the prepared statement ready, it can then be called later by providing the values previously filled with question marks. To fully illustrate the power of prepared statements, we need to make a slight change to our people table - run this command to add an extra field, Age, to the table:

ALTER TABLE people ADD Age TINYINT;

Now, with that in place, we're going to use the following prepared statement:

INSERT INTO people VALUES (?, 10, ?);

That is, we're going to provide a default NumVisits value of 10, but the Name and Age will need to be specified when the prepared statement is called. Now, onto the actual code itself:

<?php
    include_once('DB.php');
    $db = DB::connect("mysql://root:alm65z@localhost/phpdb");

    if (DB::isError($db)) {
        print $db->getMessage();
        exit;
    } else {
        $data = array(
            array("Gabor", 25),
            array("Elsabeth", 39),
            array("Vicky", 19)
        );

        $prep = $db->prepare("INSERT INTO people VALUES (?, 10, ?);");

        while(list($var, $val) = each($data)) {
            print "Adding element $var\n";
            $db->execute($prep, $val);
        }
    }

    $db->disconnect();
?>

The first part of the code is nothing new, so start reading from $data = array onwards. The $data array that is defined has three elements, each arrays in their own right. Look down to the line $db->execute() - this function takes two parameters, which are the prepared statement to execute, and the array of values to pass to it. When PEAR::DB fills in the question marks in the prepared statement passed in parameter one of execute(), it iterates through the array passed as parameter two - element zero of the array is used for the first question mark, element one is used for the second, etc.

Going back to the $data array, you should now realise that the reason it is an array of arrays is because each child array holds one complete set of values for the prepared statement, ready to be passed into $db->execute() later on. The first set of values is "Gabor" and 25, which will be turned into this:

INSERT INTO people VALUES ('Gabor', 10, 25);");

Moving on, $db->prepare() is what actually sets up the prepared statement. It takes the SQL statement to use as its parameter, with question marks being used wherever values need to be provided later. As you can see in the code, you can mix hard-coded values and question marks freely, and you should take advantage of this so that do as little work as possible.

Calling prepare() returns the index number of the prepared statement to use, which is just an integer. This needs to be stored away in a variable so that you can specify which prepared statement you want to use when you call execute().

The actual execution of the prepared statement is inside a while loop. The loop iterates through each element in the $data array, extracting its key and value into $var and $val respectively, and each time we have an element we call execute().

As mentioned already, execute() takes two parameters - the prepared statement to execute, and the values to pass to it. In the example code above, the return value from the $db->prepare() line is used as parameter one, and the $val value extracted from the $data array is sent in as parameter two. All being well, that should execute the prepared statement three times, as we have three sets of data to be inserted.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.