Migrating from the Codeigniter database abstraction to the Zend database abstraction.

This post is meant to give a quick review and function reference to developers migrating from the codeigniter database abstraction to the Zend database abstraction. This is just a quick review for frequently used sql functions. If you want a detailed understanding of the Zend DB abstraction class you should not be here but here: http://framework.zend.com/manual/en/zend.db.html Zend_Db and its related classes provide a simple SQL database interface. Zend_Db_Adapter is the basic class you use to connect your PHP application to an RDBMS. I will use the pdo_mysql php extension so you will have to enable the pdo_mysql php extension in order to use the Zend_db_Adapter_Pdo_Mysql class from the Zend_db. Other RDBMS brands are however also supported by this framework. Most of the functions listed here too are just the equivalence of just the CI DB active record class

Connecting to a database using the Zend_db adapter

To create an instance of an adapter, use

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

you can also use the Zend_Db::factory() method in order to create an instance of an adapter. This method will load the adapter class on demand using the Zend Loader class (use this only if you are using the framework).

Managing Connections

<?php
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
}
?>

Getting Rows

<?php
$sql = 'SELECT * FROM bugs WHERE bug_id = ?';
$result = $db->fetchAll($sql, 2);
// second argument is an array (or val)substituting placeholders in the sql statement
?>

You can change the way to fetch data by using the setFetchMode() method. Example

<?php
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$result = $db->fetchAll('SELECT * FROM bugs WHERE bug_id = ?', 2);
// $result is an array of objects | Zend_Db::FETCH_ASSOC, Zend_Db::FETCH_NUM, Zend_Db::FETCH_BOTH, Zend_Db::FETCH_COLUMN, Zend_Db::FETCH_OBJ
echo $result[0]->bug_description;
?>

$db->fetchCol() returns only the first column in the query and is returned as values. fetchRow() can be used to get the first row of a result set

<?php
$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;
?>

fetchOne() returns an intersection of first row and first column

<?php
$result = $db->fetchOne('SELECT bug_status FROM bugs WHERE bug_id = 2');
// this is a single string value
echo $result;
?>

inserting data

<?php
// $data is an associative array of key=>value pairs with keys being the column names and value being the data to be inserted under each column associative
$db->insert('bugs', $data);
$id = $db->lastInsertId();

?>

updating data

<?php
$db->update('bugs', $data, 'user_id = 5'[$where]);

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

?>

deleting data

<?php
$n = $db->delete('bugs', 'bug_id = 3');
?>

The quote() method excapes string and returns a deliminited sql string
<?php $db->quote("O'Reilly"); //returns 'O\'Reilly'?>

more example
<?php
$value = '1234';
$sql = 'SELECT * FROM atable WHERE intColumn = '
     . $db->quote($value, 'INTEGER');
  
$sql = $db->quoteInto("SELECT * FROM bugs WHERE bug_id = ?", '1234', 'INTEGER');
?>

Transactions

Straight to an example:

<?php
// Start a transaction explicitly.
$db->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();
}
?>

Methods in the Zend_Db_Statement class

use $db->query to get all results at once or you can create a statement using the statement class and execute it

<?php
$sql = 'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?';
$stmt = new Zend_Db_Statement_Mysqli($db, $sql);
$stmt->execute(array('goofy', 'FIXED'));
?>

user the fetch method to get results in

<?php
while ($row = $stmt->fetch()) {
    echo $row['bug_description'];
}
//OR $result->fetchObject();
?>

SELECT statement

The select statement object can be used to build good select queries. To create a select statemnt you can do one of the following

<?php
$db = Zend_db::Factory(...options...);
$select = new Zend_Db_Select($db);
  OR
$db->select();
?>

Since it always returns an instance of itself, you can bind its methods in a fluent interface

<?php
$select = $db->select()
    ->from( ...specify table and columns... )
    ->where( ...specify search criteria... )
    ->order( ...specify sorting criteria... );
?>

Example using 'from'
In the second argument of the from() method, you can specify the columns to select from the respective table. If you specify no columns, the default is "*", the SQL wildcard for "all columns". You can list the columns in a simple array of strings, or as an associative mapping of column alias to column name. If you only have one column to query, and you don't need to specify a column alias, you can list it as a plain string instead of an array.

<?php
// Build this query:
//   SELECT p."product_id", (p.cost * 1.08) AS cost_plus_tax
//   FROM "products" AS p
 
$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id',
                          'cost_plus_tax' => '(p.cost * 1.08)')
                   );
       
// Build this query with an alias for one column:
//   SELECT p."product_id" AS prodno, p."product_name"
//   FROM "products" AS p
 
$select = $db->select()
             ->from(array('p' => 'products'),
                    array('prodno' => 'product_id', 'product_name'));
?>

Example using 'join'

<?php
// Build this query:
//   SELECT p."product_id", p."product_name", l.*
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id
 
$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id', 'product_name'))
             ->join(array('l' => 'line_items'),
                    'p.product_id = l.product_id');
     
?>

The several sql join types can be specified as follows
INNER JOIN with the join(table, join, [columns]) or joinInner(table, join, [columns]) //most common join type LEFT JOIN with the joinLeft(table, condition, [columns])
RIGHT JOIN with the joinRight(table, condition, [columns])
FULL JOIN with the joinFull(table, condition, [columns])
CROSS JOIN with the joinCross(table, [columns])
NATURAL JOIN with the joinNatural(table, [columns])
Examples using 'where'

<?php
// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (price > 100.00)
//     AND (price < 500.00)
 
$minimumPrice = 100;
$maximumPrice = 500;
 
$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('price > ?', $minimumPrice)
             ->where('price < ?', $maximumPrice);
?>

There is also orwhere which is similar to or_where of the codeigniter active record class.

<?php
// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (product_id IN (1, 2, 3))
 
$productIds = array(1, 2, 3);
 
$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('product_id IN (?)', $productIds);
    ->orwhere('.....');
?>

you can also add

 
<?php
$select->group() // for group_by() //GROUP BY CLAUSE
$select->having() // for having() //HAVING CLAUSE
$select->order(array('id DESC', 'name ASC') // for order_by('field', ORDER) //ORDER BY CLAUSE
$selcet->limit(20, 10);   //The first argument to this method is the desired count of rows. The second argument is the number of rows to skip                     
?>

Adding the DISTINCT keywords to queries

Example:

<?php
// Build this query:
//   SELECT DISTINCT p."product_name"
//   FROM "products" AS p
 
$select = $db->select()
             ->distinct()
             ->from(array('p' => 'products'), 'product_name');

?>

Building a union query

<?php $sql1 = $db->select(); $sql2 = "SELECT ..."; $select = $db->select() ->union(array($sql1, $sql2)) ->order("id"); ?> You can convert your select query to a string by using <?php $sql = $select->__toString(); echo $sql; ?> Executing Select Queries

<?php
Example:
$select = $db->select()
             ->from('products');
 
$stmt = $db->query($select);
$result = $stmt->fetchAll();

//OR  

$stmt = $db->query($select);
$result = $stmt->fetchAll();
?>

This is just a quick review for frequently used sql functions. If you want a detailed understanding of the Zend DB abstraction class you should visit http://framework.zend.com/manual/en/zend.db.html