Building a basic MySQL abstraction class - PHP

True to form, the active record pattern can be also implemented via a simple database abstraction class. In this particular situation, I’m going to demonstrate how this concept can be applied specifically to a PHP 5 class that works with MySQL.

As you’ll see in a moment, this class not only will be capable of performing a few common tasks, such as connecting to the server and selecting a specific database, but running some CRUD operations as well.

But it’s time to get rid of the boring theory and show some functional code. So here’s the signature of a rudimentary active record class:

// define 'MySQL' class

class MySQL{

private $result;

public function __construct($host='localhost',$user='user',$password='password',$database='database'){

// connect to MySQL and select database

if(!$conId=mysql_connect($host,$user,$password)){

throw new Exception('Error connecting to the server');

}

if(!mysql_select_db($database,$conId)){

throw new Exception('Error selecting database');

}

}

// run SQL query

public function query($query){

if(!$this->result=mysql_query($query)){

throw new Exception('Error performing query '.$query);

}

}

// fetch one row

public function fetchRow(){

while($row=mysql_fetch_array($this->result)){

return $row;

}

return false;

}

// fetch all rows

public function fetchAll($table='default_table'){

$this->query('SELECT * FROM '.$table);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

}

As shown before, the signature of the above “MySQL” class doesn’t differ too much from any MySQL abstraction class that you might have coded before. In this case, it implements a set of methods that come in useful for connecting to the database server, running SQL queries and fetching one row at a time from a returned result set. So far, nothing unexpected, right?

However, I’d like you to pay close attention to the method called “fetchAll().” As you can see, it executes a “SELECT * FROM” SQL statement behind the scenes to fetch all the rows from a specified MySQL table. Even so, the method only takes up the name of the table to be queried. No additional SQL clauses are coded explicitly, in this way considerably abstracting this process.

By following an approach like the one I used to code the previous “fetchAll()” method, it’s feasible to implement the active record pattern in a pretty simple manner, while obtaining an acceptable level of SQL abstraction.

To demonstrate the functionality of the previous “MySQL” class, suppose that there’s a MySQL table called “users,” which has been previously populated with the following data:

Now that there’s a database table to work with, take a look at the following example. It uses the database abstraction class to fetch all of the rows from the above MySQL table:

try{

// connect to MySQL and select a database

$db=new MySQL('host','user','password','mydatabase');

$result=$db->fetchAll('users');

foreach($result as $row){

echo $row['firstname'].' '.$row['lastname'].' '.$row['email'].'<br />';

}

/* displays the following

Alejandro Gervasio alejandro@domain.com
John Doe john@domain.com
Susan Norton susan@domain.com
Marian Wilson marian@domain.com
Mary Smith mary@domain.com
Amanda Bears amanda@domain.com
Jodie Foster jodie@domain.com
Laura Linney laura@domain.com
Alice Dern alice@domain.com
Jennifer Aniston jennifer@domain.com

*/

}

catch(Exception $e){

echo $e->getMessage();

exit();

}

In this case, the active record pattern implemented via the “fetchAll()” method permits us to retrieve all the rows contained in the previous “users” MySQL table without having to directly write a “SELECT” statement, since the method handles this process automatically.

Now that you hopefully understood how the previous “MySQL” class is capable of fetching all the rows from a selected MySQL table, it’s time to move forward and extend its functionality. Therefore, in the section to come I’ll be providing this class with the capability for inserting new records into a selected table via the active record pattern.

To see how this will be achieved, please click on the link below and read the next few lines.