Home arrow PHP arrow Page 3 - Using the Active Record Pattern with PHP and MySQL

Building a basic MySQL abstraction class - PHP

Using the active record pattern to develop a database-driven application offers many advantages. It can save PHP developers a wealth of time in coding and code maintenance, because you can take advantage of SQL abstraction. This first part of a series will walk you through the basics of using the active record pattern.

TABLE OF CONTENTS:
  1. Using the Active Record Pattern with PHP and MySQL
  2. Building a simple data mapper in PHP
  3. Building a basic MySQL abstraction class
  4. Inserting database table rows with the active record pattern
By: Alejandro Gervasio
Rating: starstarstarstarstar / 13
March 03, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: