Prepared Statements

From Achievo/ATK Wiki

Jump to: navigation, search

ATK Howto: Prepared Statements

Complexity: Intermediate
Author: Peter C. Verhage <peter@achievo.org>

List of other Howto's

As of ATK 6.5, ATK has support for prepared statements. Prepared statements are the ability to set up a statement once, and then execute it many times with different parameters. They are designed to replace building ad hoc query strings, and do so in a more secure and efficient manner.

Contents

Creating a prepared statement

Creating a prepared statement is easy:

$stmt = $this->getDb()->prepare("SELECT * FROM customer WHERE lastname = ?"); // within a node or attribute
$stmt = atkGetDb()->prepare("SELECT * FROM customer WHERE lastname = ?"); // anywhere

Once you have created a statement you can execute the statement by calling the execute method. Part of executing a prepared statement is specifying the values for bind parameters (if there are any):

$stmt->execute(array('Doe'));

Bind parameters

ATK supports two kinds of bind parameters; anonymous bind parameters which use a question mark as placeholder (?) and named bind parameters which use a colon followed by a valid identifier (:param). The two can be fixed, but this isn't recommended! Some examples:

$stmt = $this->getDb()->prepare("SELECT * FROM customer WHERE firstname = ? AND lastname = ?");
$stmt->execute(array('John', 'Doe'));
 
$stmt = $this->getDb()->prepare("SELECT * FROM customer WHERE firstname = :firstname AND lastname = :lastname");
$stmt->execute(array('firstname' => 'John', 'lastname' => 'Doe'));
 
$stmt = $this->getDb()->prepare("SELECT * FROM customer WHERE firstname LIKE :name OR lastname LIKE :name");
$stmt->execute(array('name' => '%john%'));

The main benefit of named bind parameters is that the same parameter can be used multiple times in the same query. Named bind parameters also make the parameter to query mapping more readable / clear. ATK supports named bind parameters for all database drivers, even though your database might not support them native. ATK will convert the named bind parameters to anonymous bind parameters before executing the query. Even for database drivers that don't support bind parameters (yet) you can use bind parameters, ATK will replace them using proper escaping in the query itself before executing the query.

Insert / update / delete statements

Prepared statements can of-course also be used for insert, update and delete statements. Using a prepared statement might even mean that your database can execute the query more efficiently if you need the execute the same query multiple times with different values:

$customer = array(...);
foreach ($customers as $customer) {
  $stmt = $this->getDb()->prepare("INSERT INTO customer (firstname, lastname) VALUES(:firstname, :lastname)");
  $stmt->execute($customer);
}

If you are using the MySQLi driver you can get the auto-increment key value after an insert using:

$stmt->getInsertId(); // MySQL only!

Using an iterator

Once you've executed a select statement you can fetch rows one-by-one using an iterator. You can either iterate over the statement object itself or use the getIterator() method:

foreach ($stmt->getIterator() as $row) {
  echo $row['firstname'].' '.$row['lastname'].PHP_EOL;
}

You should however never iterate with more than one iterator on the same statement object at the same time. E.g., never do this:

foreach ($stmt->getIterator() as $row1) {
  foreach ($stmt->getIterator() as $row2) {
    // never do this!
  }
}

Convenience methods

You can also use the special convenience methods for fetching only one or multiple rows at once:

$stmt->getFirstRow(); // returns the 1st row (array)
 
$stmt->getFirstValue(); // returns the value of the first column of the 1st row
$stmt->getFirstValue(2); // returns the value of the 3rd column of the 1st row
$stmt->getFirstValue('lastname') // returns the value of the lastname column of the 1st row
 
$stmt->getAllRows(); // returns all rows in an array
 
$stmt->getAllRowsAssoc(); // returns all rows in an array indexed by the 1st column
$stmt->getAllRowsAssoc(2); // returns all rows in an array indexed by the 3rd column
$stmt->getAllRowsAssoc('id') // returns all rows in an array indexed by the id column
 
$stmt->getAllValues(); // returns an array with the value of the first column of each row
$stmt->getAllValues(2); // returns an array with the value of the 3rd column of each row
$stmt->getAllValues('firstname'); // returns an array with the value of the firstname column of each row
 
$stmt->getAllValuesAssoc(); // returns an array with the value of the 2nd column indexed by the 1st column for each row
$stmt->getAllValuesAssoc(1, 0); // returns an array with the value of the 1st column indexed by the 2nd column for each row
$stmt->getAllValuesAssoc('id', 'lastname'); // returns an array with the value of the lastname column indexed by the id column for each row

If you are working with large data sets you should always try to avoid these convenience methods, except the getFirstRow and getFirstValue methods, because they will load all rows into memory, use an iterator instead.

Closing a prepared statement

Once you have no use for a prepared statement anymore you can close it:

$stmt->close();

If you forget to do this, the statement is automatically closed by the destructor. However you never know when the destructor will be called so it's good practice to always close a statement when you are done with it, to avoid unnecessary leaking of (expensive) resources.

Use of prepared statements within atkNode

ATK's atkNode class only uses prepared statements for select queries at the moment. This means you can use bind parameters in a where clause you add to a node's select or count query:

  $node->select()->where('lastname = ?', array('Doe'))->getAllRows();
  $node->select()->where('lastname = ?', array('Doe'))->getRowCount();
  $node->select()->where('firstname = ? AND lastname = ?', array('John', 'Doe'))->getFirstRow();

Another benefit of the use of prepared statements for select queries in nodes is that you can now use an iterator to fetch rows one-by-one:

foreach ($node->select()->where('lastname = ?', array('Doe'))->getIterator() as $row) {
  echo $row['firstname'].' '.$row['lastname'].PHP_EOL;
}

This was already possible in older versions of ATK, but thanks to the use of statements we don't need to pre-fetch all rows in memory before we start iterating.

Personal tools
Navigation