Using Parameterized Prepared Statements in a Class

In my previous article, I discussed how to create a parameterized, prepared statement that can be reused in a loop so that prepare() only needs to be called once. But what if you’re operating inside a class file where it’s not practical to have the prepare() statement and the code that uses it in the same method?

Consider a class-based version of the example in the previous article. In that example, we looped through a bunch of user data, setting one user’s password each time through the loop. The prepared statement and the code to bind the user variables to the parameters in the statement came just before the loop.

In a class, though, you might well have a method called updateUser(), which updates a single user. A loop in another method will call UpdateUser() once for each user. We can’t put the prepared statement in that method, or it will be called once for each user (defeating the purpose of the parameterized, prepared statement).

Since the parameterized statement only needs to occur once, it makes sense to put it in the class’s init() method and store it in a class variable:

public function init() {

    $table = $this->modx->getTableName('modUser');
    $this->updateStatement = $modx->prepare("UPDATE " . $table . " SET `password`=:password,
        `username`=:username, `hash_class`='hashing.modMD5',
        `cachepwd`='', `salt`='' WHERE id=:id");
}

We’ve made the update statement a class variable so that it will be available throughout the class. We’ve got a problem, though. We need to bind the parameters in the prepared statement to some variables, but if they’re local variables in the init() method, the prepared statement won’t work anywhere else.

The answer is to use class variables in the bindParam() statements, like this:

public function init() {

    $table = $this->modx->getTableName('modUser');
    $this->updateStatement = $modx->prepare("UPDATE " . $table . " SET `password`=:password,
        `username`=:username, `hash_class`='hashing.modMD5',
        `cachepwd`='', `salt`='' WHERE id=:id");

    $this->updateStatement->bindParam(':password', $this->password);
    $this->updateStatement->bindParam(':username', $this->username);
    $updateStatement->bindParam(':id', $this->id, PDO::PARAM_INT);
}

Now that we have the prepared statement and the bindings set up, we can do this in our updateUser() method (or anywhere else in the class):

public function updateUser($id, $username, $password) {
    $this->id = $password;
    $this->username = $username;
    $this->password = $password;
    $this->updateStatement->execute();
}

Because we bound those three variables to the parameters of the prepared statement back in the init() method, we can set them anywhere in the class and call the prepared statement’s execute() method. Note that we wouldn’t have to set the values of the three class variables in the updateUser() method (though in this case it makes sense to do so). They could be set anywhere in the class.

---

Bob Ray is the author of the MODX: The Official Guide and dozens of MODX Extras including QuickEmail, NewsPublisher, SiteCheck, GoRevo, Personalize, EZfaq, MyComponent and many more. His website is Bob’s Guides. It not only includes a plethora of MODX tutorials but there are some really great bread recipes there, as well.