Balanced Braces

Prepared statements are a common thing among databases. They’re useful when you’re going to be running the same query multiple times but with different values inside of it. The common steps when working with prepared statements are as follows

1. Declare your query and where the placeholders are.
2. Run that prepared statement by just giving data – the data will fall into the placeholders

// our people to add to the database ...
$john = array('john', 34);
$bill = array('bill, 34);

// prepare the query. note the '?' placeholders
$q = 'INSERT INTO person ( name, age) VALUES (?, ?)';
$statement = $mdb2->prepare($q);

// execute the prepared statement with our people
$statement->execute($john);
$statement->execute($bill);

Of course you can rapidly execute lots of data pretty easily by using one array and a foreach to go through it . . .

// our people to add to the database ...
$people = (   ('john', 34),
              ('bill, 37),
              ('joe, 32),
              ('sarah, 55)  );

// prepare the query. note the '?' placeholders
$q = 'INSERT INTO person ( name, age) VALUES (?, ?)';
$statement = $mdb2->prepare($q);

// execute the prepared statement with our people
foreach($people as $person) {
    $statement->execute($person)
}

Benefit

The benefit to doing it this way, aside from the ‘look at me I’m a PHP/database whiz’ factor, is that it’s faster and easier on the database. Once a query is prepared, the database doesn’t have to parse through the query each time you run that query.

Returning Results

If you need to do a SELECT vs. something like an INSERT or an UPDATE, then you just have to include some parameters to prepare()…

// our people to find in the database ...
$john = array('john', 34);
$bill = array('bill, 34);

// prepare the query. now we need to add a third parameter to prepare()
// which states our return type.
$q = '    SELECT * FROM person WHERE name = ? AND age = ?';
$statement = $mdb2->prepare($q, null, MDB2_PREPARE_RESULT);

// execute the prepared statement with our people
$john_results = $statement->execute($john);
$bill_results = $statement->execute($bill);

That third parameter to prepare() can be either MDB2_PREPARE_RESULT or MDB2_PREPARE_MANIP. Both pretty self explanatory. The second parameter is to declare the data types that the placeholders will be. MDB2 guesses at this automatically so I won’t go into too much detail on it.

For more on MDB2, check out PEAR’s own documentation on it.