Using Parameterized Prepared Statements to Retrieve Data

In previous articles, we discussed using PDO to do fast writes to the database. If you’re going to write a bunch of objects to the database, though, it’s wasteful to call prepare() for each one.

In this article, we’ll look at how to use parameterized statements to write a series of objects to the database and only call prepare() once.

Parameterized?

In the previous article, we put the values of the variables directly in the SQL of the prepare() statement. Here’s the code we used:

[code language=”php”]
$password = ‘SomePassword’;
$userId = 12;

$table = $modx->getTableName(‘modUser’);
$stmt = $modx->prepare("UPDATE " . $table . " SET `password`=’" .
$password . "’, `hash_class`=’hashing.modMD5′, `cachepwd`=”,
`salt`=” WHERE id=" . $userId);
[/code]

Prepared statements are faster and safer. The values are escaped to mMODX logoake sure they will cause no harm. This prevents a number of possible hacking attacks when you use user-supplied data from the $_POST, $_REQUEST, or $_GET array. MySQL also pre-processes prepared statements to optimize them for efficient communication with the database.

It’s a shame, though, to call prepare() for every row you want to store in the database. Luckily, you don’t have to. You can use a “parameterized” statement, which is essentially a statement with variables in it that you can reset for each row you save. The prepare() method is called only once. It is escaped and optimized, and only the variables are updated for each write. Here’s an example using the user password code above:

[code languge=”php”]
$table = $modx->getTableName(‘modUser’);
$updateStatement = $modx->prepare("UPDATE " . $table . "
SET `password`=:password,
`username`=:username,
`hash_class`=’hashing.modMD5′,
`cachepwd`=”,
`salt`=”
WHERE id=:id");

$updateStatement->bindParam(‘:password’, $password);
$updateStatement->bindParam(‘:username’, $username);
$updateStatement->bindParam(‘:id’, $id, PDO::PARAM_INT);
[/code]

 How it Works

The parts of the statement with colons in front of them are the parameters. Think of the colon like the dollar sign used for PHP variables. It tells the database code (e.g., MySQL) that they are variables (aka parameters). The three bindParam statements tell the database engine which PHP variables should be tied to each parameter. Notice that for the :id parameter, we’ve identified the variable as an integer. The default type is PDO::PARAM_STR — a string variable — so we don’t have to do this for strings, but we do for other kinds of variables. The most commonly used options are: PDO::PARAM_STR, PDO::PARAM_INT, PDO::PARAM_BOOL, and PDO::PARAM_NULL. The full list is here.

The Code

Now that we’ve created our prepared statement (in the code above), we can use it over and over as shown in the code below. Assume that we have the user data in an array of arrays called $user, where each member is an array like this:

[code language=”php”]
$users = array(
array(
‘username’ => ‘SomeUserName’,
‘password’ => ‘SomePassword’,
‘id’ => somenumber,
),
/* etc. */
);

foreach ($user as $user) {
$username = $user[‘username’],
$password = $user[‘password’],
$id = $user[‘id’];
$updateStatement->execute();
}
[/code]

 

Each time through the loop, the the “variables” in the prepared statement (:password, :username, :id) are replaced with the the values of the three bound variables. We used an array for the source of the data, but it could just as well have been a CSV file.

In the next article, we’ll look at how to do the same thing in a class, where the execution of the prepared statement might be in a different method than the prepared statement itself.


For more information on how to use MODX to create a web site, see my web site Bob’s Guides, or better yet, buy my book: MODX: The Official Guide.

Looking for quality MODX Web Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , | Posted under MODX, MODX | RSS 2.0

Author Spotlight

Bob Ray

Bob Ray is the author of MODX: The Official Guide and over 30 MODX add-on components. He hosts Bob's Guides, a source of valuable information for MODX users, and has been very active in the MODX Forums with over 19,000 posts.

Leave a Reply

Your email address will not be published. Required fields are marked *