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.