After reading the first part of our series, you’ve learned how to create a simple website based on Zend Framework. But it’s necessary to keep the information for full web-application. In this part we will learn how to work with databases using ZF.

Zend_Db

Zend_Db_Adapter gives us an opportunity to work with different database management systems (DBMS).

Using PDO drivers:

• BM DB2 and Informix Dynamic Server (IDS), using pdo_ibm extension
• MySQL, using the pdo_mysql PHP extension
• Microsoft SQL Server, using pdo_mssql
• Oracle, using pdo_oci
• PostgreSQL, using pdo_pgsql
• SQLite, using the pdo_sqlite PHP extension

Using PHP extensions:

• MySQL, using the mysqli extension
• Oracle, using the oci8 PHP extension
• IBM DB2, using ibm_db2
• Firebird / Interbase, using php_interbase

We’ll use the PDO driver to work with MySQL in this article.

Let’s Get Started

We need the way to store configuration file to the database connection. For it we’ll create a application.ini file in our ./application/configs directory. Add to the file following lines:

db.adapter = "pdo_mysql"
db.params.host = "localhost"
db.params.username = "root"
db.params.password = ""
db.params.dbname = "your_db_name_here"

I think the content will not cause any difficulties for you. We will use Zend_Confg_Ini to parse the config of this file. You have to initialize connection to the database before dispatching process. Add the following code to your index.php file.

// Load up our config file
$config = new Zend_Config_Ini('./application/configs/application.ini', 'db');

$db = Zend_Db::factory($config->db->adapter,
$config-db->config->toArray());

// Return data as object
$db->setFetchMode(Zend_Db::FETCH_OBJ);

// The default adapter for table classes
Zend_Db_Table::setDefaultAdapter($db);

// Write our adapter to registry
Zend_Registry::set('db', $db);

If you do not want to receive the result as an object, there are descriptions of other styles to obtain the result for you:

• Zend_Db:: FETCH_ASSOC: returns the data in an associative array.
  The keys of the array are the column names. It’s the mode of extraction is used by default in Zend_Db_Adapter classes.
• Zend_Db:: FETCH_NUM: returns the data in an array. The array indexed by integers in accordance with the position of the field in the select query list.
• Zend_Db:: FETCH_BOTH: returns the data in an array. The keys of the array are lines and integer values. The number of elements will produce twice more in the array than if FETCH_ASSOC or FETCH_NUM is used.
• Zend_Db:: FETCH_COLUMN: returns the data in an array of values. The value is the value returned from one column of the result set in each array. By default, it’s the first column, indexed by zero.
• Zend_Db:: FETCH_OBJ: returns the data in an array of objects. By default, the built-in PHP class stdClass is used. Columns of the result of sample are available as public properties of this object.

You are probably interested why have we recorded the adapter to the registry? We did so to be able to get access to our created adapter in any controller or model. To extract our adapter from the registry back we can use Zend_Registry::get(); method

Zend_Registry::get('db');

Well, let’s try to execute some requests!

$sql = 'SELECT * FROM users';
$result = $db->fetchAll($sql);

The $result variable will contain an array of objects. To extract a single column from the sample results you should do

$sql = 'SELECT name, id FROM users';
$result = $db->fetchCol($sql);

Now $result contains only the name. In order to extract a single row from the result of sampling execute the following code

$sql = 'SELECT * FROM users WHERE id = 2';
$result = $db->fetchRow($sql);

And now $result is the single object ($result->name). Extracting a single value

$sql = 'SELECT COUNT(*) FROM users';
$result = $db->fetchOne($sql);

$result contains the number of users. That’s all with SELECT queries. But we still have to add and modify our data in the database. Lets’s look at it.

Adding Records

There is a special method for inserting data to the database: Zend_Db’s insert method.

$data = array('name' => 'Nick',
'login' => 'nick',
'email' => 'myemailgmail [dot] com');

$db->insert('user', $data);
$lastId = $db->lastInsertId();

We need to pass two parameters to insert method: table name, and an associative array with the data. lastInsertId method will return the value of auto-increment primary field in the table.

Updating Records

To able to update our db data we should call the update method and pass it three parameters. The first parameter is the name of the table, the second – an associative array with the data, the third parameter – a condition (optional); if you omit the third parameter, all records will be updated. The array of conditions can be passed as a WHERE condition.

$data = array('name' => 'Nick',
'login' => 'nick',
'email' => 'myemailgmail [dot] com');

$db->update('user', $data, 'id = 1');

Deleting Records

Now you should know what each parameter is used for. The first argument is the name of the table, the second is the condition. Simply, right? Also there may be several conditions which is passed as an array. If you omit the second argument, all records of the table will be deleted.

$db->delete('user', 'id = 1');

Example Bits of Code

Example 1:

// Extract Zend_Db_Select
$select = $db->select();

// 1st - table name, 2nd - array of selecting columns
$select->from('news', array('YEAR(date) AS yearNews',
'MONTH(date) AS monthNews',
'DAy(date) AS dayNews'));

Example 2:

// Extract Zend_Db_Select
$select = $db->select();

// Descending sorting
$select->from('news')->order('date DESC');
$result = $db->fetchAll($select);

Example 3:

// Extract Zend_Db_Select
$select = $db->select();

// Descending sorting by date and ascending by title field
$select->from('news')
->order(array('date DESC', 'title'));

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

Example 4:

// Extract Zend_Db_Select
$select = $db->select();

// Descending sorting by date and ascending by title field
$select->from(array('n' => 'news'), // table name and its alias
array('newsTitle' => 'title', // The second parameter
'newsDescription' => 'description', // column alias => column name
'date' => 'date'))
->join(array('a' => 'author'), // The first param for join method | alias => table name
array('n.author = a.id'), // The 2nd param - array of condition for join operator
array('name' => 'authorName')) // Associative array of columns
->order('date DESC'); // Sorting descending by date column

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

Zend_Db_Select

Zend_Db_Select – is the assistant of making SELECT queries. Using this class methods we can build our complicated queries expressions part-by-part.

Queries Building

For example, we have to choose the record of news from a table with this query

SELECT * FROM news WHERE id = 12;

Using Zend_Db_Select it would look like so

// Extract Zend_Db_Select
$select = $db->select();

$select->from('news')->where('id = 12');
$result = $db->fetchAll($select);

Now, let’s choose all the news records, but breaking the date’s field into three columns – year, month, day

SELECT *, YEAR(date) AS yearNews,
MONTH(date) AS monthNews,
DAY(date) AS dayNews
FROM news;

SQL has several types of joining operation. Below are the list of methods for supporting different joining types in Zend_Db_Select.

• INNER JOIN, methods join (table, join, [columns]) and joinInner (table, join, [columns]).
• LEFT JOIN, the method joinLeft (table, condition, [columns]).
• RIGHT JOIN, the method joinRight (table, condition, [columns]).
• FULL JOIN, the method joinFull (table, condition, [columns]).
• CROSS JOIN, the method joinCross (table, [columns]).
• NATURAL JOIN, the method joinNatural (table, [columns]).

There are also some special methods of Zend_Db_Select class.

• orWhere() – the same as where(), but with logical OR.
• group() – one column’s name can be sent to this method by one line or the lines’ array of columns’ names./li>
• limit() – passes the desired number of rows to choose by the first parameter, by the second – number of the optional rows which can be omitted.

The Result

Another one Zend Framework lesson is completed. Digest the received knowledge, and I hope we will try to continue our experiment in the next part. Bye!

P.S. I look forward to receiving your feedback and questions in the comments below.