How to use Zend Adapter to retrieve information from database

Question

I'm trying to figure out the essentials of Zend Adapter. The tutorials and explanations are confusing and needs more clarification. Can someone give me simple examples to fully understand how to work with SQL queries and how to get the desired SQL results?

especially, I'm interested in learning how to get

-- column names

-- table names

-- fetchAll entries

and etc.

Thank you,

Answer 1

Like a lot of other people you seem to be having difficulty with the quickstart, try the tutorial from Rob Allen it helped me get started.

You have multiple choices on how to connect to your table, the confusion with Zend_Db often begins here.

The easiest way when using one DB, in your application.ini file add these lines at a minmum:

resources.db.adapter = "pdo_Mysql"
resources.db.params.username = "user_name"
resources.db.params.password = "password"
resources.db.params.dbname = "db_name"

Alternatively you can connect to a database almost anywhere in your code using the Zend_Db_Adapter:

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

//using factory
$db = Zend_Db::factory('Pdo_Mysql', array(
    'host'     => '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test'
));

List of supported Databases

Using this in your application can be as simple as:

//fetchAll using Zend_Db_Adapter and plain SQL
$sql = 'SELECT * FROM bugs WHERE bug_id = ?';

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

you can list the tables in a database:

$tables = $db->listTables();

or you can get a full table description (including column names), I included the comment block from the function in Zend_Db_Adapter_Abstract:

 /**
     * Returns the column descriptions for a table.
     *
     * The return value is an associative array keyed by the column name,
     * as returned by the RDBMS.
     *
     * The value of each array element is an associative array
     * with the following keys:
     *
     * SCHEMA_NAME => string; name of database or schema
     * TABLE_NAME  => string;
     * COLUMN_NAME => string; column name
     * COLUMN_POSITION => number; ordinal position of column in table
     * DATA_TYPE   => string; SQL datatype name of column
     * DEFAULT     => string; default expression of column, null if none
     * NULLABLE    => boolean; true if column can have nulls
     * LENGTH      => number; length of CHAR/VARCHAR
     * SCALE       => number; scale of NUMERIC/DECIMAL
     * PRECISION   => number; precision of NUMERIC/DECIMAL
     * UNSIGNED    => boolean; unsigned property of an integer type
     * PRIMARY     => boolean; true if column is part of the primary key
     * PRIMARY_POSITION => integer; position of column in primary key
     *
     * @param string $tableName
     * @param string $schemaName OPTIONAL
     * @return array
     */
$describTable = $db->describeTable('myTable');

This info should get you started, however I find that a lot of the real power of Zend_Db resides in the Zend_Db_Table, Zend_Db_Table_Row and especially the Zend_Db_Select classes.

I urge you to take some time and figure them out.

As an example of what you might expect from Zend_Db_Table and Zend_Db_select (when not using more advanced mappers and domain objects, hopefully those will come later):

//When using DbTable models that extend Zend_Db_Table_Abstract the model already 
//knows the name of the table and has full access to the Db adapter, allowing your code to
//be very brief and descriptive.
class Application_Model_DbTable_Weekend extends Zend_Db_Table_Abstract
{
    //name of table, required if classname is not the same as the table name
    protected $_name = 'weekend';
    //primary key column of table, a good idea especially if primary key is not 'id'
    protected $_primary = 'weekendid';

    public function getWeekend($weekendId) {
        //create select object
        $select = $this->select();
        $select->where('weekendid = ?', $weekendId);//placeholder syntax

        $result = $this->fetchRow($select);       
        if (!$result) {
            throw new Exception('Could not find weekend ID ' . $weekendId);
        }
        return $result;//returns a single row object
    }
    public function fetchAllWeekend() {

        $select = $this->select();

        $result = $this->fetchAll($select);

        return $result; //returns array of row objects (rowset object)
    }
}

Rob Allen's Zf tutorial will explain how DbTable models are setup and how they Work.

hope this helps...