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_Adapter gives us an opportunity to work with different.
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
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.
There is a special method for inserting data to the database: Zend_Db’s insert method.
$data = array('name' => 'Nick', 'login' => 'nick', 'email' => '[email protected]'); $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.
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' => '[email protected]'); $db->update('user', $data, 'id = 1');
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
// 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'));
// Extract Zend_Db_Select $select = $db->select(); // Descending sorting $select->from('news')->order('date DESC'); $result = $db->fetchAll($select);
// 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);
// 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 – is the assistant of making SELECT queries. Using this class methods we can build our complicated queries expressions part-by-part.
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.
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.
Hi there! My name is Nick Plekhanov. I'm a passionate web developer and user interface designer from Russia, experienced in PHP, and MySQL along with the beautiful XHTML and CSS under jQuery stuff. I've been doing web development for 3 years. Here, I'll be your PHP coder, sometimes designer, and SEO expert. My own web development blog is coming really soon. If you’d like to connect with me, follow me on Twitter.