Browse DevX
Sign up for e-mail newsletters from DevX


Using the DAO Generator for PHP and MySQL : Page 2

Discover how to generate PHP classes that provide common MySQL database operations using the DAO design pattern.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Introduction to the DAO Classes

Here's a simple description of each of the important DAO classes:

  • ConnectionFactory class: This is the class that works most closely with the database. This “factory” class is responsible for opening connections to a database. It does that with a getConnection method, which takes three arguments representing the database name and username/password credentials. These arguments are mapped in POJO style in the ConnectionProperty class.
  • static public function getConnection() { $conn = mysql_connect(ConnectionProperty::getHost(), ConnectionProperty::getUser(), ConnectionProperty::getPassword()); mysql_select_db(ConnectionProperty::getDatabase()); if(!$conn){ throw new Exception('could not connect to database'); } return $conn; }

  • Connection class: This class represents a single connection to the database. The connection is pulled from ConnectionFactory class through the Connection constructor.

  • public function Connection() { $this->connection = ConnectionFactory::getConnection(); }

    This class is also responsible for closing a connection and executing SQL queries.

  • Transaction class: This class encapsulates a set of functions that enable transactional operations against the database. It offers an array of transactions through the helper class ArrayList, which is a class that simulates a collection over a PHP array. The transactions use connections provided by the Connection class. In addition, the Transaction class provides access to the current transaction through the getCurrentTransaction function.
  • QueryExecutor class: The QueryExecutor class provides functions for executing SQL statements. (Actually, it provides functions that implement the CRUD syntax.) The execute function executes any SELECT SQL statement, while the executeUpdate function executes UPDATE, DELETE, and INSERT statements:
  • public static function execute($sqlQuery){ $transaction = Transaction::getCurrentTransaction(); if(!$transaction){ $connection = new Connection(); }else{ $connection = $transaction->getConnection(); } $query = $sqlQuery->getQuery(); $result = $connection->executeQuery($query); if(!$result){ throw new Exception(mysql_error()); } $i=0; $tab = array(); while ($row = mysql_fetch_array($result)){ $tab[$i++] = $row; } mysql_free_result($result); if(!$transaction){ $connection->close(); } return $tab; } public static function executeUpdate($sqlQuery){ $transaction = Transaction::getCurrentTransaction(); if(!$transaction){ $connection = new Connection(); }else{ $connection = $transaction->getConnection(); } $query = $sqlQuery->getQuery(); $result = $connection->executeQuery($query); if(!$result){ throw new Exception(mysql_error()); } return mysql_affected_rows(); }

  • {databasename}MySqlDAO class: The DAO generator creates this class, which provides a set of DAO functions specific to a particular database. The generated class resides in the generated/class/mysql folder, and its name is a combination of the database name and the suffix MySqlDAO. Later in this article you'll see how to generate and use this class.
Author's Note: The QueryExecutor and the {databasename}MySqlDAO classes use a helper class named SqlQuery, which encapsulates functions to represent and store SQL statements.

With that background in place, it's time to jump into an example.

Thanks for your registration, follow us on our social networks to keep up-to-date