Previous Next

Zend_Db_Adapter

Zend_Db and its related classes provide a simple SQL database interface for Zend Framework. The Zend_Db_Adapter is the basic class you use to connect your PHP application to an RDBMS. There is a different Adapter class for each brand of RDBMS.

The Zend_Db Adapters create a bridge from the vendor-specific PHP extensions to a common interface, to help you write PHP applications once and deploy with multiple brands of RDBMS with very little effort.

The interface of the Adapter class is similar to the interface of the » PHP Data Objects extension. Zend_Db provides Adapter classes to PDO drivers for the following RDBMS brands:

In addition, Zend_Db provides Adapter classes that utilize PHP database extensions for the following RDBMS brands:

Note:

Each Zend_Db Adapter uses a PHP extension. You must have the respective PHP extension enabled in your PHP environment to use a Zend_Db Adapter. For example, if you use any of the PDO Zend_Db Adapters, you need to enable both the PDO extension and the PDO driver for the brand of RDBMS you use.

Connecting to a Database using an Adapter

This section describes how to create an instance of a database Adapter. This corresponds to making a connection to your RDBMS server from your PHP application.

Using a Zend_Db Adapter Constructor

You can create an instance of an Adapter using its constructor. An Adapter constructor takes one argument, which is an array of parameters used to declare the connection.

Example #1 Using an Adapter constructor

 '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test'
));

Using the Zend_Db Factory

As an alternative to using an Adapter constructor directly, you can create an instance of an Adapter using the static method Zend_Db::factory(). This method dynamically loads the Adapter class file on demand, using Zend_Loader::loadClass().

The first argument is a string that names the base name of the Adapter class. For example the string 'Pdo_Mysql' corresponds to the class Zend_Db_Adapter_Pdo_Mysql. The second argument is the same array of parameters you would have given to the Adapter constructor.

Example #2 Using the Adapter factory method

 '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test'
));

If you create your own class that extends Zend_Db_Adapter_Abstract, but you do not name your class with the "Zend_Db_Adapter" package prefix, you can use the factory() method to load your Adapter if you specify the leading portion of the adapter class with the 'adapterNamespace' key in the parameters array.

Example #3 Using the Adapter factory method for a custom adapter class

 '127.0.0.1',
    'username'         => 'webuser',
    'password'         => 'xxxxxxxx',
    'dbname'           => 'test',
    'adapterNamespace' => 'MyProject_Db_Adapter'
));

Using Zend_Config with the Zend_Db Factory

Optionally, you may specify either argument of the factory() method as an object of type Zend_Config.

If the first argument is a config object, it is expected to contain a property named adapter, containing the string naming the adapter class name base. Optionally, the object may contain a property named params, with subproperties corresponding to adapter parameter names. This is used only if the second argument of the factory() method is absent.

Example #4 Using the Adapter factory method with a Zend_Config object

In the example below, a Zend_Config object is created from an array. You can also load data from an external file, for example with Zend_Config_Ini or Zend_Config_Xml.

 array(
            'adapter' => 'Mysqli',
            'params' => array(
                'dbname' => 'test',
                'username' => 'webuser',
                'password' => 'secret',
            )
        )
    )
);

$db = Zend_Db::factory($config->database);

The second argument of the factory() method may be an associative array containing entries corresponding to adapter parameters. This argument is optional. If the first argument is of type Zend_Config, it is assumed to contain all parameters, and the second argument is ignored.

Adapter Parameters

The list below explains common parameters recognized by Zend_Db Adapter classes.

  • host: a string containing a hostname or IP address of the database server. If the database is running on the same host as the PHP application, you may use 'localhost' or '127.0.0.1'.

  • username: account identifier for authenticating a connection to the RDBMS server.

  • password: account password credential for authenticating a connection to the RDBMS server.

  • dbname: database instance name on the RDBMS server.

  • port: some RDBMS servers can accept network connections on a administrator-specified port number. The port parameter allow you to specify the port to which your PHP application connects, to match the port configured on the RDBMS server.

  • options: this parameter is an associative array of options that are generic to all Zend_Db_Adapter classes.

  • driver_options: this parameter is an associative array of additional options that are specific to a given database extension. One typical use of this parameter is to set attributes of a PDO driver.

  • adapterNamespace: names the initial part of the class name for the adapter, instead of 'Zend_Db_Adapter'. Use this if you need to use the factory() method to load a non-Zend database adapter class.

Example #5 Passing the case-folding option to the factory

You can specify this option by the constant Zend_Db::CASE_FOLDING. This corresponds to the ATTR_CASE attribute in PDO and IBM DB2 database drivers, adjusting the case of string keys in query result sets. The option takes values Zend_Db::CASE_NATURAL (the default), Zend_Db::CASE_UPPER, and Zend_Db::CASE_LOWER.

 Zend_Db::CASE_UPPER
);

$params = array(
    'host'           => '127.0.0.1',
    'username'       => 'webuser',
    'password'       => 'xxxxxxxx',
    'dbname'         => 'test',
    'options'        => $options
);

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

Example #6 Passing the auto-quoting option to the factory

You can specify this option by the constant Zend_Db::AUTO_QUOTE_IDENTIFIERS. If the value is true (the default), identifiers like table names, column names, and even aliases are delimited in all SQL syntax generated by the Adapter object. This makes it simple to use identifiers that contain SQL keywords, or special characters. If the value is false, identifiers are not delimited automatically. If you need to delimit identifiers, you must do so yourself using the quoteIdentifier() method.

 false
);

$params = array(
    'host'           => '127.0.0.1',
    'username'       => 'webuser',
    'password'       => 'xxxxxxxx',
    'dbname'         => 'test',
    'options'        => $options
);

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

Example #7 Passing PDO driver options to the factory

 true
);

$params = array(
    'host'           => '127.0.0.1',
    'username'       => 'webuser',
    'password'       => 'xxxxxxxx',
    'dbname'         => 'test',
    'driver_options' => $pdoParams
);

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

echo $db->getConnection()->getAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY);

Managing Lazy Connections

Creating an instance of an Adapter class does not immediately connect to the RDBMS server. The Adapter saves the connection parameters, and makes the actual connection on demand, the first time you need to execute a query. This ensures that creating an Adapter object is quick and inexpensive. You can create an instance of an Adapter even if you are not certain that you need to run any database queries during the current request your application is serving.

If you need to force the Adapter to connect to the RDBMS, use the getConnection() method. This method returns an object for the connection as represented by the respective PHP database extension. For example, if you use any of the Adapter classes for PDO drivers, then getConnection() returns the PDO object, after initiating it as a live connection to the specific database.

It can be useful to force the connection if you want to catch any exceptions it throws as a result of invalid account credentials, or other failure to connect to the RDBMS server. These exceptions are not thrown until the connection is made, so it can help simplify your application code if you handle the exceptions in one place, instead of at the time of the first query against the database.

Example #8 Handling connection exceptions

getConnection();
} catch (Zend_Db_Adapter_Exception $e) {
    // perhaps a failed login credential, or perhaps the RDBMS is not running
} catch (Zend_Exception $e) {
    // perhaps factory() failed to load the specified Adapter class
}

The example database

In the documentation for Zend_Db classes, we use a set of simple tables to illustrate usage of the classes and methods. These example tables could store information for tracking bugs in a software development project. The database contains four tables:

  • accounts stores information about each user of the bug-tracking database.

  • products stores information about each product for which a bug can be logged.

  • bugs stores information about bugs, including that current state of the bug, the person who reported the bug, the person who is assigned to fix the bug, and the person who is assigned to verify the fix.

  • bugs_products stores a relationship between bugs and products. This implements a many-to-many relationship, because a given bug may be relevant to multiple products, and of course a given product can have multiple bugs.

The following SQL data definition language pseudocode describes the tables in this example database. These example tables are used extensively by the automated unit tests for Zend_Db.

CREATE TABLE accounts (
  account_name      VARCHAR(100) NOT NULL PRIMARY KEY
);

CREATE TABLE products (
  product_id        INTEGER NOT NULL PRIMARY KEY,
  product_name      VARCHAR(100)
);

CREATE TABLE bugs (
  bug_id            INTEGER NOT NULL PRIMARY KEY,
  bug_description   VARCHAR(100),
  bug_status        VARCHAR(20),
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  assigned_to       VARCHAR(100) REFERENCES accounts(account_name),
  verified_by       VARCHAR(100) REFERENCES accounts(account_name)
);

CREATE TABLE bugs_products (
  bug_id            INTEGER NOT NULL REFERENCES bugs,
  product_id        INTEGER NOT NULL REFERENCES products,
  PRIMARY KEY       (bug_id, product_id)
);

Also notice that the bugs table contains multiple foreign key references to the accounts table. Each of these foreign keys may reference a different row in the accounts table for a given bug.

The diagram below illustrates the physical data model of the example database.

zend.db.adapter.example-database.png

Reading Query Results

This section describes methods of the Adapter class with which you can run SELECT queries and retrieve the query results.

Fetching a Complete Result Set

You can run a SQL SELECT query and retrieve its results in one step using the fetchAll() method.

The first argument to this method is a string containing a SELECT statement. Alternatively, the first argument can be an object of class Zend_Db_Select. The Adapter automatically converts this object to a string representation of the SELECT statement.

The second argument to fetchAll() is an array of values to substitute for parameter placeholders in the SQL statement.

Example #9 Using fetchAll()

fetchAll($sql, 2);

Changing the Fetch Mode

By default, fetchAll() returns an array of rows, each of which is an associative array. The keys of the associative array are the columns or column aliases named in the select query.

You can specify a different style of fetching results using the setFetchMode() method. The modes supported are identified by constants:

  • Zend_Db::FETCH_ASSOC: return data in an array of associative arrays. The array keys are column names, as strings. This is the default fetch mode for Zend_Db_Adapter classes.

    Note that if your select-list contains more than one column with the same name, for example if they are from two different tables in a JOIN, there can be only one entry in the associative array for a given name. If you use the FETCH_ASSOC mode, you should specify column aliases in your SELECT query to ensure that the names result in unique array keys.

    By default, these strings are returned as they are returned by the database driver. This is typically the spelling of the column in the RDBMS server. You can specify the case for these strings, using the Zend_Db::CASE_FOLDING option. Specify this when instantiating the Adapter. See Passing the case-folding option to the factory.

  • Zend_Db::FETCH_NUM: return data in an array of arrays. The arrays are indexed by integers, corresponding to the position of the respective field in the select-list of the query.

  • Zend_Db::FETCH_BOTH: return data in an array of arrays. The array keys are both strings as used in the FETCH_ASSOC mode, and integers as used in the FETCH_NUM mode. Note that the number of elements in the array is double that which would be in the array if you used either FETCH_ASSOC or FETCH_NUM.

  • Zend_Db::FETCH_COLUMN: return data in an array of values. The value in each array is the value returned by one column of the result set. By default, this is the first column, indexed by 0.

  • Zend_Db::FETCH_OBJ: return data in an array of objects. The default class is the PHP built-in class stdClass. Columns of the result set are available as public properties of the object.

Example #10 Using setFetchMode()

setFetchMode(Zend_Db::FETCH_OBJ);

$result = $db->fetchAll('SELECT * FROM bugs WHERE bug_id = ?', 2);

// $result is an array of objects
echo $result[0]->bug_description;

Fetching a Result Set as an Associative Array

The fetchAssoc() method returns data in an array of associative arrays, regardless of what value you have set for the fetch mode.

Example #11 Using fetchAssoc()

setFetchMode(Zend_Db::FETCH_OBJ);

$result = $db->fetchAssoc('SELECT * FROM bugs WHERE bug_id = ?', 2);

// $result is an array of associative arrays, in spite of the fetch mode
echo $result[0]['bug_description'];

Fetching a Single Column from a Result Set

The fetchCol() method returns data in an array of values, regardless of the value you have set for the fetch mode. This only returns the first column returned by the query. Any other columns returned by the query are discarded. If you need to return a column other than the first, see Fetching a Single Column from a Result Set.

Example #12 Using fetchCol()

setFetchMode(Zend_Db::FETCH_OBJ);

$result = $db->fetchCol('SELECT bug_description, bug_id FROM bugs WHERE bug_id = ?', 2);

// contains bug_description; bug_id is not returned
echo $result[0];

Fetching Key-Value Pairs from a Result Set

The fetchPairs() method returns data in an array of key-value pairs, as an associative array with a single entry per row. The key of this associative array is taken from the first column returned by the SELECT query. The value is taken from the second column returned by the SELECT query. Any other columns returned by the query are discarded.

You should design the SELECT query so that the first column returned has unique values. If there are duplicates values in the first column, entries in the associative array will be overwritten.

Example #13 Using fetchPairs()

setFetchMode(Zend_Db::FETCH_OBJ);

$result = $db->fetchPairs('SELECT bug_id, bug_status FROM bugs');

echo $result[2];

Fetching a Single Row from a Result Set

The fetchRow() method returns data using the current fetch mode, but it returns only the first row fetched from the result set.

Example #14 Using fetchRow()

setFetchMode(Zend_Db::FETCH_OBJ);

$result = $db->fetchRow('SELECT * FROM bugs WHERE bug_id = 2');

// note that $result is a single object, not an array of objects
echo $result->bug_description;

Fetching a Single Scalar from a Result Set

The fetchOne() method is like a combination of fetchRow() with fetchCol(), in that it returns data only for the first row fetched from the result set, and it returns only the value of the first column in that row. Therefore it returns only a single scalar value, not an array or an object.

Example #15 Using fetchOne()

fetchOne('SELECT bug_status FROM bugs WHERE bug_id = 2');

// this is a single string value
echo $result;

Writing Changes to the Database

You can use the Adapter class to write new data or change existing data in your database. This section describes methods to do these operations.

Inserting Data

You can add new rows to a table in your database using the insert() method. The first argument is a string that names the table, and the second argument is an associative array, mapping column names to data values.

Example #16 Inserting to a table

 '2007-03-22',
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW'
);

$db->insert('bugs', $data);

Columns you exclude from the array of data are not specified to the database. Therefore, they follow the same rules that an SQL INSERT statement follows: if the column has a DEFAULT clause, the column takes that value in the row created, otherwise the column is left in a NULL state.

By default, the values in your data array are inserted using parameters. This reduces risk of some types of security issues. You don't need to apply escaping or quoting to values in the data array.

You might need values in the data array to be treated as SQL expressions, in which case they should not be quoted. By default, all data values passed as strings are treated as string literals. To specify that the value is an SQL expression and therefore should not be quoted, pass the value in the data array as an object of type Zend_Db_Expr instead of a plain string.

Example #17 Inserting expressions to a table

 new Zend_Db_Expr('CURDATE()'),
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW'
);

$db->insert('bugs', $data);

Retrieving a Generated Value

Some RDBMS brands support auto-incrementing primary keys. A table defined this way generates a primary key value automatically during an INSERT of a new row. The return value of the insert() method is not the last inserted ID, because the table might not have an auto-incremented column. Instead, the return value is the number of rows affected (usually 1).

If your table is defined with an auto-incrementing primary key, you can call the lastInsertId() method after the insert. This method returns the last value generated in the scope of the current database connection.

Example #18 Using lastInsertId() for an auto-increment key

insert('bugs', $data);

// return the last value generated by an auto-increment column
$id = $db->lastInsertId();

Some RDBMS brands support a sequence object, which generates unique values to serve as primary key values. To support sequences, the lastInsertId() method accepts two optional string arguments. These arguments name the table and the column, assuming you have followed the convention that a sequence is named using the table and column names for which the sequence generates values, and a suffix "_seq". This is based on the convention used by PostgreSQL when naming sequences for SERIAL columns. For example, a table "bugs" with primary key column "bug_id" would use a sequence named "bugs_bug_id_seq".

Example #19 Using lastInsertId() for a sequence

insert('bugs', $data);

// return the last value generated by sequence 'bugs_bug_id_seq'.
$id = $db->lastInsertId('bugs', 'bug_id');

// alternatively, return the last value generated by sequence 'bugs_seq'.
$id = $db->lastInsertId('bugs');

If the name of your sequence object does not follow this naming convention, use the lastSequenceId() method instead. This method takes a single string argument, naming the sequence literally.

Example #20 Using lastSequenceId()

insert('bugs', $data);

// return the last value generated by sequence 'bugs_id_gen'.
$id = $db->lastSequenceId('bugs_id_gen');

For RDBMS brands that don't support sequences, including MySQL, Microsoft SQL Server, and SQLite, the arguments to the lastInsertId() method are ignored, and the value returned is the most recent value generated for any table by INSERT operations during the current connection. For these RDBMS brands, the lastSequenceId() method always returns null.

Note: Why not use "SELECT MAX(id) FROM table"?

Sometimes this query returns the most recent primary key value inserted into the table. However, this technique is not safe to use in an environment where multiple clients are inserting records to the database. It is possible, and therefore is bound to happen eventually, that another client inserts another row in the instant between the insert performed by your client application and your query for the MAX(id) value. Thus the value returned does not identify the row you inserted, it identifies the row inserted by some other client. There is no way to know when this has happened.

Using a strong transaction isolation mode such as "repeatable read" can mitigate this risk, but some RDBMS brands don't support the transaction isolation required for this, or else your application may use a lower transaction isolation mode by design.

Furthermore, using an expression like "MAX(id)+1" to generate a new value for a primary key is not safe, because two clients could do this query simultaneously, and then both use the same calculated value for their next INSERT operation.

All RDBMS brands provide mechanisms to generate unique values, and to return the last value generated. These mechanisms necessarily work outside of the scope of transaction isolation, so there is no chance of two clients generating the same value, and there is no chance that the value generated by another client could be reported to your client's connection as the last value generated.

Updating Data

You can update rows in a database table using the update() method of an Adapter. This method takes three arguments: the first is the name of the table; the second is an associative array mapping columns to change to new values to assign to these columns.

The values in the data array are treated as string literals. See Inserting Data for information on using SQL expressions in the data array.

The third argument is a string containing an SQL expression that is used as criteria for the rows to change. The values and identifiers in this argument are not quoted or escaped. You are responsible for ensuring that any dynamic content is interpolated into this string safely. See Quoting Values and Identifiers for methods to help you do this.

The return value is the number of rows affected by the update operation.

Example #21 Updating rows

 '2007-03-23',
    'bug_status'      => 'FIXED'
);

$n = $db->update('bugs', $data, 'bug_id = 2');

If you omit the third argument, then all rows in the database table are updated with the values specified in the data array.

If you provide an array of strings as the third argument, these strings are joined together as terms in an expression separated by AND operators.

Example #22 Updating rows using an array of expressions

 '2007-03-23',
    'bug_status'      => 'FIXED'
);

$where[] = "reported_by = 'goofy'";
$where[] = "bug_status = 'OPEN'";

$n = $db->update('bugs', $data, $where);

// Resulting SQL is:
//  UPDATE "bugs" SET "update_on" = '2007-03-23', "bug_status" = 'FIXED'
//  WHERE ("reported_by" = 'goofy') AND ("bug_status" = 'OPEN')

Deleting Data

You can delete rows from a database table using the delete() method. This method takes two arguments: the first is a string naming the table.

The second argument is a string containing an SQL expression that is used as criteria for the rows to delete. The values and identifiers in this argument are not quoted or escaped. You are responsible for ensuring that any dynamic content is interpolated into this string safely. See Quoting Values and Identifiers for methods to help you do this.

The return value is the number of rows affected by the delete operation.

Example #23 Deleting rows

delete('bugs', 'bug_id = 3');

If you omit the second argument, the result is that all rows in the database table are deleted.

If you provide an array of strings as the second argument, these strings are joined together as terms in an expression separated by AND operators.

Quoting Values and Identifiers

When you form SQL queries, often it is the case that you need to include the values of PHP variables in SQL expressions. This is risky, because if the value in a PHP string contains certain symbols, such as the quote symbol, it could result in invalid SQL. For example, notice the imbalanced quote characters in the following query:

$name = "O'Reilly";
$sql = "SELECT * FROM bugs WHERE reported_by = '$name'";

echo $sql;
// SELECT * FROM bugs WHERE reported_by = 'O'Reilly'

Even worse is the risk that such code mistakes might be exploited deliberately by a person who is trying to manipulate the function of your web application. If they can specify the value of a PHP variable through the use of an HTTP parameter or other mechanism, they might be able to make your SQL queries do things that you didn't intend them to do, such as return data to which the person should not have privilege to read. This is a serious and widespread technique for violating application security, known as "SQL Injection" (see » http://en.wikipedia.org/wiki/SQL_Injection).

The Zend_Db Adapter class provides convenient functions to help you reduce vulnerabilities to SQL Injection attacks in your PHP code. The solution is to escape special characters such as quotes in PHP values before they are interpolated into your SQL strings. This protects against both accidental and deliberate manipulation of SQL strings by PHP variables that contain special characters.

Using quote()

The quote() method accepts a single argument, a scalar string value. It returns the value with special characters escaped in a manner appropriate for the RDBMS you are using, and surrounded by string value delimiters. The standard SQL string value delimiter is the single-quote (').

Example #24 Using quote()

quote("O'Reilly");
echo $name;
// 'O\'Reilly'

$sql = "SELECT * FROM bugs WHERE reported_by = $name";

echo $sql;
// SELECT * FROM bugs WHERE reported_by = 'O\'Reilly'

Note that the return value of quote() includes the quote delimiters around the string. This is different from some functions that escape special characters but do not add the quote delimiters, for example » mysql_real_escape_string().

Values may need to be quoted or not quoted according to the SQL datatype context in which they are used. For instance, in some RDBMS brands, an integer value must not be quoted as a string if it is compared to an integer-type column or expression. In other words, the following is an error in some SQL implementations, assuming intColumn has a SQL datatype of INTEGER

SELECT * FROM atable WHERE intColumn = '123'

You can use the optional second argument to the quote() method to apply quoting selectively for the SQL datatype you specify.

Example #25 Using quote() with a SQL type

quote($value, 'INTEGER');

Each Zend_Db_Adapter class has encoded the names of numeric SQL datatypes for the respective brand of RDBMS. You can also use the constants Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, and Zend_Db::FLOAT_TYPE to write code in a more RDBMS-independent way.

Zend_Db_Table specifies SQL types to quote() automatically when generating SQL queries that reference a table's key columns.

Using quoteInto()

The most typical usage of quoting is to interpolate a PHP variable into a SQL expression or statement. You can use the quoteInto() method to do this in one step. This method takes two arguments: the first argument is a string containing a placeholder symbol (?), and the second argument is a value or PHP variable that should be substituted for that placeholder.

The placeholder symbol is the same symbol used by many RDBMS brands for positional parameters, but the quoteInto() method only emulates query parameters. The method simply interpolates the value into the string, escapes special characters, and applies quotes around it. True query parameters maintain the separation between the SQL string and the parameters as the statement is parsed in the RDBMS server.

Example #26 Using quoteInto()

quoteInto("SELECT * FROM bugs WHERE reported_by = ?", "O'Reilly");

echo $sql;
// SELECT * FROM bugs WHERE reported_by = 'O\'Reilly'

You can use the optional third parameter of quoteInto() to specify the SQL datatype. Numeric datatypes are not quoted, and other types are quoted.

Example #27 Using quoteInto() with a SQL type

quoteInto("SELECT * FROM bugs WHERE bug_id = ?", '1234', 'INTEGER');

echo $sql;
// SELECT * FROM bugs WHERE reported_by = 1234

Using quoteIdentifier()

Values are not the only part of SQL syntax that might need to be variable. If you use PHP variables to name tables, columns, or other identifiers in your SQL statements, you might need to quote these strings too. By default, SQL identifiers have syntax rules like PHP and most other programming languages. For example, identifiers should not contain spaces, certain punctuation or special characters, or international characters. Also certain words are reserved for SQL syntax, and should not be used as identifiers.

However, SQL has a feature called delimited identifiers, which allows broader choices for the spelling of identifiers. If you enclose a SQL identifier in the proper types of quotes, you can use identifiers with spellings that would be invalid without the quotes. Delimited identifiers can contain spaces, punctuation, or international characters. You can also use SQL reserved words if you enclose them in identifier delimiters.

The quoteIdentifier() method works like quote(), but it applies the identifier delimiter characters to the string according to the type of Adapter you use. For example, standard SQL uses double-quotes (") for identifier delimiters, and most RDBMS brands use that symbol. MySQL uses back-quotes (`) by default. The quoteIdentifier() method also escapes special characters within the string argument.

Example #28 Using quoteIdentifier()

quoteIdentifier("order");

$sql = "SELECT * FROM $tableName";

echo $sql
// SELECT * FROM "order"

SQL delimited identifiers are case-sensitive, unlike unquoted identifiers. Therefore, if you use delimited identifiers, you must use the spelling of the identifier exactly as it is stored in your schema, including the case of the letters.

In most cases where SQL is generated within Zend_Db classes, the default is that all identifiers are delimited automatically. You can change this behavior with the option Zend_Db::AUTO_QUOTE_IDENTIFIERS. Specify this when instantiating the Adapter. See Passing the auto-quoting option to the factory.

Controlling Database Transactions

Databases define transactions as logical units of work that can be committed or rolled back as a single change, even if they operate on multiple tables. All queries to a database are executed within the context of a transaction, even if the database driver manages them implicitly. This is called auto-commit mode, in which the database driver creates a transaction for every statement you execute, and commits that transaction after your SQL statement has been executed. By default, all Zend_Db Adapter classes operate in auto-commit mode.

Alternatively, you can specify the beginning and resolution of a transaction, and thus control how many SQL queries are included in a single group that is committed (or rolled back) as a single operation. Use the beginTransaction() method to initiate a transaction. Subsequent SQL statements are executed in the context of the same transaction until you resolve it explicitly.

To resolve the transaction, use either the commit() or rollBack() methods. The commit() method marks changes made during your transaction as committed, which means the effects of these changes are shown in queries run in other transactions.

The rollBack() method does the opposite: it discards the changes made during your transaction. The changes are effectively undone, and the state of the data returns to how it was before you began your transaction. However, rolling back your transaction has no effect on changes made by other transactions running concurrently.

After you resolve this transaction, Zend_Db_Adapter returns to auto-commit mode until you call beginTransaction() again.

Example #29 Managing a transaction to ensure consistency

beginTransaction();

try {
    // Attempt to execute one or more queries:
    $db->query(...);
    $db->query(...);
    $db->query(...);

    // If all succeed, commit the transaction and all changes
    // are committed at once.
    $db->commit();

} catch (Exception $e) {
    // If any of the queries failed and threw an exception,
    // we want to roll back the whole transaction, reversing
    // changes made in the transaction, even those that succeeded.
    // Thus all changes are committed together, or none are.
    $db->rollBack();
    echo $e->getMessage();
}

Listing and Describing Tables

The listTables() method returns an array of strings, naming all tables in the current database.

The describeTable() method returns an associative array of metadata about a table. Specify the name of the table as a string in the first argument to this method. The second argument is optional, and names the schema in which the table exists.

The keys of the associative array returned are the column names of the table. The value corresponding to each column is also an associative array, with the following keys and values:

Metadata fields returned by describeTable()
Key Type Description
SCHEMA_NAME (string) Name of the database schema in which this table exists.
TABLE_NAME (string) Name of the table to which this column belongs.
COLUMN_NAME (string) Name of the column.
COLUMN_POSITION (integer) Ordinal position of the column in the table.
DATA_TYPE (string) RDBMS name of the datatype of the column.
DEFAULT (string) Default value for the column, if any.
NULLABLE (boolean) True if the column accepts SQL NULLs, false if the column has a NOT NULL constraint.
LENGTH (integer) Length or size of the column as reported by the RDBMS.
SCALE (integer) Scale of SQL NUMERIC or DECIMAL type.
PRECISION (integer) Precision of SQL NUMERIC or DECIMAL type.
UNSIGNED (boolean) True if an integer-based type is reported as UNSIGNED.
PRIMARY (boolean) True if the column is part of the primary key of this table.
PRIMARY_POSITION (integer) Ordinal position (1-based) of the column in the primary key.
IDENTITY (boolean) True if the column uses an auto-generated value.

Note: How the IDENTITY metadata field relates to specific RDBMS

The IDENTITY metadata field was chosen as an 'idiomatic' term to represent a relation to surrogate keys. This field can be commonly known by the following values:-

  • IDENTITY - DB2, MSSQL

  • AUTO_INCREMENT - MySQL

  • SERIAL - PostgreSQL

  • SEQUENCE - Oracle

If no table exists matching the table name and optional schema name specified, then describeTable() returns an empty array.

Closing a Connection

Normally it is not necessary to close a database connection. PHP automatically cleans up all resources and the end of a request. Database extensions are designed to close the connection as the reference to the resource object is cleaned up.

However, if you have a long-duration PHP script that initiates many database connections, you might need to close the connection, to avoid exhausting the capacity of your RDBMS server. You can use the Adapter's closeConnection() method to explicitly close the underlying database connection.

Example #30 Closing a database connection

closeConnection();

Note: Does Zend_Db support persistent connections?

The usage of persistent connections is not supported or encouraged in Zend_Db.

Using persistent connections can cause an excess of idle connections on the RDBMS server, which causes more problems than any performance gain you might achieve by reducing the overhead of making connections.

Database connections have state. That is, some objects in the RDBMS server exist in session scope. Examples are locks, user variables, temporary tables, and information about the most recently executed query, such as rows affected, and last generated id value. If you use persistent connections, your application could access invalid or privileged data that were created in a previous PHP request.

Running Other Database Statements

There might be cases in which you need to access the connection object directly, as provided by the PHP database extension. Some of these extensions may offer features that are not surfaced by methods of Zend_Db_Adapter_Abstract.

For example, all SQL statements run by Zend_Db are prepared, then executed. However, some database features are incompatible with prepared statements. DDL statements like CREATE and ALTER cannot be prepared in MySQL. Also, SQL statements don't benefit from the » MySQL Query Cache, prior to MySQL 5.1.17.

Most PHP database extensions provide a method to execute SQL statements without preparing them. For example, in PDO, this method is exec(). You can access the connection object in the PHP extension directly using getConnection().

Example #31 Running a non-prepared statement in a PDO adapter

getConnection()->exec('DROP TABLE bugs');

Similarly, you can access other methods or properties that are specific to PHP database extensions. Be aware, though, that by doing this you might constrain your application to the interface provided by the extension for a specific brand of RDBMS.

In future versions of Zend_Db, there will be opportunities to add method entry points for functionality that is common to the supported PHP database extensions. This will not affect backward compatibility.

Notes on Specific Adapters

This section lists differences between the Adapter classes of which you should be aware.

IBM DB2

  • Specify this Adapter to the factory() method with the name 'Db2'.

  • This Adapter uses the PHP extension ibm_db2.

  • IBM DB2 supports both sequences and auto-incrementing keys. Therefore the arguments to lastInsertId() are optional. If you give no arguments, the Adapter returns the last value generated for an auto-increment key. If you give arguments, the Adapter returns the last value generated by the sequence named according to the convention 'table_column_seq'.

MySQLi

  • Specify this Adapter to the factory() method with the name 'Mysqli'.

  • This Adapter utilizes the PHP extension mysqli.

  • MySQL does not support sequences, so lastInsertId() ignores its arguments and always returns the last value generated for an auto-increment key. The lastSequenceId() method returns null.

Oracle

  • Specify this Adapter to the factory() method with the name 'Oracle'.

  • This Adapter uses the PHP extension oci8.

  • Oracle does not support auto-incrementing keys, so you should specify the name of a sequence to lastInsertId() or lastSequenceId().

  • The Oracle extension does not support positional parameters. You must use named parameters.

  • Currently the Zend_Db::CASE_FOLDING option is not supported by the Oracle adapter. To use this option with Oracle, you must use the PDO OCI adapter.

PDO for IBM DB2 and Informix Dynamic Server (IDS)

  • Specify this Adapter to the factory() method with the name 'Pdo_Ibm'.

  • This Adapter uses the PHP extensions pdo and pdo_ibm.

  • You must use at least PDO_IBM extension version 1.2.2. If you have an earlier version of this extension, you must upgrade the PDO_IBM extension from PECL.

PDO Microsoft SQL Server

  • Specify this Adapter to the factory() method with the name 'Pdo_Mssql'.

  • This Adapter uses the PHP extensions pdo and pdo_mssql.

  • Microsoft SQL Server does not support sequences, so lastInsertId() ignores its arguments and always returns the last value generated for an auto-increment key. The lastSequenceId() method returns null.

  • Zend_Db_Adapter_Pdo_Mssql sets QUOTED_IDENTIFIER ON immediately after connecting to a SQL Server database. This makes the driver use the standard SQL identifier delimiter symbol (") instead of the proprietary square-brackets syntax SQL Server uses for delimiting identifiers.

  • You can specify pdoType as a key in the options array. The value can be "mssql" (the default), "dblib", "freetds", or "sybase". This option affects the DSN prefix the adapter uses when constructing the DSN string. Both "freetds" and "sybase" imply a prefix of "sybase:", which is used for the » FreeTDS set of libraries. See also »  http://www.php.net/manual/en/ref.pdo-dblib.connection.php for more information on the DSN prefixes used in this driver.

PDO MySQL

  • Specify this Adapter to the factory() method with the name 'Pdo_Mysql'.

  • This Adapter uses the PHP extensions pdo and pdo_mysql.

  • MySQL does not support sequences, so lastInsertId() ignores its arguments and always returns the last value generated for an auto-increment key. The lastSequenceId() method returns null.

PDO Oracle

  • Specify this Adapter to the factory() method with the name 'Pdo_Oci'.

  • This Adapter uses the PHP extensions pdo and pdo_oci.

  • Oracle does not support auto-incrementing keys, so you should specify the name of a sequence to lastInsertId() or lastSequenceId().

PDO PostgreSQL

  • Specify this Adapter to the factory() method with the name 'Pdo_Pgsql'.

  • This Adapter uses the PHP extensions pdo and pdo_pgsql.

  • PostgreSQL supports both sequences and auto-incrementing keys. Therefore the arguments to lastInsertId() are optional. If you give no arguments, the Adapter returns the last value generated for an auto-increment key. If you give arguments, the Adapter returns the last value generated by the sequence named according to the convention 'table_column_seq'.

PDO SQLite

  • Specify this Adapter to the factory() method with the name 'Pdo_Sqlite'.

  • This Adapter uses the PHP extensions pdo and pdo_sqlite.

  • SQLite does not support sequences, so lastInsertId() ignores its arguments and always returns the last value generated for an auto-increment key. The lastSequenceId() method returns null.

  • To connect to an SQLite2 database, specify 'sqlite2'=>true in the array of parameters when creating an instance of the Pdo_Sqlite Adapter.

  • To connect to an in-memory SQLite database, specify 'dbname'=>':memory:' in the array of parameters when creating an instance of the Pdo_Sqlite Adapter.

  • Older versions of the SQLite driver for PHP do not seem to support the PRAGMA commands necessary to ensure that short column names are used in result sets. If you have problems that your result sets are returned with keys of the form "tablename.columnname" when you do a join query, then you should upgrade to the current version of PHP.

Firebird/Interbase

  • This Adapter uses the PHP extension php_interbase.

  • Firebird/interbase does not support auto-incrementing keys, so you should specify the name of a sequence to lastInsertId() or lastSequenceId().

  • Currently the Zend_Db::CASE_FOLDING option is not supported by the Firebird/interbase adapter. Unquoted identifiers are automatically returned in upper case.

Previous Next
Introduction to Zend Framework
Overview
Installation
Zend_Acl
Introduction
Refining Access Controls
Advanced Use
Zend_Auth
Introduction
Database Table Authentication
Digest Authentication
HTTP Authentication Adapter
LDAP Authentication
Open ID Authentication
Zend_Cache
Introduction
The theory of caching
Zend_Cache frontends
Zend_Cache backends
Zend_Captcha
Introduction
Captcha Operation
Captcha Adapters
Zend_Config
Introduction
Theory of Operation
Zend_Config_Ini
Zend_Config_Xml
Zend_Console_Getopt
Introduction to Getopt
Declaring Getopt Rules
Fetching Options and Arguments
Configuring Zend_Console_Getopt
Zend_Controller
Zend_Controller Quick Start
Zend_Controller Basics
The Front Controller
The Request Object
The Standard Router: Zend_Controller_Router_Rewrite
The Dispatcher
Action Controllers
Action Helpers
The Response Object
Plugins
Using a Conventional Modular Directory Structure
MVC Exceptions
Migrating from Previous Versions
Zend_Currency
Introduction to Zend_Currency
How to work with currencies
Migrating from Previous Versions
Zend_Date
Introduction
Theory of Operation
Basic Methods
Zend_Date API Overview
Creation of dates
Constants for General Date Functions
Working examples
Zend_Db
Zend_Db_Adapter
Zend_Db_Statement
Zend_Db_Profiler
Zend_Db_Select
Zend_Db_Table
Zend_Db_Table_Row
Zend_Db_Table_Rowset
Zend_Db_Table Relationships
Zend_Debug
Dumping Variables
Zend_Dojo
Introduction
Zend_Dojo_Data: dojo.data Envelopes
Dojo View Helpers
Dojo Form Elements and Decorators
Zend_Dom
Introduction
Zend_Dom_Query
Zend_Exception
Using Exceptions
Zend_Feed
Introduction
Importing Feeds
Retrieving Feeds from Web Pages
Consuming an RSS Feed
Consuming an Atom Feed
Consuming a Single Atom Entry
Modifying Feed and Entry structures
Custom Feed and Entry Classes
Zend_File
Zend_File_Transfer
Validators for Zend_File_Transfer
Zend_Filter
Introduction
Standard Filter Classes
Filter Chains
Writing Filters
Zend_Filter_Input
Zend_Filter_Inflector
Zend_Form
Zend_Form
Zend_Form Quick Start
Creating Form Elements Using Zend_Form_Element
Creating Forms Using Zend_Form
Creating Custom Form Markup Using Zend_Form_Decorator
Standard Form Elements Shipped With Zend Framework
Standard Form Decorators Shipped With Zend Framework
Internationalization of Zend_Form
Advanced Zend_Form Usage
Zend_Gdata
Introduction to Gdata
Authenticating with AuthSub
Authenticating with ClientLogin
Using Google Calendar
Using Google Documents List Data API
Using Google Spreadsheets
Using Google Apps Provisioning
Using Google Base
Using the YouTube Data API
Using Picasa Web Albums
Catching Gdata Exceptions
Zend_Http
Zend_Http_Client - Introduction
Zend_Http_Client - Advanced Usage
Zend_Http_Client - Connection Adapters
Zend_Http_Cookie and Zend_Http_CookieJar
Zend_Http_Response
Zend_InfoCard
Introduction
Zend_Json
Introduction
Basic Usage
JSON Objects
XML to JSON conversion
Zend_Json_Server - JSON-RPC server
Zend_Layout
Introduction
Zend_Layout Quick Start
Zend_Layout Configuration Options
Zend_Layout Advanced Usage
Zend_Ldap
Introduction
Zend_Loader
Loading Files and Classes Dynamically
Loading Plugins
Zend_Locale
Introduction
Using Zend_Locale
Normalization and Localization
Working with Dates and Times
Supported Languages for Locales
Supported Regions for Locales
Zend_Log
Overview
Writers
Formatters
Filters
Zend_Mail
Introduction
Sending via SMTP
Sending Multiple Mails per SMTP Connection
Using Different Transports
HTML E-Mail
Attachments
Adding Recipients
Controlling the MIME Boundary
Additional Headers
Character Sets
Encoding
SMTP Authentication
Securing SMTP Transport
Reading Mail Messages
Zend_Measure
Introduction
Creation of Measurements
Outputting measurements
Manipulating Measurements
Types of measurements
Zend_Memory
Overview
Memory Manager
Memory Objects
Zend_Mime
Zend_Mime
Zend_Mime_Message
Zend_Mime_Part
Zend_OpenId
Introduction
Zend_OpenId_Consumer Basics
Zend_OpenId_Provider
Zend_Paginator
Introduction
Usage
Configuration
Advanced usage
Zend_Pdf
Introduction.
Creating and loading PDF documents.
Save changes to the PDF document.
Document pages.
Drawing.
Document Info and Metadata.
Zend_Pdf module usage example.
Zend_Registry
Using the Registry
Zend_Rest
Introduction
Zend_Rest_Client
Zend_Rest_Server
Zend_Search_Lucene
Overview
Building Indexes
Searching an Index
Query Language
Query Construction API
Character Set
Extensibility
Interoperating with Java Lucene
Advanced
Best Practices
Zend_Server
Introduction
Zend_Server_Reflection
Zend_Service
Introduction
Zend_Service_Akismet
Zend_Service_Amazon
Zend_Service_Audioscrobbler
Zend_Service_Delicious
Zend_Service_Flickr
Zend_Service_Nirvanix
Zend_Service_ReCaptcha
Zend_Service_Simpy
Introduction
Zend_Service_StrikeIron
Zend_Service_StrikeIron: Bundled Services
Zend_Service_StrikeIron: Advanced Uses
Zend_Service_Technorati
Zend_Service_Yahoo
Zend_Session
Introduction
Basic Usage
Advanced Usage
Global Session Management
Zend_Session_SaveHandler_DbTable
Zend_Soap
Zend_Soap_Server
Zend_Soap_Client
WSDL Accessor
AutoDiscovery. Introduction
Class autodiscovering.
Functions autodiscovering.
Autodiscovering. Datatypes.
Zend_Test
Introduction
Zend_Test_PHPUnit
Zend_Text
Zend_Text_Figlet
Zend_TimeSync
Introduction
Working with Zend_TimeSync
Zend_Translate
Introduction
Adapters for Zend_Translate
Using Translation Adapters
Zend_Uri
Zend_Uri
Zend_Validate
Introduction
Standard Validation Classes
Validator Chains
Writing Validators
Zend_Version
Reading the Zend Framework Version
Zend_View
Introduction
Controller Scripts
View Scripts
View Helpers
Zend_View_Abstract
Zend_Wildfire
Zend_Wildfire
Zend_XmlRpc
Introduction
Zend_XmlRpc_Client
Zend_XmlRpc_Server
Zend Framework Requirements
PHP Version
PHP Extensions
Zend Framework Components
Zend Framework Dependencies
Zend Framework Coding Standard for PHP
Overview
PHP File Formatting
Naming Conventions
Coding Style
Copyright Information