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.

$db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test'
));

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.

// We don't need the following statement because the
// Zend_Db_Adapter_Pdo_Mysql file will be loaded for us by the Zend_Db
// factory method.

// require_once 'Zend/Db/Adapter/Pdo/Mysql.php';

// Automatically load class Zend_Db_Adapter_Pdo_Mysql
// and create an instance of it.
$db = Zend_Db::factory('Pdo_Mysql', array(
    'host'     => '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.

// We don't need to load the adapter class file
// because it will be loaded for us by the Zend_Db factory method.

// Automatically load class MyProject_Db_Adapter_Pdo_Mysql and create
// an instance of it.
$db = Zend_Db::factory('Pdo_Mysql', array(
    'host'             => '127.0.0.1',
    'username'         => 'webuser',
    'password'         => 'xxxxxxxx',
    'dbname'           => 'test',
    'adapterNamespace' => 'MyProject_Db_Adapter'
));

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.

$config = new Zend_Config(
    array(
        'database' => array(
            'adapter' => 'Mysqli',
            'params'  => array(
                'host'     => '127.0.0.1',
                '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.

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

$options = array(
    Zend_Db::CASE_FOLDING => 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);

$options = array(
    Zend_Db::AUTO_QUOTE_IDENTIFIERS => false
);

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

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

$pdoParams = array(
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => 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);

$options = array(
    Zend_Db::ALLOW_SERIALIZATION => false
);

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

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

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.

Additionally, an adapter can get serialized to store it, for example, in a session variable. This can be very useful not only for the adapter itself, but for other objects that aggregate it, like a Zend_Db_Select object. By default, adapters are allowed to be serialized, if you don't want it, you should consider passing the Zend_Db::ALLOW_SERIALIZATION option with FALSE, see the example above. To respect lazy connections principle, the adapter won't reconnect itself after being unserialized. You must then call getConnection() yourself. You can make the adapter auto-reconnect by passing the Zend_Db::AUTO_RECONNECT_ON_UNSERIALIZE with TRUE as an adapter option.

try {
    $db = Zend_Db::factory('Pdo_Mysql', $parameters);
    $db->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
}

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.

$sql = 'SELECT * FROM bugs WHERE bug_id = ?';

$result = $db->fetchAll($sql, 2);

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:

$db->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;

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

$db->setFetchMode(Zend_Db::FETCH_OBJ);

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

// $result is an array of associative arrays, in spite of the fetch mode
echo $result[2]['bug_description']; // Description of Bug #2
echo $result[1]['bug_description']; // Description of Bug #1

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 this section.

$db->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];

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.

$db->setFetchMode(Zend_Db::FETCH_OBJ);

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

echo $result[2];

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

$db->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;

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.

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

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

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.

$data = array(
    'created_on'      => '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.

$data = array(
    'created_on'      => new Zend_Db_Expr('CURDATE()'),
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW'
);

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

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.

$db->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".

$db->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.

$db->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 MariaDB, 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.

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 this section 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 this section for methods to help you do this.

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

$data = array(
    'updated_on'      => '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.

If you provide an array of arrays as the third argument, the values will be automatically quoted into the keys. These will then be joined together as terms, separated by AND operators.

$data = array(
    'updated_on'      => '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')

$data = array(
    'updated_on'      => '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')

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 this section for methods to help you do this.

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

$n = $db->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.

If you provide an array of arrays as the second argument, the values will be automatically quoted into the keys. These will then be joined together as terms, separated by AND operators.

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 (').

$name = $db->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.

$value = '1234';
$sql = 'SELECT * FROM atable WHERE intColumn = '
     . $db->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.

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.

$sql = $db->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.

$sql = $db
    ->quoteInto("SELECT * FROM bugs WHERE bug_id = ?", '1234', 'INTEGER');

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

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.

// we might have a table name that is an SQL reserved word
$tableName = $db->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 this example.