// May 20th, 2009 // No Comments » // Zend Framework
Back when I first started using the Zend Framework one of the first questions that I wanted answered was “how do I extract data from my database, and how do I write back?”. Of course the first place I went to was the documentation. Along the way I found some tutorials and some videos, including the video below. While it all works, in all honesty, I was not able to get a lot of the samples provided by Zend to work for me, but for the most part the video was a great tool.
The video is at the bottom of the post. So I will recap the video first and tell you how I went about using each of the components and provide some of my own source code as examples.
First, setting up a connection to the database
From the 2 methods provided by the video, I used a configuration file to store my database information. I did this because, well, I was able to get it to work the first time I tried it, but it’s also very easy to maintain since it removed those lines of code from and places them neatly in a separate file.
Here is the way my config.ini file looks like, notice that I can declare a parameters for multiple connection strings. In my case I have 2 databases that need to talk to each other. Also note that the config.ini file is located in the applications folder if you follow the directory structure recomended by Zend.
[firstDbConnect]
db.adapter = PDO_MYSQL
db.params.host = localhost
db.params.username = mydbname
db.params.password = mypassword
db.params.dbname = mydbname
[secondDbConnect]
db.adapter = PDO_MYSQL
db.params.host = localhost
db.params.username = anotherusername
db.params.password = anotherpassword
db.params.dbname = seconddb
Once I had my .ini file in place, I needed to read the file, and for that, we use Zend_Config()
$config = new Zend_Config_Ini(’../application/config.ini’, ‘firstDbConnect’);
Now, this is were I stray. The video says to pass the values of $config->db to Zend_Db::factory(), but instead I read them into the global registry so that I could later call them from any class or other part of the project.
I did that like this:
$registry = Zend_Registry::getInstance();
$registry->set(’config’, $config);
Then, inside a class I can connect to the database like so:
$db = Zend_Db::factory(Zend_Registry::get(’config’)->db);
Next is how to run a SQL Query & Fetching results
This one is straight forward, all you have to do is use the method fetchAll() and supply it with your query. The fetchAll method returns an array of rows as your record set. Each row is an associative array which you can loop through with a foreach() loop.
Puting a database connection from above with a query looks like this:
$db = Zend_Db::factory(Zend_Registry::get(’config’)->db);
$sql = “SELECT * FROM users”;
$results = $db->fetchAll($sql);
Looping through the returned rows stored in $results looks like this:
foreach($result as $row){
echo $row["username"];
}
It’s as simple as that. Just remember that you’re looping through an array, not an object, so you need to access the array elements as an array and not as an object.
Preparing a SQL Query
This section of the video talks about ‘preparing’ queries. By using the prepare() method you can create a query and assign a parameter place holder which is where you parameter values will be inserted before the query is executed. The thing to notice here is that the value you give the query is not simple appended to the query, instead, the value gets passed to the RDBMS server where it is combined with a machine usable compiled version of the query, this is good to know since it means that this is a good line of defense against SQL injections.
$stmt = $db->prepare(’SELECT * FROM users WHERE user_status= ?’)
Then you execute the query, giving it your parameter value, in this case ‘active’ and store returned rows
$stm->execute(array['active']);
$result = $stmt->fetchAll();
You can then execute the query a second time but with a different parameter value, in this case ‘closed’
$stm->execute(array['active']);
$result = $stmt->fetchAll();
Inserting data to your database
To insert data you will use the insert() method. It takes 2 parameters, the table name you are inserting to, and an associative array that maps the table field names to the values you’re inserting. Also, as an added bonus, just as when you prepare a query, the values you pass into the insert() method get passed in a parameter place holders, bye bye SQL injection.
$db = Zend_Db::factory(Zend_Registry::get(’config’)->db);
$data = array(
‘promos_id’ => 3,
‘product_id’ => 11
);
$db->insert(’promo_products’, $data);
$lastId = $db->lastInsertId();
I emphasized the last line because the lastInsertId() function will return the auto generated primary key value from your last insert. If you write your queries in your data extration layer as classes, as you should be doing, then returning the primary key of the last row you just inserted can come in handy quite often.
Updating data
Updating a record in your database is very similar to inserting it. You will use the update() method which takes 3 parameters, the table name, an associative array that maps columns to NEW values, and an expressing for the WHERE clause of the query. The video will show the array being built directly inside the update() method, but I like to set it up ahead of time and then passing only the array variable, only to keep things cleaner when updating more than one field in the table.
$db = Zend_Db::factory(Zend_Registry::get(’config’)->db);
$data = array(
‘promo_name’ => ‘New name’,
‘promo_code’ => ‘New code’
);
$rowsAffected = $db->update(’promos’, $data, ‘promo_id =3′);
The update() method will return the number of rows that where affected by the query, in case you need to know that value.
Deleting rows
Deleting a row, or rows requires the use of the delet() method, and it takes the table name and an expression for the WHERE clause.
$db = Zend_Db::factory(Zend_Registry::get(’config’)->db);
$n = $db->delete(’order_detail’, ‘order_id = ‘.$order_id);
$n of course being the number of rows deleted by the query.
And there you have it. Although this is a pretty simple overview of how to access your database through the Zend methods, I hope it still serves as a good tool for developers trying to get their feet wet in this environment. Once you become familiar with how each component behaves things become second nature as I’m sure they have been with any other tool and skill set you’ve added your development toolbelt.
