If you enjoyed this article, get email updates (it's free).
Join over 77,235 Subscribers Today.
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 database management systems (DBMS).
Using PDO drivers:
Using PHP extensions:
We’ll use the PDO driver to work with MySQL in this article.
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:
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');
// 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.
There are also some special methods of Zend_Db_Select class.
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.
Join over 77,235 Subscribers Today.
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.