Zend_Db_Table is an implementation of the Table Data Gateway design pattern provided by the Zend Framework. It provides a set of methods and attributes useful for interacting with a database table, but can also be extended to provide custom functionality for your application.

In the simplest case you will only want to use the functionality already provided by Zend_Db_Table:

1
2
3
4
5
6
7
8
9
// PDO_MYSQL can be replaced for any of the databases supported by Zend Framework
$db = Zend_Db::factory('PDO_MYSQL', $options);

// Set the db adapter that will be used by default by instances of Zend_Db_Table
Zend_Db_Table::setDefaultAdapter($db);

// Create an instance that will work against a table named 'users'
// This is case sensitive and must match exactly the name of the table
$usersTable = new Zend_Db_Table('users');

If you want to be able to add custom methods you would extend the class like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
class Users extends Zend_Db_Table_Abstract
{
    // This attribute specifies the name of the table this class will handle.
    // If this attribute is not present the name of the class will be used
    protected $_name = 'users';

    // This attribute should match the name of the primary key of the table.
    // If it is not provided, Zend_Db_Table will try to discover it
    protected $_primary = 'user';

    // The constructor of this class by defaults sets up the table. If you want
    // to execute some code when this class is instantiated the best way to do it
    // is using the init method that will be executed after all table metadata
    // is processed
    public function init()
    {
    }
}

Inserting rows

To insert a new record you can use the insert method:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$usersTable = new Users('users');

// The array keys are the names of the columns and the array values are the
// values that are going to be inserted.
// By default all values are taken literally, so if you want to use an expression
// Zend_Db_Expr needs to be used. These values are escaped automatically.

$data = array(
    'user'      => 'my_user',
    'name'      => 'Juanito',
    'password'  => new Zend_Db_Expr('SHA1("mypass")')
);

$usersTable->insert($data);

Updating rows

1
2
3
4
5
6
7
8
9
10
11
$usersTable = new Users('users');

$data = array(
    'user'      => 'changed_user',
    'name'      => 'Juanita'
);

// You should use quoteInto to avoid sql injection
$where = $usersTable->getAdapter()->quoteInto('user = ?', 'my_user');

$usersTable->update($data, $where);

Selecting rows

You can use the find() method if you want to find records based on the primary key:

1
2
3
4
5
6
7
$usersTable = new Users('users');

// Find a single record
$row = $usersTable->find('my_user');

// Find multiple records
$row = $usersTable->find(array ('my_user', 'your_user'));

The find() method always returns an object of type Zend_Db_Table_Rowset_Abstract.

To fetch a set of rows or a single row based on a column different than the primary key you can use fetchAll() and fetchRow().

1
2
3
4
5
6
7
$usersTable = new Users('users');

// select() method returns a Zend_Db_Table_Select object that you can modify
// to add select criteria at your convenience
$select = $usersTable->select()->where('name = ?', 'Juanito');

$rows = $table->fetchAll($select);

fetchAll() method also returns a Zend_Db_Table_Rowset_Abstract object.

The Zend_Db_Table_Select object accepts not only a where clause, but there are other filters you can apply:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$usersTable = new Users('users');

// select() method returns a Zend_Db_Table_Select object that you can modify
// to add select criteria at your convenience
$select = $usersTable->select()
        // Specify which columns to return. You can use aggregate functions.
        ->from($usersTable, array('user', 'name', 'COUNT(reported_by) as count'))
        // Column that will be used to order rows
        ->order('name')
        // The first value is the number of rows to return and the second the offset
        ->limit(50, 0)
        // You can join with another table
        ->join('books', 'books.user = users.user')
        ->where('books.name = ?', 'The best book')
        ->where('status = ?', 'active');

$rows = $table->fetchAll($select);

There is a lot more stuff that can be done with Zend_Db_Table but I think this is the most useful.

[ php  zend_framework  ]
Zend Framework Authentication php zend_framework
Fatal error: Uncaught exception 'Zend_Controller_Dispatcher_Exception' with message 'Invalid controller specified (error)' php zend_framework
Zend Framework resource autoloading with appnamespace php zend_framework
Using Table Data Gateway and Row Data Gateway design patterns in Zend Framework php zend_framework
Using AJAX with Zend Framework php zend_framework