Using Prepared Parameterized Statements in a Class

MODX logo

In the previous article, we 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:

[code languge=”php”]
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");
}
[/code]

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:

[code languge=”php”]
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);
}
[/code]

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):

[code language=”php”]
public function updateUser($id, $username, $password) {
$this->id = $password;
$this->username = $username;
$this->password = $password;
$this->updateStatement->execute();
}
[/code]

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.


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 *