PHP: Data Access Objects part 1

Posted on the 28th of October 2009

In this post I will cover the use of Data Access Objects (DAO) in PHP. The primary goal by doing so is greater abstraction by seperating the layers as well as covering some of the new techniques of PHP5. It will also come as a great help for you who create massive database web-applications which often result in a lot of duplicate code.

Introduction

Many years ago when I started playing around with PHP programming I discovered the joy of saving and retrieving data from a database. From that worldbreaking discovery it didn’t take long before I started doing smaller web-applications which consisted of many different pages and parts – which all (almost) fetched data in the database. This resulted in a lot of copy-and-paste code, and the same create-connection code to be written again and again and again… and again!

Let’s see if we can do something clever and avoid the above problem and make our PHP code more object-oriented than database oriented and at the same time separate logic and HTML.

Example without the use of DAO’s

Let’s create a simple users table, which most of us have probably tried before.

CREATE TABLE `users` (
`user_id` int(11) NOT NULL auto_increment,
`username` varchar(20) NOT NULL,
`name` varchar(20) NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`)
)

Normalle we will write code as seen below to connect to the database, query it and display the result.

<?php

$db_username = "dbtest";
$db_password = "dbpassword";
$db_hostname = "localhost";
$db_name = "database";
$query = "SELECT * FROM users"

$connection = @mysql_connect($db_hostname, $db_username,$db_password) or die("Cannot connect to database");

if (! mysql_selectdb($db_name, $connection)) die("Cannot open table");

if (!($result = @ mysql_query($strsql, $connection))) die("Cannot select record");

echo "<h1>Alle users</h1>";

while($row = mysql_fetch_array($result)){
$row = mysql_fetch_array($result);
echo "UserId: ".$row['user_id']."<br/>";
echo "Username: ".$row['username']."<br/>";
echo "Name: ".$row['name']."<br/>";
echo "Email: ".$row['email']."<br/>";
}
?>

It is not that the above example is horrible in any way. I just think that we can do it smarter and more simple. Especially if we have an application where you have to fetch data from the database on many different pages. It can easily get redundant and cluttered, especially because PHP and HTML tend to be mixed if you dont watch out when you make web applications. Of course this can’t be avoided totally – but we can atleast do something, so that the PHP in the HTML will only be view-related.

Let’s take a closer look at DAO’s.

Data Access Objects

First we will make 2 classes, which will handle the database connection and some of the database operations. The first one we give the name class-database.php with the following content (of course you have to replace some of the values so that it matches your host, username, password etc)

<?php
class Database {
private $host = "databasehost";
private $username = "databaseusername";
private $pwd = "databaseadgangskode";
private $db = "databasenavn";

public function GetDBConn()
{
return mysqli_connect($this->host, $this->username, $this->pwd, $this->db);
}
}
?>

This class will be used to get the database connection from the other objects we create.

Now it is time to create a class with a bit more functionality, the class-resultset.php.

<?php

class ReadOnlyResultSet {

private $rs;

function __construct($rs)
{
$this->rs = $rs;
}

// Get the next object
function getNext($dataobject)
{
$row = mysqli_fetch_array($this->rs);
// Get the properties of the object
$class = new ReflectionObject($dataobject);

$properties = $class->getProperties();

// Loop through the properties
for ($i = 0; $i < count($properties); $i++) {
$prop_name = $properties[$i]->getName();
$dataobject->$prop_name = $row[$prop_name];
}

return $dataobject;

}

// Reset the pointer back to the beginning

function reset()
{
mysqli_data_seek($this->rs, 0);
}

function rowCount()
{
return mysqli_num_rows($this->rs);
}

}

?>

Now we have created the class that has the functions we will use on the queried resultsets.

The next thing on the list, is to create the DAO’s themself. This is an object which looks like the table in the database. This means an object with the same properties as the fields you want to fetch/save from the database.

Den klasse vi lige har oprettet, indeholder funktioner, som bruges på de recordsets vi hiver ud.

Lets create one that matches our newly created users table.

<?php
require_once 'class-database.php';

class User extends Database {
public $user_id;
public $username;
public $name;
public $email;

public function insert()
{
$dbc = $this->getDBConn();
$sql = "INSERT INTO users (name, username, email)VALUES('$this->name', '$this->username', '$this->email')";
$rs = mysqli_query($dbc,$sql);
$this->user_id = mysqli_insert_id($dbc);
}
}
?>

We have created public variables matching the data in the database. As well as an insert() function. We will use this when we want to save a user in the database. Basically it is just an INSERT-query, which takes the values from our user-object. We extends our user object with the Database-class, so we can access the database-connection by calling the getDBConn()-function. After a succesful insertion of the new user, the primary-key is saved in the user_id variable.

Below is an example of how to create a new user in the database.

$user = new User();
$user->username = 'jesper';
$user->name = 'Jesper Christiansen';
$user->email = 'mig@jesperchristiansen.dk'
$user->insert();

Easy! :)

Let’s create a new method so that we can also update an existing user in the database.

public function update()
{

$dbc = $this->getDBConn();

$sql = "UPDATE users SET name = '$this->name', username = '$this->username', email = '$this->email' WHERE user_id = $this->user_id";

$rs = mysqli_query($dbc,$sql);

}

The update function isn’t any fun if we have to write all of the values that are already in the database ourselves and then update these. So let’s create a function to get an existing user.

public function find()

{

$sql = "SELECT * FROM users";

$where = array();

$class = new ReflectionClass('user');

$properties = $class->getProperties();

for ($i = 0; $i < count($properties); $i++) {
$name = $properties[$i]->getName();

if ($this->$name != '') {
$where[] = "`" . $name . "`='". mysql_escape_string($this->$name) . "'";

}

}

// If we have a where clause, then create it

if (count($where) > 0){
$sql .= " WHERE " . implode(' AND ', $where);
}

$dbc = $this->getDBConn();

$rs = mysqli_query($dbc,$sql);

include_once('class-resultset.php');

return new ReadOnlyResultSet($rs);

}

We simply make a SELECT-statement on behalf of the properties on the user object. If the user doesn’t have any data in its properties, the select-statement will retrieve all users. If we create a users object with a name, and then call the find() method – then all users with the given name will be fetched from the database and is returned as a resultset by using our resultset-class.

Example that will fetch all users with the name “Jesper”

// Find all users with the name Jesper
$user = new User();
$user->name = "Jesper";
$rs = $user->find();

To display these users we do the following

for($i = 0; $i < $rs.rowCount(); $i++) {

$foundUser = $rs.getNext(new User());
echo "Name: ".$foundUser->name."<br/>";

}

First we get the total rowCount in a for-loop. We then call the getNext()-function to get the next row in the resultset. Since it is a user object we are getting, we need to pass that as an argument to the getNext-function. Then we simply echo the properties on the user-object.

Conclusion

The possibilities with DAO’s are endless. In my eyes it gives a more abstract representation of the data in the database and you assure better seperation of the layers – compared tih inline SQL in your presentation-layer (HTML). This might not be the best approach on this and can be done in many different ways. Please write a comment if you found this article useful or have any objections :)

Take care!

Sourcecode can be downloaded here!