Minimalist PDO ActiveRecord Implementation

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Minimalist PDO ActiveRecord Implementation

Post by volomike »

I've written a minimalist PDO ActiveRecord implementation. If you think I've made a huge blunder here, or have constructive criticism, please share. Thanks.

Usage:

It's fairly straight forward but with a few minor catches. The class creates a $DB object that is basically a PDO object, but also includes a getRecord method that outputs an ActiveRecord. The ActiveRecord has properties for your table columns, and then the class methods .save(), .insert(), and .update(). It also supports the public property theRecord which returns an array of values that have been set upon it. Note that when calling getRecord() you can either call it without parameters, which is useful for an UPDATE statement, or with a table parameter only, which is useful for an INSERT statement, or call with both a table and a where clause parameter, which is useful for an UPDATE or SAVE statement under certain conditions. Calling with a table means that the record object will only have the column name parameters. Calling with a table and a where clause means that the record will have column names + actual row values from the table. Calling with neither means it's an empty object, so no metadata about your table.

A demo:

$oRec = $DB->getRecord();
$oRec->ID = 4;
$oRec->FirstName = 'Volo';
$oRec->LastName = 'Mike';
$oRec->update();

And here's the other catch -- note the private methods that translate names. Thus, in the database I might have column names: id, fkey_user_id, dt_modified, first_name, and last_name, but the object property names become ID, FKUserID, DateModified, FirstName, and LastName. If you read those functions, you kind of get the hang of it and you'll note that I don't deviate that much from the norm on column names in the database.

Code: Select all

<?php
 
class ActiveRecord {
 
    public $theRecord = array();
    private $_Table = '';
    private $_Where = '';
    private $_hDB;
 
    public function __construct(&$oDB, $sTable = '', $sWhere = '') {
        if ((!empty($sTable)) and (!empty($sWhere))) {
            $rsRows = $oDB->query("SELECT * FROM $sTable WHERE $sWhere LIMIT 1");
            foreach ($rsRows as $rwRow) {
                foreach ($rwRow as $sKey => $sVal) {
                    if (!is_numeric($sKey)) {
                        $sKey = $this->_getObjectKeyName($sKey);
                        $this->theRecord[$sKey] = $sVal;
                    }
                }
                break;
            }
        } elseif (!empty($sTable)) {
            $rsRows = $oDB->query("SELECT * FROM $sTable LIMIT 1");
            foreach ($rsRows as $rwRow) {
                foreach ($rwRow as $sKey => $sVal) {
                    if (!is_numeric($sKey)) {
                        $sKey = $this->_getObjectKeyName($sKey);
                        $this->theRecord[$sKey] = '';
                    }
                }
                break;
            }
        }
        $this->_Table = $sTable;
        $this->_Where = $sWhere;
        $this->_hDB = $oDB;
    }
    
    private function _getObjectKeyName(&$sKey) {
        $sKey = strtolower($sKey);
        $sKey = str_replace('fkey_','fk_',$sKey);
        $sKey = str_replace('dt_','Date_',$sKey);
        $sKey = str_replace('_',' ',$sKey);
        $sKey = ucwords($sKey);
        $sKey = str_replace('Ip ','IP ',$sKey);
        $sKey = str_replace(' Ip',' IP',$sKey);
        $sKey = str_replace('Url ','URL ',$sKey);
        $sKey = str_replace(' Url',' URL',$sKey);   
        $sKey = str_replace('name','Name',$sKey); //not a bug -- I conjoin name usually
        $sKey = str_replace(' Id',' ID',$sKey);
        $sKey = str_replace('Id ','ID ',$sKey);
        $sKey = str_replace(' ','',$sKey);
        $sKey = ($sKey == 'Id') ? 'ID' : $sKey;
        $sKey = ($sKey == 'Ip') ? 'IP' : $sKey; 
        $sKey = ($sKey == 'Url') ? 'URL' : $sKey;
        $sKey = str_replace('Fk','FK',$sKey);
        $sKey = ($sKey == 'UserName') ? 'Username' : $sKey;
        return $sKey;
    }
 
    private function _getDBKeyName(&$sKey) {
        foreach (range('A','Z') as $c) {
            $sKey = str_replace($c, "_$c",$sKey);
        }
        $sKey = str_replace('_F_K_','_fkey_',$sKey);
        $sKey = str_replace('_I_D_','_id_',$sKey);
        $sKey = str_replace('_I_D','_id',$sKey);
        $sKey = str_replace('_I_P_','_ip_',$sKey);
        $sKey = str_replace('_I_P','_ip',$sKey);
        $sKey = str_replace('_U_R_L_','_url_',$sKey);
        $sKey = str_replace('_U_R_L','_url',$sKey); 
        $sKey = str_replace('_Name','name',$sKey); //not a bug -- I separate name usually
        $sKey = str_replace('_Date_','_dt_',$sKey);
        $sKey = str_replace('_Date','_Date',$sKey);
        if (strpos($sKey, '_') == 0) {
            $sKey = substr($sKey, 1);
        }
        $sLast = substr($sKey, -1);
        if ($sLast == '_') {
            $sKey = strrev($sKey);
            if (strpos($sKey, '_') == 0) {
                $sKey = substr($sKey, 1);
            }
            $sKey = strrev($sKey);
        }
        $sKey = strtolower($sKey);
        return $sKey;
    }
    
    
    public function save() {
        $sTable = $this->_Table;
        $sWhere = $this->_Where;
        if (empty($sWhere)) {
            $nCount = 0;
        } else {
            $sSQL = "SELECT COUNT(*) AS counted FROM $sTable WHERE $sWhere;";
            $rsRows = $this->_hDB->query($sSQL);
            foreach ($rsRows as $rwRow) {
                $nCount = $rwRow['counted'];
                break;
            }
        }
        if ($nCount > 0) {
            return $this->update();
        } else {
            return $this->insert();
        }
    }
    
    public function update() {
        $sTable = $this->_Table;
        $sWhere = $this->_Where;
        if ((empty($sTable)) or (empty($sWhere))) {
            throw new Exception('Missing table name or WHERE clause in UPDATE SQL.');
        }
        $s = '';
        foreach ($this->theRecord as $sKey => $sVal) {
            $sKey = $this->_getDBKeyName($sKey);
            $s .= $sKey . '=:' . $sKey . ',';
        }
        $s = substr($s, 0, -1);
        $sSQL = "
        UPDATE
            $sTable
        SET
            $s
        WHERE
            $sWhere;
        ";
        $st = $this->_hDB->prepare($sSQL);
        foreach ($this->theRecord as $sKey => $sVal) {
            $sKey = $this->_getDBKeyName($sKey);
            $st->bindValue(':' . $sKey, $sVal);
        }
        return $st->execute();
    }
 
    public function insert() {
        $sTable = $this->_Table;
        if (empty($sTable)) {
            throw new Exception('Missing table name in INSERT SQL.');
        }
        $s1 = ''; $s2 = '';
        foreach ($this->theRecord as $sKey => $sVal) {
            $sKey = $this->_getDBKeyName($sKey);
            $s1 .= '' . $sKey . ',';
            $s2 .= ':' . $sKey . ',';
        }
        $s1 = substr($s1, 0, -1);
        $s2 = substr($s2, 0, -1);
        $sSQL = "
        INSERT INTO $sTable (
            $s1
        ) VALUES (
            $s2
        );
        ";
        $st = $this->_hDB->prepare($sSQL);
        foreach ($this->theRecord as $sKey => $sVal) {
            $sKey = $this->_getDBKeyName($sKey);
            $st->bindValue(':' . $sKey, $sVal);
        }
        return $st->execute();      
    }
    
    public function __set($var, $val){
        $this->theRecord[$var] = $val;
    }
 
    public function __get($var){
        if(isset($this->theRecord[$var])){
            return $this->theRecord[$var];
        } else {
            throw new Exception("Property '$var' does not exist");
        }
    }   
    
} //end ActiveRecord class
 
class DBClass extends PDO {
 
    public function getRecord($sTable = '', $sWhere = '') {
        $oRecord = new ActiveRecord($this, $sTable, $sWhere);
        return $oRecord;
    }
 
} //end DBClass
 
global $Settings;
global $DB;
 
// THIS INSTANTIATES THE CLASS SIMPLY BY INCLUDING THE SCRIPT, AS WELL AS CONNECTS THE DATABASE
$DB = new DBClass ($Settings->DSN);
 
 
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Minimalist PDO ActiveRecord Implementation

Post by volomike »

Some things have occurred to me since the last post:

- needs to handle nulls
- needs to handle serial/autonumber columns
- needs to handle default columns
- does it have the error handling it needs?
- will bindValue be enough to protect me from SQL injection?
- I probably should add a theLastSQL property to return the last SQL statement that was issued after save(), update(), or insert()
- will this work the same across MySQL5, PostgreSQL8.2+, and SQLite3, or need special logic for each platform?
- boolean results can act differently sometimes across the DB platforms -- I should try to make them act consistent, but not mess up strings that actually say 't', 'f', 'true', 'false', 'TRUE', or 'FALSE'.
User avatar
volomike
Forum Regular
Posts: 633
Joined: Wed Jan 16, 2008 9:04 am
Location: Myrtle Beach, South Carolina, USA

Re: Minimalist PDO ActiveRecord Implementation

Post by volomike »

One might also ask, "Where's the .remove() or .delete()?" The reason I didn't include that is because the following code is illogical as the best opportunity to solve that need:

$oRec = $DB->getNewRecord('users','id = 4');
$oRec.delete();

Why would I do a SELECT when, if I know the table and the WHERE clause, just send an exec straight from PDO like:

// assume $DB is already established and connected to your database
$DB->exec('DELETE FROM users WHERE id = 4');

Therefore, I didn't include it.
Post Reply