Nov. 14, 2007 at 3:48pm

PHP Patterns, Part II

The DAO and VO Patterns

In this installment, we will be looking at two patterns that have been 'borrrowed' from Java. If you've had any development experience with J2EE, you are probably well aware of how handy Data Access Objects and Value Objects can be. If you haven't, don't fret! This article was written especially for you!

If you've never heard these terms before, you may be wondering why I have chosen to group them together within one article. The simple explanation is ... well you'll see. For now just accept that they go hand-in-hand, much like salt and pepper or peanut butter and jelly or <insert clever combination here>.

Excited? Let's dig deeper...

I. Data Access Objects (DAOs) and Value Objects (VOs), an Introduction
These two classes comprise what is often called the "model" layer of the MVC (Model View Control) model. If you aren't familiar with this concept do yourself a huge favor and read up on it - it will save you and your peers quite a bit of time further down the road on any project.

The DAO is basically the object you use to query your data source within your application. Rather than sprinkling SQL queries throughout your code, it allows you to encapsulate everything related to accessing your database within a single class. That way, if you ever change databases or need to tweak a query, you can do it in one place that's easy to find. Think of it like a filing cabinet that keeps all your papers from floating all over your desk.

The VO does what you might expect a "value object" to do - it holds values. It provides you with a nifty object to pass around your application and typically contains a single row of information from your database. Again the goal here is to simplify the process of handling data retrieved from the database.

II. The VO, A Closer Look
Creating a VO is probably one of the simplest classes you will ever create. It basically mirrors a table in your database, with setter and getter methods for each field in that table. First, let's pretend you have a simple database table called "users" that has the following columns:

• id
  
• user
• pass

Using this information, we can create our very first Value Object:

class UserVO {
    protected $id;
    protected $user;
    protected $pass;
    
    public function setId($id) {
        $this->id = $id;
    }
    
    public function getId() {
        return $this->id;
    }
    
    public function setUser($user) {
        $this->user = $user;
    }
    
    public function getUser() {
        return $this->user;
    }
    
    public function setPass($pass) {
        $this->pass = $pass;
    }
    
    public function getPass() {
        return $this->pass;
    }
}


Pretty simple and to the point, huh? You may be wondering why I have gone through and written explicit setters and getters for each value, rather than just making two generic functions (or simply making the class variables public). The primary reason for this has more to do with coding standards than anything else. It is usually good practice to prevent class variables from being accessed directly. This way you don't have to anticipate what might happen ouside the class. By declaring functions for each specific value, you have more control over the use of each value as well. For instance, where appropriate, you could use the addslashes() and stripslashes()  functions to automatically massage data before it is inserted into the database.

II. The DAO, A Closer Look
Now that we have created our first VO, let's take a look at its partner in crime, the DAO. Let's take a look at some code before we start picking it apart:

class UserDAO {
    protected var $connect;
    protected var $db;
    
    // Attempts to initialize the database connection using the supplied info.
    public function UserDAO($host, $username, $password, $database) {
        $this->connect = mysql_connect($host, $username, $password);
        $this->db = mysql_select_db($database);
    }
    
    // Executes the specified query and returns an associative array of reseults.
    protected function execute($sql) {
        $res = mysql_query($sql, $this->connect) or die(mysql_error());
        
        if(mysql_num_rows($res) > 0) {
            for($i = 0; $i < mysql_num_rows($res); $i++) {
                $row = mysql_fetch_assoc($res);
                $userVO[$i] = new UserVO();
                
                $userVO[$i]->setId($row[id]);
                $userVO[$i]->setUsername($row[username]);
                $userVO[$i]->setPassword($row[password]);
            }
        }
        return $userVO;
    }
    
    // Retrieves the corresponding row for the specified user ID.
    public function getByUserId($userId) {
        $sql = "SELECT * FROM users WHERE id=".$userId;
        return $this->execute($sql);
    }
    
    // Retrieves all users currently in the database.
    public function getUsers() {
        $sql = "SELECT * FROM users";
        return $this->execute($sql);
    }
    
    //Saves the supplied user to the database.
    public function save($userVO) {
        $affectedRows = 0;
        
        if($userVO->getId() != "") {
            $currUserVO = $this->getByUserId($userVO->getId());
        }
        
        // If the query returned a row then update,
        // otherwise insert a new user.
        if(sizeof($currUserVO) > 0) {
            $sql = "UPDATE users SET ".
                "username='".$userVO->getUsername()."', ".
                "password='".$userVO->getPassword()."', ".
                "WHERE id=".$userVO->getId();
            
            mysql_query($sql, $this->connect) or die(mysql_error());
            $affectedRows = mysql_affected_rows();
        }
        else {
            $sql = "INSERT INTO users (username, password) VALUES('".
                $userVO->getUsername()."', ".
                $userVO->getPassword()."')".
            
            mysql_query($sql, $this->connect) or die(mysql_error());
            $affectedRows = mysql_affected_rows();
        }
        
        return $affectedRows;
    }
    
    // Deletes the supplied user from the database.
    public function delete($userVO) {
        $affectedRows = 0;
        
        // Check for a user ID.
        if($userVO->getId() != "") {
            $currUserVO = $this->getByUserId($userVO->getId());
        }
        
        // Otherwise delete a user.
        if(sizeof($currUserVO) > 0) {
            $sql = "DELETE FROM users WHERE id=".$userVO->getId();
            
            mysql_query($sql, $this->connect) or die(mysql_error());
            $affectedRows = mysql_affected_rows();
        
        return $affectedRows;
    }
}


The Data Access Object consists a handful of basic methods. First is the constructor, which basically initiates the connection to the database. You pass it the database connection info and credentials, and it takes care of the rest.

Next comes the getter functions, which basically represent a collection of the queries used to access the database. For example, you might have a getByUserId() method that returns the UserVO we created above filled with the corresponding data for a particular user ID.

Following the getters is the save() function, which essentially does what you would imagine it does. You pass a VO to it and it will either update an existing record if it finds one that matches the VO or will insert a new row. I'll bet you're thinking what I thought the first time I learned of this function: "holy cow! that just reduced 8+ lines of code down to one!"

Finally we have the big scary delete() function that, you guessed it, deletes a record from the database. To make it slightly less scary, it requires that you pass it a VO that matches a database row exactly. This will force you to have first retrieved the row you want to delete before calling this method. This helps prevent the accidental deletion of a record.

And there you have it! Together these two classes can save you a tremendous amount of effort and time, makes it much easier to maintain say 6-12 months from now when you have completely forgotten how you wrote that file manager script.

A side note: If you live in fear of having to write these classes for databases with 100 tables that have 200 columns each, don't despair! With the help of Google you can find countless PHP DAO and VO generators that will build the code for you (take a look here for example). Some will do it based on existing database tables, and others will do it based on what you tell them. They might still require some tweaking afterwards, but at least that way the hard part would be done and over with.

Posted in Coding Techniques, From the Workbench, MySQL, PHP, Software Engineering by Nick Williams

Comments (4)

function login() {

    $sql = "SELECT username FROM User 

               WHERE username='$this->user'

                    AND password=PASSWORD('$this->pass')"

    $res = mysql_query($sql);

    if(mysql_num_rows($res) > 0) {

        $_SESSION['logged_in'] = true;

        return true;

    } else {

        return false;

    }

}

$user = new User();

$user->setUsername($_POST['un']);

$user->setPassword($_POST['pw']);



if($user->login()) {

    // forward to account landing page

} else {

   // reload form with an error message

}