Simple PHP Class-Based Querying

Though it is usually advisable to use some sort of framework or CMS, sometimes a project is small enough such that those options would weigh down the development. However, even in smaller projects, separating presentational elements from backend querying should not be ignored. This tutorial will walk you through creating a basic class-based querying engine for PHP and MySQL.

Step 1. Setup the Project

The first thing we are going to want to do is make some specific files and directories. Here’s how I like to setup my projects. You can, of course, feel free to change the names and structure to your liking. Just make sure you change the require’s later on as well.

Make directories

We’ll need a new directory to hold everything. In this case, I called it tut. Inside of that, I put my configuration files in a directory called conf. Then, I will make an inc directory (short for include) and put a “class” directory inside of that.

Add Files

Then, inside /conf, we will make config.php. Inside /inc/class we will make DAL.php. Finally, in the root directory, we will make index.php.

DAL stands for “Data Access Layer” or “Data Access Link”.

In multi-tiered architecture, it is essentially used to translate database query results into objects and vice-versa.

Step 2. Setup the Database

We need to make a database and populate it with some data. For the purposes of this tutorial, it will just be a two-table database with a single one-to-many relationship. This is just so we can show our querying engine spanning at least one relationship.

Create tables

So, in a database named “tut”, let’s make a table called makes and a table called models. The makes table will have fields “id” and “name” and the models table will have fields “id”,”make”, and “name”.

Add some data

Now we can just add some makes (like Ford, Chevy, etc.) as data in the makes table and some models that those manufacturers are responsible for.

This tutorial assumes you have some working knowledge of databases and SQL, so I won’t go into details about the relation/foreign key setup.

Step 3. The Database Connection

Usually, I don’t like working with raw constants in PHP. I will typically define a bunch of things then make some functions to hook into those constants. For this example, let’s just keep things simple and use the constants.

Define connection variables

In our /conf/config.php file, let’s setup our database connection variables. While we are at it, let’s throw an include to our DAL.php script.

<?php

// Include DAL
require_once(dirname(dirname(__FILE__)) . '/inc/class/DAL.php');

// Database
define ( 'DB_HOST', 'localhost' );
define ( 'DB_USER', 'root' );
define ( 'DB_PASSWORD', 'password1' );
define ( 'DB_DB', 'tut' );

?>

This setup assumes you are running MySQL on it’s default port.

Create connection function

Now, inside /inc/class/DAL.php, we will make a function that we will use to connect to our database.

The connection, as well as all forthcoming queries, will live inside a class named DAL. Wrapping all database involvement inside a single class allows us to manipulate our queries later without needing to touch business or presentation layer scripts. Also, it provides some degree of mock namespacing.

In the case of this class, we will add a constructor even though it doesn’t need to do anything.

<?php 

class DAL {
	
  public function __construct(){}
  
  private function dbconnect() {
    $conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)
      or die ("
Could not connect to MySQL server");
		
    mysql_select_db(DB_DB,$conn)
      or die ("
Could not select the indicated database");
	
    return $conn;
  }
  
}

?>

Notice that the scope of the dbconnect method is private. This is because we should not need to connect to the database from outside our DAL. Instead, we will have public query methods which will call the dbconnect from inside the DAL. A little confusing? No worries, read on.

Step 4. Create Generic Query Tools

To abstract our queries so that we can reuse short pieces of code, we will need two things. First, we will need some sort of “generic query result” class. Second, we will need a generic querying method inside our DAL.

Create generic query result class

The purpose of all of this is to be able to convert SQL queries into objects and minimize use of the ugly while($row = mysql_fetch_array($result)) loop. Objects are far easier to work with and allow us to use properties instead of array keys.

In short, we want to make a class that will create property names on the fly and store data associated with those properties.

We will put this class inside our /inc/class/DAL.php script. Since it is a new class, it will be outside the DAL class.

class DALQueryResult {

  private $_results = array();

  public function __construct(){}

  public function __set($var,$val){
    $this->_results[$var] = $val;
  }

  public function __get($var){	
    if (isset($this->_results[$var])){
      return $this->_results[$var];
    }
    else{
      return null;
    }
  }
}

Create generic query method

Now, inside our DAL class, we need to make a generic querying method that will turn SELECT queries into DALQueryResult objects.

Basically, we want to turn each returned field name into a property of the DALQueryResult object.

private function query($sql){

  $this->dbconnect();

  $res = mysql_query($sql);

  if ($res){
    if (strpos($sql,'SELECT') === false){
      return true;
    }
  }
  else{
    if (strpos($sql,'SELECT') === false){
      return false;
    }
	else{
	  return null;
	}
  }

  $results = array();

  while ($row = mysql_fetch_array($res)){

    $result = new DALQueryResult();

    foreach ($row as $k=>$v){
      $result->$k = $v;
    }

    $results[] = $result;
  }
  return $results;		
}

Here is a private function that accepts a SQL query. It connects to the database and runs the query. Then, it checks to see if there are any results. If there are not any results, it returns null on a SELECT query, false on other queries. If the query was successful and the query was not a SELECT query, it will return true. If it was a SELECT, then it converts the results into an array of DALQueryResult objects. This mimics the results that one would normally get from a mysql_query.

Step 5. Write a Specific Query

Now we are ready to actually write a SQL query. DAL queries should be very specific both in name and purpose. Let’s make one that finds all models of a given make.

This will be our first public method.

public function get_models_by_make_name($name){
  $sql = "SELECT models.id as id, models.name as name, makes.name as make FROM models INNER JOIN makes ON models.make=makes.id WHERE makes.name='$name'";
  return $this->query($sql);
}

Here we are just writing the query and returning the result in the form of DALQueryResult objects. Our generic query method takes care of the itterations and decision making.

Finished DAL

At this point, our DAL.php script is finished. It should look like the following.

<?php 

class DALQueryResult {
	
  private $_results = array();

  public function __construct(){}

  public function __set($var,$val){
    $this->_results[$var] = $val;
  }

  public function __get($var){	
    if (isset($this->_results[$var])){
	  return $this->_results[$var];
	}
    else{
	  return null;
	}
  }
}

class DAL {

  public function __construct(){}
  
  public function get_models_by_make_name($name){
    $sql = "SELECT models.id as id, models.name as name, makes.name as make FROM models INNER JOIN makes ON models.make=makes.id WHERE makes.name='$name'";
    return $this->query($sql);
  }
  
  private function dbconnect() {
    $conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)
    	or die ("<br/>Could not connect to MySQL server");
		
    mysql_select_db(DB_DB,$conn)
    	or die ("<br/>Could not select the indicated database");
	
	return $conn;
  }
  
  private function query($sql){

    $this->dbconnect();

    $res = mysql_query($sql);

    if ($res){
      if (strpos($sql,'SELECT') === false){
        return true;
      }
    }
    else{
      if (strpos($sql,'SELECT') === false){
        return false;
      }
      else{
        return null;
      }
    }

    $results = array();

    while ($row = mysql_fetch_array($res)){

      $result = new DALQueryResult();

      foreach ($row as $k=>$v){
        $result->$k = $v;
      }

      $results[] = $result;
    }
    return $results;		
  }  
}

?>

Step 6. Use the DAL

Now, let’s finally head over to our /index.php script and display our results using the DAL. All we need to do is include our /conf/config.php file, instantiate the DAL, and do something with the data. Here’s an example.

<?php 
// include configuration
require_once(dirname(__FILE__) . '/conf/config.php');

// instanciate a new DAL
$dal = new DAL();

// array of makes to check
$makes = array('Ford','Chevy','Honda');

// cycle through the makes
foreach ($makes as $make){
  $results = $dal->get_models_by_make_name($make);
  echo "<h1>Models by $make</h1>";
  
  // check if there were any results
  if ($results){
  	echo "<ul>";
  	
	// cycle through results
	foreach ($results as $model){
  		echo "<li>$model->make $model->name (Database ID: $model->id)</li>";
  	}
  	echo "</ul>";
  }
  else{
    // Display a message concerning lack of data
  	echo "<p>Sorry, we have no information regarding that manufacturer.</p>";
  }
}
?>

As you can see, we now have results that we can call the field names as properties of a PHP object.

Step 7. Taking Things one Step Further

Often, it will be useful to convert the generic DALQueryResult into a more specific object. In this case, you can write business objects that accept a DALQueryResult as a constructor parameter. Then, you just use that to build the new object.

Here’s an example

<?php

class CarModel{
  private $_id;
  private $_make;
  private $_name;
  
  public function __construct(DALQueryResult $result){
    $this->_id = $result->id;
    $this->_make = $result->make;
    $this->_name = $result->name;
  }
  public function __get($var){
    switch ($var){
	  case 'id':
	    return $this->_id;
		break;
	  case 'make':
	    return $this->_make;
		break;
	  case 'name':
	    return $this->_name;
		break;
	  default:
	    return null;
	    break;
	}
  }
  public function __toString(){
    return $this->_name;
  }
}

?>

Then, just write a query to return an array of these objects instead of an array of generic DALQueryResult objects.

Remember, always name your queries very specifically.

public function get_models_by_make_name_as_CarModel($name){
  // Reuse existing query
  $results = $this->get_models_by_make_name($sql);
  
  // check for results
  if (!$results){
    return $results;
  }
  else{
    // array to hold CarModel objects
    $object_results = array();
    // cycle through and convert to CarModel objects
    foreach ($results as $result){
      object_results[] = new CarModel($result);
    }
    // return array of CarModel objects
    return object_results;
  }
}

Building specific objects can become very useful when calculations are needed to extract meaningful data from fields.

Hope you all enjoyed the tutorial! Good luck.