Oct 28 2011

I recently blogged on Reducing Code using CodeIgniters Active Record Class. In this blog I focus on easily collecting data from your object members using get_object_vars(). After using this for a while I found that unsetting certain members prior to an insert/update became rather annoying. I’ve been knee deep in Code Igniter for the past 6 months (and CakePHP at my new job) so I decided to take what I’ve learned and create me own parent model class.

Code Igniter allows you to create your own parent Controllers and Models. This is a great way of extending core functionality into child models via inheritance. The CodeIgniter documentation covers this in Creating Core System Classes so I won’t go into that process too much. Instead I’ll focus on what I’ve done and how it speeds up programming by:

• Reducing boiler plate CRUD code.
• Handling basic data validation.
• Leveraging InnoDB to automatically join tables. (I’m saving this one for another blog)

Creating the Class, Defining Data Members, and its Constructor

I want the constructor to automatically handle defining the classes data members based on a table I pass in. I understand that querying MySQL each time a class is constructed adds overhead, but down the road I can implement some form of model caching. For the next few months I am not concerned with the minimal amount of load this will create. I’m more concerned with getting good OOP/MVC code out the door and avoilding boilerplate code (defining private variables, getters, setters etc).

To accomplish this goal the constructor will read-in columns from $table_name and store the columns in (ARRAY) $this->member. It will also assume the first column is my primary key (a convention most developers and DBAs follow). It’s also going to store some meta data on each column including: type, nullable, and default. These will be used by the validate() method that I’ll be defining later.

UPDATE Nov. 9, 2011 I altered the constructor a bit by adding a second parameter to the schema query. It now checks for TABLE_SCHEMA (the database name). I found that if I had multiple tables in different databases with the same name it was pulling results from all. I didn’t notice any bugs with this, but I’m sure it would’ve eventually done some fun things. I also added a check for the primary key so the code no longer relies on the first column returned being the primary key.

class MY_Model extends CI_Model {
 
    private $member;
    private $primary_key;
    private $table_name = '';
 
    function __construct($table_name=''){
	parent::__construct();
 
        $this->table_name = $table_name; // needs to be cleaned - but is supplied by dev not be user so its safeish
 
        $query = $this->db->query("SELECT COLUMN_NAME as `name`, DATA_TYPE as `type`, IS_NULLABLE as `nullable`, COLUMN_DEFAULT as `default`, COLUMN_KEY as `key` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '".$this->db->database."' AND TABLE_NAME = '".$this->table_name."'");
 
        foreach($query->result() as $i){
 
            $this->member[$i->name] = $i;
 
            if(strtoupper($i->key) == 'PRI'){
                $this->primary_key = $i->name;
            }
 
        }
      }

Loading in Data

This is a simple method that sets the objects data members. It, like the constructor, can be used dynamically by classes that inherit MY_Model. Once an object has been loaded its data members can be accessed by getters and setters (see below). It only requires the primary keys value as an argument.

    public function load($id){
 
        if(is_numeric($id)){
            if(empty($this->table_name)){
                throw new Exception('Missing this->table_name');
            }
 
            $query = $this->db->get_where($this->table_name,array("$this->primary_key"=>$id));
 
            foreach($query->row() as $x=>$i){
                $this->member[$x]->value = $i;
            }
        }
    }

Accessing Object Data Members

Data encapsulation is handled by 3 methods: set_member(), get_member(), and get_primary_key(). In the past I’ve normally written a get/set method for each private variable in a class and its gotten old. These methods should serve me well, though I can see some problems with automatically performing xss_cleans. I’ll probably need to accept a third parameter determining whether to strip XSS or not. There may come a time when I want to see what the parent class has stored as a member so down the road I may create a get_definition() or get_object_vars() method to expose that information. Get and set member require the columns name as the first argument. The set method requires a value as a second argument.

    public function set_member($member,$value){
        if(array_key_exists($member, $this->member)){
            $this->member[$member]->value = xss_clean($value);
        }
    }
    public function get_member($member){
        return $this->member[$member]->value;
    }
    public function get_primary_key(){
        return $this->primary_key;
    }

Data Validation

One thing I’m always having to do is really basic data validations before attempting to save data. In the constructor I read in meta data from the columns for a reason. The following method will automatically to do type checks on data as well as checking for fields that can or cannot be empty. If any fail the data validation then I can return that information to the user. This doesn’t completely replace the need for data validation, but it certainly reduces the amount of custom code. The remaining business logic can be handled by the child class or at the controller level. This validation method is not complete and will likely need some additional tweaking.

    public function validate(){
 
        $array = array('success'=>0,'errors'=>array());
 
        foreach($this->member as $column => $i){
 
            $name = ucwords(preg_replace('/_/',' ',$i->name));
 
            if($i->name != $this->primary_key){
 
                if($i->nullable == 'NO' && !preg_match('/int/i',$i->type) && $i->value == ''){
                    $array['error'][] = $name.' should have a value.';
                }
                else if(!empty($i->value)){
 
                    switch(strtolower($i->type)){
 
                        case 'tinyint':
                        case 'smallint':
                        case 'mediumint':
                        case 'int':
                        case 'bigint':
                            if( !is_numeric($i->value) ){
                                $array['error'][]  = $name.' should be a valid number.';
                            }
                            break;
                        case 'float':
                        case 'double':
                        case 'decimal':
                            if( !is_float($i->value) ){
                                $array['error'][]  = $name.' should be a valid decimal number.';
                            }
                            break;
                        case 'char':
                        case 'tinytext':
                        case 'mediumtext':
                        case 'longtext':
                        case 'text':
                        case 'enum':
                            break;
                        case 'date':
                            $r = explode('-',$i->value);
                            if( !checkdate($r[1],$r[2],$r[0]) ){
                                $array['error'][] = $name.' should be a valid date.';
                            }
                            break;
                        case 'time':
                        case 'datetime':
                        case 'year':
                            break;
                    }
                }
            }
        }
 
        if( empty($array['error']) ){
            $array['success'] = 1;
        }
 
        return $array;
    }

Inserting and Saving Data

Last is a method for inserting and updating data. It accepts one argument “insert” or “update” to determine what its doing. As you can see it automatically calls $this->validate() before processing.

        public function save($type){
 
        if(empty($this->table_name)){
            throw new Exception('Missing this->table_name');
        }
 
        $arr = $this->validate();
        if( !$arr['success'] ){
            return $arr;
        }
 
        foreach($this->member as $x=>$i){
            if($i->value == ''){
                $data[$x] = $i->default;
            }
            else{
                $data[$x] = $i->value;
            }
        }
 
        if($type == 'insert'){
 
            unset($data[$this->primary_key]);
            $this->db->insert($this->table_name,$data);
            //echo $this->db->last_query();
 
            if($this->db->insert_id()){
                return array('success'=>1,'id'=>$this->db->insert_id());
            }
        }
        else if($type == 'update'){
 
            $this->db->where($this->primary_key,$data[$this->primary_key]);
            unset($data[$this->primary_key]);
            $this->db->update($this->table_name,$data);
            //echo $this->db->last_query();
 
            return array('success'=>1,'change'=>$this->db->affected_rows());
        }
 
        return array('success'=>0,'error'=>'Error encountered saving '.ucwords(preg_replace('/[tbl|_]/',' ',$this->table_name)).' information.');
    }

Get All – Selecting with Ease

I enjoy writing my own custom queries. I think crutch code like CakePHP’s find() method lead to crap queries and bogged down system performance down the line. Sure, development is a litter faster, but at what cost? I’m making sure I don’t do too many things like this, but just about every model needs a way of selecting data for display in a table. The getAll() method allows you to pass in an existing database object including things like order_by, limit, group_by, where, etc… This definitely needs some more work and I’m not really using it yet in any of my code.

    public function getAll($dbObj=''){
 
        if(empty($this->table_name)){
            throw new Exception('Missing this->table_name');
        }
        else{
 
            if(is_object($dbObj)){
                $this->db = $dbObj;
            }
 
            return $this->db->get($this->table_name);
        }
    }

Whats Missing

There are few things I have not included in here just yet. I have not included the pieces that leverage InnoDB to determine which objects and tables are related to this model/table. One thing I like about CakePHP is the ability to define related models using hasMany and belongsTo. I’m not aware of anything CodeIgniter has that matches it. I do however have problems with how Cake implements it. I think Cakes implementation leads to degraded performance down the line and makes it difficult to determine where queries are coming from in the code (when analyzing the slow query log). I also think with the use of InnoDB the code should truly automagically handle this for us.

Chris Nizzardini

Chris Nizzardini has been developing web applications since 2006. He lives and works in beautiful Salt Lake City, Utah. If you’re interested in hiring me for contract work please visit IO Spring LLC.

  

codeigniter, frameworks, nocrud, php, php5

Unscientific Benchmarking of Type Casting, is_numeric, and regex in PHP MySQL Optimization Tip: Checking for Differences in Queries.