data access object (DAO) provides an abstract interface to a database—giving developers access to common database operations without them having to know the database schema details—essentially, data access object separate the business logic and data tiers of an application. This separation is important, because these two important application tiers should know almost nothing about each other, and you can expect both to evolve frequently and independently. With DAO in place, changing business logic can use the same DAO interface, while changes to persistence logic does not affect DAO clients—as long as the interface remains correctly implemented.

The DAO generator for PHP and MySQL automates the process of creating DAO classes for MySQL databases. In this article, you’ll see how to:

• Install the DAO generator for PHP and MySQL
• Generate a DAO artifact using a given database (bookstore)
• Create an application using DAO generator tool and its factory classes
• Create a transaction spanning multiple SQL operations
• Add a custom DAO function to a generated class

DAO generator for PHP and MySQL

Figure 1. DAO Generator Architecture: The diagram shows the architecture and data flow for the DAO generator for PHP and MySQL.

A DAO generator creates classes in a specific language, usually for a specific target database. You use the DAO generator for PHP and MySQL to generate PHP classes that query and update MySQL databases using the DAO design pattern. The tool is relatively simple to install and use, but to use it effectively, you need to know a little about the DAO mechanism.

You can download the DAO generator for PHP and MySQL here. To install it, follow these steps:

1. Download the phpdao-1.7.zip archive.
2. Unzip the archive in the htdocs directory.
3. Setup a connection to an existing database by specifying the database properties in the templates/class/dao/sql/ConnectionProperty.class.php file.
4. Run the generate.php script.
5. The tool places generated classes in the newly created folder named generated.

create table books    (id int not null auto_increment primary key,    title varchar(50),    author varchar(50),    yearofpublication int,    publisher varchar(50),    price int);

Before generating the DAO artifacts, populate the books table with five records by executing the following INSERT statements:

insert into books values(1,"Annabel Lee",   "Edgar Allan Poe",1849,"The Literature Page",256);insert into books values(2,"The Ballad of Reading Gaol",   "Oscar Wilde",1898,"The Literature Page",475);insert into books values(3,"The Sonnets",   "Edgar Allan Poe",1602,"The Literature Page",300);insert into books values(4,"Winnetow",   "Karl May",1956,"The truth",123);insert into books values(5,"JBoss Tools 3",   "Anghel Leonard",2009,"Packt",569);

With the database and books table created and populated, you can execute the DAO generator to create the DAO artifacts. To run the generator, follow these steps:

1. In the ConnectionProperty class set the connection properties host, user, password, and database.
2. Run the generated.php script.
3. The output will look like this:

generated/class/dto/Books.class.phpvarchar(50)varchar(50)varchar(50)varchar(50)int(11)int(11)varchar(50)varchar(50)int(11)int(11)generated/class/mysql/BooksMySqlDAO.class.phpvarchar(50)varchar(50)int(11)varchar(50)int(11)generated/class/dao/BooksDAO.class.phpgenerated/include_dao.phpgenerated/class/dao/DAOFactory.class.php

4. The last three lines of the output show the names of generated classes, placed in a new generated folder that appears as a subfolder in the phpdao-1.7 directory.

The three classes generated for application work with the books table. The first class, in the file generated/class/dto/Books.php, defines an object that represents the books table:

The second class, in the generated/class/dao/BooksDAO.php file (see Listing 1), is an interface that defines various operations on the books table.

The third and final class, in the file generated/class/mysql/BooksMySqlDAO.php, implements the preceding interface class. You can see this class in the corresponding folder, (it’s not included here because of its length).

Working with a Transaction

This section shows how to use the DAO generator for PHP and MySQL to create a transaction. To use DAO generator for PHP and MySQL create the example.php script and place it into the generated folder, which should contain all DAO files. In this example the transaction spans two SELECT and one DELETE SQL statements:

clean();$transaction->rollback();// Starting a new transaction$transaction = new Transaction();echo '****** Query All the books table ******'.'
'; $arr = DAOFactory::getBooksDAO()->queryAll();for($i=0;$iid.' '.$row->title.' '.$row->author.' '.      $row->yearofpublication.' '.$row->publisher.' '.      $row->price.'

';}echo '****** Deleting the third row ******'.'
';$rowDeleted = DAOFactory::getBooksDAO()->delete(3);echo 'rows deleted ='.$rowDeleted.'

';echo '****** Loading the fifth row ******'.'
';$art = DAOFactory::getBooksDAO()->load(5);echo 'Price for the fifth record book is = '.$art->price.'

';echo '****** Printing all rows order by title ******'.'
';$article = DAOFactory::getBooksDAO()->queryAllOrderBy('title');for($i=0;$iid.' '.$row->title.' '.$row->author.' '.      $row->yearofpublication.' '.$row->publisher.' '.      $row->price.'

';}// Commit transaction$transaction->commit();?>

When you run the example, it outputs:

****** Query All the books table ******1 Annabel Lee Edgar Allan Poe 1849 The Literature Page 2562 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 453 The Sonnets Edgar Allan Poe 1602 The Literature Page 3004 Winnetow Karl May 1956 The truth 1235 JBoos Tools 3 Anghel Leonard 2009 Packt 569****** Deleting the third row ******rows deleted =1****** Loading the fifth row ******Price for the fifth record book is = 569****** Printing all rows order by title ******1 Annabel Lee Edgar Allan Poe 1849 The Literature Page 2565 JBoos Tools 3 Anghel Leonard 2009 Packt 5692 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 454 Winnetow Karl May 1956 The truth 123 

Adding a New DAO Function

This section shows how to create a new DAO function that will print all the records with publication dates between 1850 and 2009. Add this function to the BooksMySQLDAO.class.php after all the existing query functions:

public function queryByYear(){   $sql = "SELECT * FROM books WHERE yearofpublication".      " BETWEEN '1850' AND '2009'";   $sqlQuery = new SqlQuery($sql);   return $this->getList($sqlQuery);}

You can add to the example.php page to call the preceding function and print the records for which yearofpublication is between 1850 and 2009:

echo "****** Printing all rows where yearofpublication is between.    '1850' and '2009'******".'
';$arr = DAOFactory::getBooksDAO()->queryByYear();for($i=0;$iid.' '.$row->title.' '.$row->author.' '.      $row->yearofpublication.' '.$row->publisher.' '      .$row->price.'

';}

The output is:

****** Printing all rows where yearofpublication is between '1850' AND '2009'******2 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 454 Winnetow Karl May 1956 The truth 1235 JBoss Tools 3 Anghel Leonard 2009 Packt 569

You’ve seen how the DAO mechanism works, including the process of using a factory class schema to generate a DAO artifact using a specified database (bookstore), create an application using the DAO generator tools and its factory classes, create a transaction over the bookstore database, and how to add new DAO functions to the generated {databasename}MySqlDAO class. While you can code all this functionality manually, it’s better to automate such repetitive work, because automation reduces errors and saves time.