Write your own PHP5 MVC framework: Handling databases [part 2] – The ORM

by blowfish

We had earlier introduced the concept of working with models (db objects) rather that writing db queries in your php scripts. We had seen how it offers a simpler object oriented interface to the db, and what a joy it is to work with. Here we expound on the M in ‘MVC’. In brief though, models allow you to treat db data in a mysql or postgres or oracle (or any other rdbms) database as php objects. These objects might represent a single row in a db table, or span across multiple relations in the case of a row having related data in other tables ; they can also be a collection of db objects (which then makes them iterable). Whatever they are, they offer a high degree of encapsulation and enforce the DRY principle ( ie Dont Repeat Yourself). You will not repeatedly write the same queries fetching data, instead , you will concentrate on the task at hand in your application.

Consider :

$rs = mysql_query("select username form users where id = 2");
while($row = mysql_fetch_array($rs)) {
echo $row['username'];
}

With an orm we are able to summarise the above repetitive cycle into :

$user = new User(2);
echo $user->username;

There is yet much more to it, just read on..

You will need the following files (dao.php, db.php, db_connect.php) in 0rm.zip  :

0rm.zip

This ORM has 3 classes in 3 files:

  • db_conn in db_conn.php
  • db in db.php
  • dao in dao.php

db_conn

db_conn as you may have guessed, provides a db connection to the database through whatever db driver you have chosen : pgsql, mysql and perhaps mssql, odbc etc if you have compiled pdo support for those into your apache php module.

db_conn also maps out relations between different tables in your database.

This is done automatically for you, provided you followed 4 simple naming conventions when designing your db tables :

  1. All primary key fields are called “id”
  2. Foreign keys are named <tablename_singular_form>_id eg users table has a one-to-many foreign key link with the persons table through a foreign key “person_id”; “persons” becomes “person” and then we append “_id” to it.
  3. Table names and all field names are rendered in lower case e.g. users, username rather than Users, UserName etc.
  4. Table names are in their plural forms, though this may not always be possible.

With these in place, db_conn will create an array of all the tables in the database, their fields, their relations with other tables and the field types.

The code below:

print_r(db_conn::instance());

anywhere in the controllers you will give you an idea of how db_conn maps out and stores the information on table relations.

db

The db class provides a database abstraction layer. this means, it allows to run queries without having to care whether your queries are compatible with whatever RDBMS runs beneath your application.

You can do:-

  1. SELECTs
  2. INSERTs
  3. UPDATEs
  4. DELETEs

Now lets assume we have a db with the 3 tables:

users

  1. id
  2. username
  3. email
  4. password
  5. person_id

persons

  1. id
  2. firstname
  3. lastname
  4. phone
  5. organisation_id

organisations

  1. id
  2. name
  3. location
  4. phone

1. SELECTs

Example 1: a simple select


SELECT username from users where id = 1


 $user = db::factory()
		 ->SELECT("username")
		 ->from("users")
		 ->where(array('id' => 1))
		 ->run(); 

you must run() or execute() every query for it to be actually run against the db
the query will return a 2 dimensional array result
echo $user[0]['username'];

Example 2 : SELECT * from users where id = 1


 $user = db::factory()
		 ->SELECT("*")
		 ->from("users")
		 ->where(array('id' => 1))
		 ->run();
the query will return a 2 dimensional array result
echo $user[0]['username'];

Example 3 : get(‘fieldname’)


 $username = db::factory()
		 ->SELECT("*")
		 ->from("users")
		 ->where(array('id' => 1))
		 ->get('username')
		 ->run();
with get() , the query will simply return the SELECTed column
echo $username;

Example 4 : a join between users and persons table


SELECT concat(firstname, ' ' , lastname) as names from users, persons where users.person_id = persons.id and id = 1;$username = db::factory()
		 ->SELECT("concat(firstname, ' ' , lastname) as names ")
		 ->from("users", "persons")
		 ->join(array('users.person_id'))
		 ->where(array('id' => 1))
		 ->get('names')
		 ->run();
echo $username;

Example 5 : prepared queries


SELECT concat(firstname, ' ' , lastname) as names from users, persons where users.person_id = persons.id and id = 1;$username = db::factory()
		 ->SELECT("concat(firstname, ' ' , lastname) as names ")
		 ->from("users", "persons")
		 ->join(array('users.person_id'))
		 ->where(array('id' => '?'))
		 ->get('names')
		 ->run(1);
echo $username;

Example 6 : insert()


INSERT INTO users (username, password, email) VALUES ('johndoe', md5('secret'), 'john@doe.com');
$last_insert_id = db::factory()
	          ->insert("users",
		  array(
			'username' => '?',
			'password' => '?',
			'email' => '?'
		  )
	          )
	          ->run('johndoe', 'secret', 'john@doe.com');

Example 7 : update()


UPDATE users SET email = 'johndoe@google.com' WHERE id = 1;
db::factory()->update(“users”, array( 'email' ⇒ '?' ) )->run('johndoe@google.com');

Example 8 : _and()


SELECT users.username as username FROM users, persons WHERE persons.id = users.person_id AND persons.firstname = 'john' AND ersons.lastname = 'kamau';$username = db::factory()
		 ->SELECT("users.username as username")
		 ->from("users", "persons")
		 ->join(array('users.person_id'))
		 ->_and(array('persons.firstname' => '?', 'persons.lastname' => '?'))
		 ->get('username')
		 ->run('john', 'kamau');
echo $username;

Example 9 : _or()


SELECT users.username as username FROM users, persons WHERE persons.id = users.person_id AND persons.firstname = 'john' OR persons.firstname = 'kamau';$users = db::factory()
		 ->SELECT("users.username as username")
		 ->from("users", "persons")
		 ->join(array('users.person_id'))
		 ->_or('persons.firstname' => '?')
		 ->_or('persons.firstname' => '?')
		 ->run('john', 'kamau');
foreach ($users as $user) {
echo $user['username'] . "<br/>";
}

the result might be a multiple result set of guys named john or kamau

Example 10 : _in()


 SELECT users.username as username FROM users, persons WHERE
 persons.id = users.person_id AND
 persons.firstname IN ('john', 'kamau');
$users = db::factory()
		 ->SELECT("users.username as username")
		 ->from("users", "persons")
		 ->join(array('users.person_id'))
		 ->_or('persons.firstname' => '?')
		 ->_or('persons.firstname' => '?')
		 ->run('john', 'kamau');

the result might be a multiple result set of guys named john or kamau

foreach ($users as $user) {
echo $user['username'] . "<br/>";
}

Example 11 : _in() with a subselect


SELECT username FROM users WHERE person_id IN (SELECT id from persons WHERE firstname = 'john' OR firstname = 'kamau');$users = db::factory()
		 ->SELECT("username")
		 ->from("users")
		 ->_in( "id",
				db::factory()
				->select('id')
				->from('persons')
				->_or('persons.firstname' => '?')
				->_or('persons.firstname' => '?')
				->params('john', 'kamau')
				/* dont run() the subquery just yet*/
		 )
		 ->run();

the result might be a multiple result set of guys named john or kamau

foreach ($users as $user) {
echo $user['username'] . "<br/>";
}

Example 12 : between()


SELECT username FROM users WHERE id between 1 and 8;
$users = db::factory()
 ->SELECT("username")
 ->from("users")
 ->where('id' => 'BETWEEN ? and ?')
->run('1', '8');

the result might be a multiple result set of guys named john or kamau

foreach ($users as $user) {
echo $user['username'] . "<br/>";
}

====

There are other methods you can use to generate db agnostic queries. Look at them in db.php, chances are they will cover almost every querying scenario that will arise in day to day use. If all these fail, you can always fall back to good old PDO::query($query).

$allusers = db_conn::instance() →query(“SELECT * FROM users”) ;

while ($allusers→fetch() as $user) {
echo $user['username'] . "<br/>";
}

dao.php – the orm


to SELECT the username of a user with an id = 1;

 

Example 1 : SELECT username from users where id = 1


$id = 1; $user = new DAO('users', $id);echo $user→username;

Example 2 : SELECT * from users


$user = new DAO('users', “*”);
foreach ($users as $user) {
echo $user->username;
}

Example 3 : UPDATE users SET email = ‘john.doe@google.com’ WHERE id = 1;


$user = new DAO('users', 1);
$user→save();

 

Example 4 : INSERT INTO users (username, password, email) VALUES ('johndoe', md5('secret'), 'john@doe.com');

$user = new DAO('users'); //we instantiate DAO without an $id
$user→username = 'johndoe';
$user→password = md5('secret)';
$user→email = 'john@doe.com';
$user→save();

Example 5 : an array of arguments as the $id param


we can instantiate DAO with an array $id. the array is converted into 'and' arguments in a 'select .. where' clause

$user = new DAO('users', array('username' ⇒ 'johndoe'));
$user = new DAO('users', array('id' ⇒ 1));

echo $user→email ;

 

it is also possible to travel “up” the relational hierarchy and retrieve related information stored in other tables ..

echo $user→person→firstname . ' ' $user→person→lastname ;

and even

$user→person→organisation→name

Notice that an instance of a record stored in the ‘users’ table became “user” (singular), ‘persons’ became “person” and ‘organisations’ became ‘organisation’

Example 6 : one to many relations


From our db design above, an ‘organisation’ can have many ‘persons’. Thus :

$org = new DAO('organisations', $id);
//list all the persons in the organisations :

foreach ($org→persons as $person) {
echo $person->firstname;
}

on the other hand it is is possible to go back up this way :

$person→organisation→name

This perfectly mirrors the one-to-many relation existent between ‘organisations’

and ‘persons’ ie an $organisation has many $persons, whereas a $person belongs to an $organisation. This is an active record pattern.

Example 7 : a db object as an argument


we can instantiate DAO with a db object as an argument.below, we are selecting all ‘persons’ who do not have corresponding accounts/entries in the users table :

$persons_sql = db::factory()
  1. >select('*')
  2. >from('persons')
  3. >and_not_in('id',
db::factory()
  1. >select('person_id')
  2. >from('users')
)
			;
$persons = new DAO('persons', $persons_sql);
foreach ($persons as $person) {
echo "{$person->firstname $person->lastname} <br/>";
}

Example 8 : limit() the result set


the limit() method (also available in the db object) comes in handy when paginating.below, we are selecting all 'persons' who do not have corresponding accounts/entries in the users table :
$persons_sql = db::factory()
  1. >select('*')
  2. >from('persons')
  3. >and_not_in('id',
db::factory()
  1. >select('person_id')
  2. >from('users')
)
			;
$persons = new DAO('persons', $persons_sql);
$limit = 10; $offset = 2;
$persons→limit($limit, $offset);
foreach ($persons as $person) {
echo "{$person->firstname $person->lastname} <br/>";
}

Creating models which extend DAO


At the very least :class User extends DAO
{
	public function __construct($id = null)
	{
		parent::__construct("users", $id);
	}
}

With this, you can then do

$user = new User($id); /*$id can be “*”, an interger or an array*/

You can do the much buzzwordied “model validation” :

class User extends DAO
{
	public function __construct($id = null)
	{
		parent::__construct("users", $id);
	}    

	public function validate($input)
	{
		$this->input = $input;

		$this->validator = new Validate($this->input);

		$this->validator
		->pre_filter('username, password', array('valid::trim'))
		->post_filter('password', array('valid::hash_password'))
		->add_rule('username',
				 array('valid::required' => "Please fill in your username.",
					   'valid::length 6-128' => "Username should be between 6-128 characters long.",
					   'valid::unique users username' => "The username <b>{$this->input['username']}</b> is already taken."
					   ))
		->add_rule('password',
				array('valid::required' => "Please fill in a password.",
					  'valid::length 6-128' => "Password should be between 6-128 characters long.",
					  "valid::matches {$this->input['confirm_pass']}" => "Your passwords do not match.",
					  ))
		->add_rule('person_id',
				array('valid::required' => "Please select a person for this account.",
					  'valid::unique users person_id' => "That person already has an account"
					  ))
		;

		if ($this->validator->validate()) {

			$this->input = $this->validator->input ;

			// populate the obvious fields
			foreach ($this->fields as $field){
				if(isset($this->input[$field])) {
					$this->$field  = $this->input[$field];
				}
			}
			return true;

		} else {

			$this->errors = $this->validator->errors;
			return false;
		}
	}
}

The above user model can validate posted data in a $_POST array and returns a boolean depending on the result of the validation. For example :

Somewhere in your controllers, handling posted data ..

public function save_user()
{
	$user = new User();
	if ($user->validate($_POST)) {
		$user->save();

		Msg::instance()->info[] = "The user has been succesfully added";
	} else {
		Msg::instance()->info[] = "The user could not be added";

	}

}

The Validate class


The validate class (library) is used to validate submitted data in models using user defined static methods which reside in the Valid class.We instantiate while passing in the posted data into the constructor:

$validator = new Validate($_POST);

We trim fields :

$validator →pre_filter('username, password', array('valid::trim'))

We add a rule to validate the username and password field:

$validator →add_rule('username, password',
	 array('valid::required' => "This field is required."))

add_rule takes the arguments : 1. a string of the $_POST field or several fields to be validated by this rule 2. an array where

a. the key is the rule, with space separated arguments in order, from

the first of the valid::function() arguments to the second last. the last
  argument is reserved for the value of the submitted $_POST field.

  b. the value of the array is the error message to be shown if the
  field validation fails

We add more rules for the username field :

→add_rule('username, password',
	 array(
		   'valid::length 6-128' => "Username should be between 6-128 characters long.",
		   'valid::unique users username' => "The username <b>{$this->input['username']}</b> is already taken."
		   ))
We can then run the $validator→validate() method. It will return a boolean on success or failure. If there are errors, they will be found on the array property $validator→errors.
If there are no errors, $validator→[field] will have the posted data ready for saving.

In a model, we typically colleft all these fields back into instanctiated model as properties and call the save() method of the model. ie

$this→input = $validator→input ;
foreach ($this->fields as $field){
	if(isset($this->input[$field])) {
		$this->$field  = $this->input[$field];
	}
}
save() , will then be called from a controller.

Have a look at the methods in the valid class [valid.php] and try to write your 0wn validation rules.

That is basically it. The 3 classes are pretty hackable, and you can modify or extend them to suit your 0wn programming needs.

Happy hacking :-)