Introduction
Database abstraction layers provide an important role in modern web applications built with PHP. They allow you to easily switch your back end database server by changing just a few lines of code in your application.
The Zend Framework provides a powerful database abstraction layer in the form of the Zend_Db component. This tutorial will be looking at the Zend_Db component in closer detail.
In Part 1 we showed you how to use the Zend_Db_Select class to build complex SELECT queries simply and quickly.
In Part 2 we will show you how to build INSERT, UPDATE and DELETE queries using the Zend_Db component.
Requirements
To use the Zend_Db component you will first need to download the Zend Framework.
Visit the Zend Framework download page and download the newest version of the Zend Framework (1.5RC1 at the time of writing).
Once downloaded, unzip the Zend Framework archive to your Desktop or Home folder. The folder we are interested in is the library folder. Copy the library folder and its contents to your web folder so that your PHP scripts can access it.
Creating a database
Throughout this tutorial we will be using the table described below. If you wish to follow along with the tutorial then I would recommend creating the table on your own database server.
Articles Table: Structure
CREATE TABLE `log` (
`id` int ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`priority` int ( 10 ) UNSIGNED NOT NULL ,
`message` varchar ( 100 ) NOT NULL ,
`created` datetime NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Creating a new Zend_Db object
Lets start by creating a file called part2.php and putting it in the same folder that you placed the /library folder earlier in the tutorial. We will use this file for our examples throughout this tutorial.
The first step in using Zend_Db is to include the class:
set_include_path ( '.'
. PATH_SEPARATOR . './library'
. PATH_SEPARATOR . get_include_path ()
);
include 'Zend/Db.php' ;
Notice how we first set the PHP include path before including the file. This allows the Zend Framework to find the files it requires.
Now that we have included the Zend_Db core we can setup our database connection:
$dbConfig = array(
'host' => 'localhost' ,
'username' => 'user' ,
'password' => 'pass' ,
'dbname' => 'my_database'
);
$db = Zend_Db :: factory ( 'Pdo_Mysql' , $dbConfig );
In this example we start by creating an array with our database config, hostname, username, password and database name. We then use the Zend_Db Factory() method to create a new database connection using the Pdo_Mysql driver.
Now that we have our $db
object we can start creating our queries.
Insert a New Row
When inserting a new row into a database table you would usually use something like:
INSERT INTO `log` ( `priority` , `message` , `created` ) VALUES ( 1 , 'Unable to connect to the database server' , NOW ( ) )
To do this using Zend_Db we use the insert()
method.
insert()
takes an array of key/value pairs that contain the new data you wish to insert:
$priority = 1 ;
$message = 'Unable to connect to the database server' ;
$data = array(
'priority' => $priority ,
'message' => $message ,
'created' => new Zend_Db_Expr ( 'NOW()' )
);
In this example, we start by declaring two variables, $priority
and $message
. These would typically be received from a form or elsewhere in the application.
We then declare our $data
array that contains our tables column names and the values we wish to insert.
You will notice that for the 'priority'
and 'message'
columns we don't escape our variables using mysql_real_escape_string()
or similar.
This is because Zend_Db sends our variables to the database server separately from the query. As they are never actually included in the query, they cannot be used for SQL injection.
In our 'created'
column we wish to use the SQL function NOW(). If we where to simply use:
// ...
'created' => 'NOW()'
then Zend_Db would treat it as a regular string to be inserted.
To overcome this problem we use the Zend_Db_Expr
class. When Zend_Db is passed a Zend_Db_Expr object it knows to treat it as an SQL function or expression. In our case, it would call the SQL function NOW()
which will insert the current date and time into our new row.
Now that we have our $data
array with our column/value pairs we can insert them into our database table:
$db -> insert ( 'log' , $data );
The Zend_Db insert()
method takes two arguments. The first is the name of the table that we wish to insert our data in to. In our example, we are using the log
table. The second is our $data
array that will be inserted.
If you save and run this script you should find a new row has appeared in your database - View Screenshot
Fetching Auto-increment Values
After inserting a new row in to the database, you will often need to fetch the auto_increment value that it generated. To do this, Zend_Db provides the lastInsertId()
method.
Note : Auto Increment is not supported by all database servers. If in doubt, check the documentation for your database server.
To fetch the auto_increment value you we use the following:
// Insert query goes here...
$id = $db -> lastInsertId ();
echo 'Last row inserted was given ID #' . $id ;
This fetches the auto_increment value and stores it in the $id
variable. It then echo's the value to the screen.
Updating Existing Rows
Updating rows is very similar to inserting rows. The main differences are that we use the update()
method and provide a WHERE statement to let Zend_Db know which rows we wish to update.
Lets go ahead and get started by creating our $data
array with the column names / values that we wish to update:
$rowId = 1 ;
$newPriority = 5 ;
$data = array(
'priority' => $newPriority
);
As with our INSERT queries above, the $data
array contains an array of column/value pairs. In this example, we are changing the 'priority'
column and giving it the value of $newPriority
.
Our next step is to create the WHERE clause which will tell Zend_Db which row(s) to update:
$where = $db -> quoteInto ( 'id = ?' , $rowId );
Here we are using the quoteInto()
method to specify that we want to update the row that has an 'id'
of $rowId
.
The quoteInfo()
method contains a question mark (?) placeholder for the $rowId
value. Zend_Db will then send the $rowId
variable separately from the rest of the query to protect against SQL injection.
Now that we have our $data
array and $where
clause we can update our table:
$db -> update ( 'log' , $data , $where );
As you can see, the update()
method takes three arguments. The first is the table name that we wish to update, 'log'
in our case. The second is an array of column/value pairs that we are updating. The third argument is the SQL WHERE clause.
If we where to write this query manually it would look something like:
UPDATE `log` SET `priority` = 5 WHERE ( id = 1 )
Updating a Row With Multiple Where Statements
Sometimes you will find that you need to specify more than one WHERE clause to your update query. Zend_Db allows us to do this by passing an array as the third argument to the update()
method.
For example:
$oldPriority = 1 ;
$newPriority = 5 ;
$creationTime = '2008-03-05 20:22:08' ;
$data = array(
'priority' => $newPriority
);
$where = array();
$where [] = $db -> quoteInto ( 'priority = ?' , $oldPriority );
$where [] = $db -> quoteInto ( 'created = ?' , $creationTime );
$db -> update ( 'log' , $data , $where );
Here we just push multiple WHERE clauses into our $where
array and then pass that to the update()
method.
If we where to write this query manually it would look something like:
UPDATE `log` SET `priority` = 5 WHERE ( priority = 1 ) AND ( created = '2008-03-05 20:22:08' )
Deleting Rows
As you might expect, Zend_Db provides a simple method to delete one or more rows in a table. The delete()
method takes two arguments, the first is the table name and the second is a WHERE clause.
Lets take a look at a simple example:
$rowId = 1 ;
$where = $db -> quoteInto ( 'id = ?' , $rowId );
$db -> delete ( 'log' , $where );
Fairly straightforward, we use the quoteInto()
method to create our WHERE clause ($where
) then pass that and the table name to the delete()
method.
Manually Quoting Strings
There will be times that you will need to manually quote/escape your strings before using them in SQL queries. For example, you may be building a manual query from user input rather than using Zend_Db / Zend_Db_Select's methods.
To quote your strings in regular PHP you would typically use something like mysql_real_escape_string()
.
Zend_Db provides the quote()
method to achieve this:
$badString = "Paddy O'Neil" ;
$goodString = $db -> quote ( $badString );
If we then echo'd $goodString
we would get something like:
'Paddy O/'Neil'
Important : Note that quote()
also wraps the string in single quotes as well as escaping any quotes it finds within the string. Functions like mysql_real_escape_string()
will only escape the string, not wrap it in single quotes.
Conclusion
Hopefully this article has shown you that using an advanced database abstraction layer such as Zend_Db is quicker and easier than writing the manual queries!
It also has the major benefit of giving portability between different database systems that simple database wrapper classes don't provide. For example, some database servers do not support the LIMIT keyword. Zend_Db and Zend_Db_Select take that into account and the limit()
method adjusts the final query as needed.
References and Further Reading
All code from this tutorial in a single file
Zend Framework
Zend_Db Component Manual