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:
<?php
// Include all DAO files
require_once('include_dao.php');
$transaction = new Transaction();
//Clean table
//DAOFactory::getBooksDAO()->clean();
$transaction->rollback();
// Starting a new transaction
$transaction = new Transaction();
echo '****** Query All the books table ******'.'<br/>';
$arr = DAOFactory::getBooksDAO()->queryAll();
for($i=0;$i<count($arr);$i++){
$row = $arr[$i];
echo $row->id.' '.$row->title.' '.$row->author.' '.
$row->yearofpublication.' '.$row->publisher.' '.
$row->price.'<br/><br/>';
}
echo '****** Deleting the third row ******'.'<br/>';
$rowDeleted = DAOFactory::getBooksDAO()->delete(3);
echo 'rows deleted ='.$rowDeleted.'<br/><br/>';
echo '****** Loading the fifth row ******'.'<br/>';
$art = DAOFactory::getBooksDAO()->load(5);
echo 'Price for the fifth record book is = '.$art->price.'<br/><br/>';
echo '****** Printing all rows order by title ******'.'<br/>';
$article = DAOFactory::getBooksDAO()->queryAllOrderBy('title');
for($i=0;$i<count($article);$i++){
$row = $article[$i];
echo $row->id.' '.$row->title.' '.$row->author.' '.
$row->yearofpublication.' '.$row->publisher.' '.
$row->price.'<br/><br/>';
}
// 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 256
2 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 45
3 The Sonnets Edgar Allan Poe 1602 The Literature Page 300
4 Winnetow Karl May 1956 The truth 123
5 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 256
5 JBoos Tools 3 Anghel Leonard 2009 Packt 569
2 The Ballad of Reading Gaol Oscar Wilde 1898 The Literature Page 45
4 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'******".'<br/>';
$arr = DAOFactory::getBooksDAO()->queryByYear();
for($i=0;$i<count($arr);$i++){
$row = $arr[$i];
echo $row->id.' '.$row->title.' '.$row->author.' '.
$row->yearofpublication.' '.$row->publisher.' '
.$row->price.'<br/><br/>';
}
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 45
4 Winnetow Karl May 1956 The truth 123
5 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.