Chapter 3. Zend_Db
Prev |  | Next

## Chapter 3. Zend_Db

## 3.1. Zend_Db_Adapter

### 3.1.1. Introduction

`Zend_Db_Adapter` is the database API abstraction layer for the Zend Framework. Based on PDO, you can use `Zend_Db_Adapter` to connect to and work with any of the supported SQL database systems using the same API. These include Microsoft SQL Server, MySQL, PostgreSQL, SQLite, and others.

To create an instance of `Zend_Db_Adapter` for your particular database backend, you need to call `Zend_Db::factory()` with the name of the adapter and an array of parameters describing the connection. For example, to connect to a MySQL database called "camelot" on the local host as a user named "malory":

    <?php

    require_once 'Zend/Db.php';

    $params = array ('host'     => '127.0.0.1',
                     'username' => 'malory',
                     'password' => '******',
                     'dbname'   => 'camelot');

    $db = Zend_Db::factory('PDO_MYSQL', $params);

    ?>

Similarly, to connect to an SQLite database called "camelot":

    <?php

    require_once 'Zend/Db.php';

    $params = array ('dbname' => 'camelot');

    $db = Zend_Db::factory('PDO_SQLITE', $params);

    ?>

Either way, you will be able to use the exact same API to query the database.

### 3.1.2. Quoting Against SQL Injection

You should always quote values that are to be used in an SQL statement; this is to help prevent SQL injection attacks. `Zend_Db_Adapter` provides two methods (via the underlying PDO object) to help you manually quote values.

The first of these is the `quote()` method. It will quote a scalar value appropriately for your database adapter; if you attempt to quote an array, it will return a comma-separated string of the array values, each properly quoted (this is useful for functions that take a list parameter).

    <?php

    // create a $db object, assuming Mysql as the adapter.

    // quote a scalar
    $value = $db->quote('St John"s Wort');
    // $value is now '"St John\"s Wort"' (note the surrounding quotes)

    // quote an array
    $value = $db->quote(array('a', 'b', 'c');
    // $value is now '"a", "b", "c"' (a comma-separated string)

    ?>

The second is the `quoteInto()` method. You provide a base string with a question-mark placeholder, and then one scalar or array to quote into it. This is useful for constructing queries and clauses as-you-go. Scalars and arrays work just as in the `quote()` method.

    <?php

    // create a $db object, assuming Mysql as the adapter.

    // quote a scalar into a WHERE clause
    $where = $db->quoteInto('id = ?', 1);
    // $where is now 'id = "1"' (note the surrounding quotes)

    // quote an array into a WHERE clause
    $where = $db->quoteInto('id IN(?)', array(1, 2, 3));
    // $where is now 'id IN("1", "2", "3")' (a comma-separated string)

    ?>

### 3.1.3. Direct Queries

Once you have a `Zend_Db_Adapter` instance, you can execute queries directly in SQL. `Zend_Db_Adapter` passes these queries to the underlying PDO object, which prepares and executes them, and then passes back a PDOStatement object for you to manipulate the results (if any).

    <?php

    // create a $db object, and then query the database
    // with a properly-quoted SQL statement.
    $sql = $db->quoteInto(
        'SELECT * FROM example WHERE date > ?',
        '2006-01-01'
    );
    $result = $db->query($sql);

    // use the PDOStatement $result to fetch all rows as an array
    $rows = $result->fetchAll();

    ?>

You may bind data into your query automatically. This means you can set multiple named placeholders in the query, and then pass an array of data that is substituted for those placeholders. The substituted values are automatically quoted for you, providing greater security against SQL injection attacks.

    <?php

    // create a $db object, and then query the database.
    // this time, use placeholder binding.
    $result = $db->query(
        'SELECT * FROM example WHERE date > :placeholder',
        array('placeholder' => '2006-01-01')
    );

    // use the PDOStatement $result to fetch all rows as an array
    $rows = $result->fetchAll();

    ?>

Optionally, you may wish to prepare and bind data to SQL statements manually. To do so, use the `prepare()` method to get a prepared `PDOStatement` that you can manipulate directly.

    <?php

    // create a $db object, and then query the database.
    // this time, prepare a PDOStatement for manual binding.
    $stmt = $db->prepare('SELECT * FROM example WHERE date > :placeholder');
    $stmt->bindValue('placeholder', '2006-01-01');
    $stmt->execute();

    // use the PDOStatement to fetch all rows as an array
    $rows = $stmt->fetchAll();

    ?>

### 3.1.4. Transactions

By default, PDO (and thus `Zend_Db_Adapter`) are in "auto-commit" mode. This means that all queries are committed as they are executed. If you wish to execute within a transaction, simply call the `beginTransaction()` method, then either `commit()` or `rollBack()` your changes. `Zend_Db_Adapter` returns to auto-commit mode until you call `beginTransaction()` again.

    <?php

    // create a $db object, and then start a transaction.
    $db->beginTransaction();

    // attempt a query.
    // if it succeeds, commit the changes;
    // if it fails, roll back.
    try {
        $db->query(...);
        $db->commit();
    } catch (Exception $e) {
        $db->rollBack();
        echo $e->getMessage();
    }

    ?>

### 3.1.5. Inserting Rows

As a convenience, you may use the `insert()` method to create an INSERT statement for you and bind data to be inserted into it. (The bound data is quoted for you automatically to help prevent SQL injection attacks.)

The return value is not the last inserted ID, as the table may not have an auto-incremented column; instead, the return value is the number of rows affected (usually 1). If you want the ID of the last inserted record, call the `lastInsertId()` method after the insert.

    <?php

    //
    // INSERT INTO round_table
    //     (noble_title, first_name, favorite_color)
    //     VALUES ("King", "Arthur", "blue");
    //

    // create a $db object, and then...
    // the row data to be inserted in column => value format
    $row = array (
        'noble_title'    => 'King',
        'first_name'     => 'Arthur',
        'favorite_color' => 'blue',
    );

    // the table into which the row should be inserted
    $table = 'round_table';

    // insert the row and get the row ID
    $rows_affected = $db->insert($table, $row);
    $last_insert_id = $db->lastInsertId();

    ?>

### 3.1.6. Updating Rows

As a convenience, you may use the `update()` method to create an UPDATE statement for you and bind data to be updated into it. (The bound data is quoted for you automatically to help prevent SQL injection attacks.)

You may provide an optional WHERE clause to tell which rows to update. (Note that the WHERE clause is not a bound parameter, so you need to quote values in it yourself.)

    <?php

    //
    // UPDATE round_table
    //     SET favorite_color = "yellow"
    //     WHERE first_name = "Robin";
    //

    // create a $db object, and then...
    // the new values to set in the update, in column => value format.
    $set = array (
        'favorite_color' => 'yellow',
    );

    // the table to update
    $table = 'round_table';

    // the WHERE clause
    $where = $db->quoteInto('first_name = ?', 'Robin');

    // update the table and get the number of rows affected
    $rows_affected = $db->update($table, $set, $where);

    ?>

### 3.1.7. Deleting Rows

As a convenience, you may use the `delete()` method to create a DELETE statement for you; you may provide an optional WHERE clause to tell which rows to delete. (Note that the WHERE clause is not a bound parameter, so you need to quote values in it yourself.)

    <?php

    //
    // DELETE FROM round_table
    //     WHERE first_name = "Patsy";
    //

    // create a $db object, and then...
    // the table to delete from
    $table = 'round_table';

    // the WHERE clause
    $where = $db->quoteInto('first_name = ?', 'Patsy');

    // update the table and get the number of rows affected
    $rows_affected = $db->delete($table, $where);

    ?>

### 3.1.8. Fetching Rows

Although you may query the database directly with the `query()` method, frequently all you need to do is select rows and get the results. The `fetch*()` series of methods does this for you. For each of the `fetch*()` methods, you pass an SQL SELECT statement; if you use named placeholders in the statement, you may also pass an array of bind values to be quoted and replaced into the statement for you. The `fetch*()` methods are:

- `fetchAll()`

- `fetchAssoc()`

- `fetchCol()`

- `fetchOne()`

- `fetchPairs()`

- `fetchRow()`

    <?php

    // create a $db object, and then...

    // fetch all columns of all rows as a sequential array
    $result = $db->fetchAll(
        "SELECT * FROM round_table WHERE noble_title = :title",
        array('title' => 'Sir')
    );

    // fetch all columns of all rows as an associative array;
    // the first column is used as the array key.
    $result = $db->fetchAssoc(
        "SELECT * FROM round_table WHERE noble_title = :title",
        array('title' => 'Sir')
    );

    // fetch the first column of all rows returned
    $result = $db->fetchCol(
        "SELECT first_name FROM round_table WHERE noble_title = :title",
        array('title' => 'Sir')
    );

    // fetch only the first value
    $result = $db->fetchOne(
        "SELECT COUNT(*) FROM round_table WHERE noble_title = :title",
        array('title' => 'Sir')
    );

    // fetch a series of key-value pairs; the first column is
    // the array key, the second column is the array value
    $result = $db->fetchPairs(
        "SELECT first_name, favorite_color FROM round_table WHERE noble_title = :title",
        array('title' => 'Sir')
    );

    // fetch only the first row returned
    $result = $db->fetchRow(
        "SELECT * FROM round_table WHERE first_name = :name",
        array('name' => 'Lancelot')
    );

    ?>

Prev |  | Next
2.4. Plugins | Home | 3.2. Zend_Db_Select