Database Abstraction with Zend_Db - Part 2

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

SQL Code:

  
  

   
   

    
    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:

PHP Code:

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:

PHP Code:

$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:

SQL Code:

  
  

   
   

    
    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:

PHP Code:

$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:

PHP Code:

// ...
'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:

PHP Code:

$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:

PHP Code:

// 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:

PHP Code:

$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:

PHP Code:

$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:

PHP Code:

$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:

SQL Code:

  
  

   
   

    
    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:

PHP Code:

$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:

SQL Code:

  
  

   
   

    
    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:

PHP Code:

$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:

PHP Code:

$badString  =  "Paddy O'Neil" ;
$goodString  =  $db -> quote ( $badString ); 


If we then echo'd $goodString we would get something like:

Code:

'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