As a developer, you can become a little complacent with the framework and language you’re using. You’ve invested a lot of time in getting yourself up and running so why look at other solutions to problems you’ve already solved? Well, there is more than 1 way to skin a cat, and the other methods out there may mean you can skin 10 cats for every 1 of yours. OK, I’m going to stop with this horrible analogy. This post has come about because I was inspired by the Active Record methodology involved in Ruby on Rails and I wanted to create a PHP version.
Let’s start with a few examples of active records in RoR.
user = User.find_user_by_email('myemail@gmail.com');
user.name; # Me
user.age; # Thirty (Just)
user.new(:name => "David", :surname => "Hasselhoff" : occupation => "Life Guard");
If you’re not familiar with RoR you will probably be impressed with how easy it is to create new records and generate the setters and getters. If you are familiar with RoR please excuse any syntax errors as I don’t actually write in Ruby.
My current framework is a custom setup very similar to the CodeIgniter framework. All the data access is done via 2 files, the data access object, and the access object. I have a script which is an automatic code generator that can create both of these files using the database and table names. The data access object is the parent of the access object, this allows any custom queries to be managed by the access object and direct table access to be carried out by the data access object. Therefore for each table in the database, we need both of these files, this can start to add up fairly quickly. Just to highlight this setup…
Data Access Object
<?php
class DAO_User {
// Private properties
protected $db;
protected $db_name = "my_database";
protected $table_name = "user";
// Table specific
protected $first_name;
protected $last_name;
/*
* Constructor
*/
public function __construct () {
$this->db = new Database();
}
/*
* Getters
*/
public function getFirstName(){
return $this->first_name;
}
public function getLastName(){
return $this->last_name;
}
/*
* Setters
*/
public function setFirstName($val=''){
$this->first_name = $val;
}
public function setLastName($val=''){
$this->last_name = $val;
}
/*
* Processes
*/
public function createRow () {
# Insert code
}
public function updateRow () {
# Update code
}
public function deleteRow() {
# Delete code
}
}
?>
Access Object
<?php
require_once('class.DAO.my_database.user.php');
class AO_User extends DAO_User {
/*
* Return a list of values
*/
public function findUserByName ($name="dave") {
# Query code here
}
}
?>
These classes are used by the Models for the framework to interact directly with the database. This is a very clean way of dealing with access to the database but the number of files can get quite large and the code in the model is quite clunky. Issues can also arise when the database is changed or when variables aren’t passed for certain fields (Do we set it to blank or keep the existing value for this field?). I decided to try and create a new PHP class based on the RoR active records approach.
A strange approach to some maybe, but I decided to write the code to use the class first and then tried to get the class functioning around it. Here is the code…
<?php
class data_access_test {
public function index() {
$ao = new DataAccess('my_database','user');
print $ao->find('1')->first_name(); # Expected output dave
}
}
?>
OK, so in case you are not clear on what we are saying here. We start by initialising the class with 2 variables. The first is the name of the database and the second is the table we wish to access. We then call the find method and pass a specific id of a row in the table. By chaining the methods we are also able to call the first_name method which without a variable assumes we wish to get (if we passed a variable it would assume we wish to set). OK, so now it’s time to get started on the class itself.
private $_dbl; # Your database class to actually talk to the DB
private $_database; # User defined var for the DB we need to look at
private $_table; # User defined var for the table we need to look at
private $_internal_errors = array(); # Store any errors in setting up object
private $_primary_key_fields = array(); # Primary key holder for $database.$table
private $_set_primary_key = false; # False by default, if 2 fields create key then we set to true
private $_fields; # Holder of the available fields in our table
private $_type; # As above, see desc query on your table for example of values
private $_null; # See Above
private $_key; # See Above
private $_default; # See Above
private $_extra; # See Above
private $_row_data; # Holder of all of this row's data
To start with we define all of the variables that our class will need to function correctly. Read the comments for more detail on what each variable will be used for. Now, for the nitty-gritty of the object which will happen at the point of initialisation.
/*
* Set globals to define host user and password
*/
public function __construct($database = '', $table = '') {
$this->dbl = new Database;
if ($database != '' && $table != '') {
$this->_database = $database;
$this->_table = $table;
if (!$this->setTableData()) {
$this->_internal_errors[] = 'Database and table not recognised';
}
} else {
$this->_internal_errors[] = 'Require database and table at initialisation';
}
}
/*
* General
*/
private function setTableData() {
$sql = "SHOW COLUMNS FROM " . $this->dbl->mysqlEscape($this->_database) . "." . $this->dbl->mysqlEscape($this->_table);
list(
$this->_fields,
$this->_type,
$this->_null,
$this->_key,
$this->_default,
$this->_extra
) = $this->dbl->returnRows($sql);
if ($this->_fields) {
$primary_count = 0;
// Set the primary keys
foreach ($this->_fields as $key => $value) {
if ($this->_key[$key] == 'PRI') {
$this->_primary_key_fields[] = $value;
$primary_count++;
}
}
if ($primary_count > 2 || !in_array('auto_increment', $this->_extra)) {
$this->_set_primary_key = true;
}
return 1;
}
}
The construct of the class sets up our database object so that we can talk to the database. We check that the user has passed the 2 required variables, if they haven’t we assign an error message to our internal errors variable. I find it useful to have an internal errors variable that way if there are any issues you can simply check this variable to diagnose the problem. If we have both the database and table we can go about setting up our object, this is all done in the setTableData method.
This method begins by doing a DESC query on the table. We use the return variables to set our fields, type, null, key, default, and extra properties. The final thing this method does is check the primary keys. I had a number of problems with my original DAO files when I tried to generate the files for a table with multiple primary keys. By setting our primary_key_fields variable we can make a decision as to whether to allow the user to set and get this variable. The decision I’ve made is you cannot set the primary field if there is only 1 primary key. If there are more primary keys then I set my set_primary_key flag to true to indicate that we can allow this field setting.
So how do we set and get variables on the fly when we don’t have the methods already set? Well, we can do this by using the __call method.
/*
* Use name to check what field we are setting or getting
*/
public function __call($name = '', $arguments = '') {
// Only proceed if there are no internal errors
if (count($this->_internal_errors) == 0) {
// Check that this is a valid field
if (in_array($name, $this->_fields)) {
// We have an argument so set, otherwise get
if (!empty($arguments[0])) {
if (!in_array($name, $this->_primary_key_fields) || $this->_set_primary_key === true) {
if ($this->checkFieldDateType($name, $arguments[0]) == 1) {
$this->setData($name, $arguments[0]);
} else {
$this->_internal_errors[] = $name . ' cannot be set as it fails the type check';
}
} else {
$this->_internal_errors[] = $name . ' cannot be set as it\'s a primary key';
}
} else {
return $this->getData($name);
}
} else {
$this->_internal_errors[] = $name . ' is not a recognised field in this table';
}
}
}
I’ll now talk you through this method and then show you the get and set methods. The first thing this method does is to check the internal_errors property, we don’t want to try and set something if we’ve had a problem setting up the object. We then check to see if an argument has been passed, if it has then we can assume the user wants to set a variable. We check whether the method name is a primary key and whether we can set it. If allowed we finally check the argument against the data type set for this field (I won’t go into detail about this method but it’s a good way to ensure data integrity by ensuring it matches the type of data acceptable for that field) and then pass to our set method. If there are no arguments then we simply send it to the get method.
private function getData($key = '') {
return $this->_row_data[$key];
}
/*
* Setters
*/
private function setData($key = '', $value = '') {
$this->_row_data[$key] = $value;
return $this;
}
Fairly straightforward forward huh? Our set method simply uses the name passed to the __call method (aka table field) as the key for our internal variable _row_data. The return $this allows us to chain methods in this object. The get method is even more straightforward and just returns the value currently assigned to this key (aka table field). Ok, so if we look back at our original code we still need to define our find method.
/*
* Find record using unique id
*/
public function find($record_id = '') {
if ($record_id != '' && count($this->_primary_key_fields) == 1) {
$sql = "SELECT *
FROM " . $this->dbl->mysqlEscape($this->_database) . "." . $this->dbl->mysqlEscape($this->_table) . "
WHERE " . $this->_primary_key_fields[0] . " = '" . $record_id . "'";
$results = $this->dbl->returnRows($sql, 'non_list');
foreach ($results[0] as $key => $value) {
if (!is_numeric($key)) {
$this->_row_data[$key] = $value;
}
}
}
return $this;
}
Our find method checks that the record id is available and that there is only 1 primary key. A query is then generated to return all of the information in that table row. The columns of the rows are used as keys in our internal _row_data variable and the value is the cell data. When we now call a get on any of the columns we will get the data we require…bingo!
My class also has methods for creating new rows and updating rows. If you’ve come this far I’m sure you can figure these out for yourself. This class is far from perfect but it’s a good start. One of the problems you may have already noted is what if we want to set an empty value? Also what if we want to return multiple rows? If you have any ideas on it please share them with me 🙂