21st
Learning Zend_Db_Table
- Posted in Learning, Web & Technology
- Comments 0
Here are some quick notes taken from the Zend Framework documentation about this class. I’ll aggregate the parts I feel are most useful… :
Introduction to Table Class
The Zend_Db_Table class is an object-oriented interface to database tables. It provides methods for many common operations on tables. The base class is extensible, so you can add custom logic.
Defining a Table Class
For each table in your database that you want to access, define a class that extends Zend_Db_Table_Abstract.
My feeling on this, is that it’s a little bit “overkill”, at least for a website with a DB that handles hundreds of tables. I feel some tables will always be used in some kind of logical grouping into some class. In those cases you will not want to create systematically all those class… Unless you have a lot more time available than I do 
Defining the Table Name and Schema
Declare the database table for which this class is defined, using the protected variable $_name. This is a string, and must contain the name of the table spelled as it appears in the database.
Example : Declaring a table class with explicit table name
class Bugs extends Zend_Db_Table_Abstract
{
protected $_name = 'bugs';
}
If you don’t specify the table name, it defaults to the name of the class. If you rely on this default, the class name must match the spelling of the table name as it appears in the database. My suggestion : don’t do that!
Defining the Table Primary Key
Every table must have a primary key. You can declare the column for the primary key using the protected variable $_primary. This is either a string that names the single column for the primary key, or else it is an array of column names if your primary key is a compound key.
Example of specifying the primary key
class Bugs extends Zend_Db_Table_Abstract
{
protected $_name = 'bugs';
protected $_primary = 'bug_id';
}
If you don’t specify the primary key, Zend_Db_Table_Abstract tries to discover the primary key based on
the information provided by the describeTable() method.
Setting a Default Database Adapter
The second way to provide a database adapter to a Table class is by declaring an object of type Zend_Db_Adapter_Abstract to be a default database adapter for all subsequent instances of Tables in your application. You can do this with the static method Zend_Db_Table_Abstract::setDefaultAdapter(). The argument is an object of type Zend_Db_Adapter_Abstract.
Example of constructing a Table using a the Default Adapter
$db = Zend_Db::factory('PDO_MYSQL', $options);
Zend_Db_Table_Abstract::setDefaultAdapter($db);
// Later...
$table = new Bugs();
It can be convenient to create the database adapter object in a central place of your application, such as the bootstrap, and then store it as the default adapter. This gives you a means to ensure that the adapter instance is the same throughout your application. However, setting a default adapter is limited to a single adapter instance.
Storing a Database Adapter in the Registry
The third way to provide a database adapter to a Table class is by passing a string in the options array, also identified by the ‘db’ key. The string is used as a key to the static Zend_Registry instance, where the entry at that key is an object of type Zend_Db_Adapter_Abstract.
Example of constructing a Table using a Registry key
$db = Zend_Db::factory('PDO_MYSQL', $options);
Zend_Registry::set('my_db', $db);
// Later...
$table = new Bugs(array('db' => 'my_db'));
Like setting the default adapter, this gives you the means to ensure that the same adapter instance is used throughout your application. Using the registry is more flexible, because you can store more than one adapter instance. A given adapter instance is specific to a certain RDBMS brand and database instance. If your application needs access to multiple databases or even multiple database brands, then you need to use multiple adapters.
Inserting Rows to a Table
You can use the Table object to insert rows into the database table on which the Table object is based. Use the insert() method of your Table object. The argument is an associative array, mapping column names to values.
Example of inserting to a Table
$table = new Bugs();
$data = array(
'created_on' => '2007-03-22',
'bug_description' => 'Something wrong',
'bug_status' => 'NEW'
);
$table->insert($data);
Updating Rows in a Table
You can update rows in a database table using the update method of a Table class. This method takes two arguments: an associative array of columns to change and new values to assign to these columns; and an SQL expression that is used in a WHERE clause, as criteria for the rows to change in the UPDATE operation.
Example of updating rows in a Table
$table = new Bugs();
$data = array(
'updated_on' => '2007-03-23',
'bug_status' => 'FIXED'
);
$where = $table->getAdapter()->quoteInto('bug_id = ?', 1234);
$table->update($data, $where);
Since the table update() method proxies to the database adapter update() method, the second argument can be an array of SQL expressions. The expressions are combined as Boolean terms using an AND operator.
Note
The values and identifiers in the SQL expression are not quoted for you. If you have values or identifiers that require quoting, you are responsible for doing this. Use the quote(), quoteInto(), and quoteIdentifier() methods of the database adapter.
Deleting Rows from a Table
You can delete rows from a database table using the delete() method. This method takes one argument, which is an SQL expression that is used in a WHERE clause, as criteria for the rows to delete.
Example of deleting rows from a Table
$table = new Bugs();
$where = $table->getAdapter()->quoteInto('bug_id = ?', 1235);
$table->delete($where);
The second argument can be an array of SQL expressions. The expressions are combined as Boolean terms
using an AND operator. Since the table delete() method proxies to the database adapter delete() method, the second argument can be an array of SQL expressions. The expressions are combined as Boolean terms using an AND operator.
Finding Rows by Primary Key
You can query the database table for rows matching specific values in the primary key, using the find() method. The first argument of this method is either a single value or an array of values to match against the primary key of the table.
Example of finding rows by primary key values
$table = new Bugs(); // Find a single row // Returns a Rowset $rows = $table->find(1234); // Find multiple rows // Also returns a Rowset $rows = $table->find(array(1234, 5678));
Next : the Select API