Generic Database Table Model for CodeIgniter 2.x

For a while I had been wanting to author a generic database table model for CodeIgniter. Here are the results of those efforts…

The model:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
 * Generic Database Table Model for CodeIgniter 2.x
 *
 * @author        Robert Mullaney
 * @link        http://www.robertmullaney.com/2012/07/12/generic-database-table-model-codeigniter-2
 */
class Generic_model extends CI_Model {

    var $table_name = '';  //required
    var $field_prefix = '';
    var $primary_key = ''; //required
    var $select_fields = '*';
    var $num_rows = 0;
    var $insert_id = 0;
    var $affected_rows = 0;
    var $last_query = '';
    var $error_msg = array();

    public function initialize($params = array())
    {
        if (count($params) > 0) {
            foreach ($params as $key => $val) {
                if (isset($this->$key)) {
                    $this->$key = $val;
                }
            }
        }
        if ($this->table_name == '') {
            $this->_set_error('table_name_required');
        }
        if ($this->primary_key == '') {
            $this->_set_error('primary_key_required');
        }
        if (count($this->error_msg) > 0) {
            return FALSE;
        }
    }
    
    public function get($limit = NULL, $offset = NULL, $sort = NULL, $search = NULL)
    {
        if ($limit !== NULL) $limit = (int) $limit;
        if ($offset !== NULL) $offset = (int) $offset;
        if (is_array($sort)) {
            foreach ($sort as $field => $order) {
                $this->db->order_by($this->field_prefix . $field, $order);
            }
        }
        if (is_array($search)) {
            foreach ($search as $field => $match) {
                $this->db->like($this->field_prefix . $field, $match);
            }
        }
        $this->db->select($this->select_fields);
        $query = $this->db->get($this->table_name, $limit, $offset);
        $this->last_query = $this->db->last_query();
        $this->num_rows = $this->_num_rows($search);
        return ($limit == 1) ? $query->row() : $query->result();
    }

    public function insert($data = array())
    {
        if (is_array($data)) {
            $this->db->insert($this->table_name, $data);
            $this->insert_id = $this->db->insert_id();
            $this->_optimize();
        }
    }
    
    public function update($id = 0, $data = array())
    {
        $id = (int) $id;
        if ($id && is_array($data)) {
            $this->db->where($this->primary_key, $id);
            $this->db->update($this->table_name, $data); 
            $this->_optimize();
        }
    }
    
    public function delete($id = 0)
    {
        $id = (int) $id;
        if ($id) {
            $this->db->where($this->primary_key, $id);
            $this->db->delete($this->table_name); 
            $this->_optimize();
        }
    }
    
    private function _num_rows($search = NULL)
    {
        if ($search !== NULL) {
            foreach ($search as $field => $match) {
                $this->db->like($this->field_prefix . $field, $match);
            }
            return $this->db->count_all_results($this->table_name);
        }
        return $this->db->count_all($this->table_name);
    }

    private function _optimize()
    {
        $this->last_query = $this->db->last_query();
        $this->affected_rows = $this->db->affected_rows();
        $this->load->dbutil();
        $this->dbutil->optimize_table($this->table_name);
    }

    private function _set_error($msg)
    {
        if (is_array($msg)) {
            foreach ($msg as $val) {
                $this->error_msg[] = $val;
                log_message('error', $val);
            }
        } else {
            $this->error_msg[] = $msg;
            log_message('error', $msg);
        }
    }

    public function display_errors($open = '<p>', $close = '</p>')    {
        $str = '';
        foreach ($this->error_msg as $val) {
            $str .= $open . $val . $close;
        }
        return $str;
    }

}

/* End of file generic_model.php */
/* Location: ./application/models/generic_model.php */

Basic select usage:

$this->load->model('generic_model');
$this->generic_model->initialize(array(
    'table_name'    => 'users',
    'field_prefix'    => 'user_',
    'primary_key'    => 'user_id',
    'select_fields'    => 'user_id, user_email, user_password'
));
$user = $this->generic_model->get(1, NULL, NULL, array(
    'username'    => $this->input->post('user')),
    'password'    => $this->input->post('pass'))
);

The other functions within the model should be easily understandable. Feel free to comment if something doesn’t make sense to you. 🙂

5 thoughts on “Generic Database Table Model for CodeIgniter 2.x”

  1. Just a note, I updated the code to include recent changes that streamline the model even further. Even if you don’t use this model as-is, I think it’s a great starting point for your own variation.

  2. Hi, I was developing my own generic model, and ended up using yours because it was far more complete. Thanks by the way!!

    But, there’s an unexpected behaviour when using transactions: on the update, insert, delete you do a call to: $this->_optimize();

    As you can see on this page:
    http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html

    that statement cause an implicit commit, In my case, a part of my rollbacked transaction was in fact getting commited to the database, it was driving me nuts.

    After commenting that line, the problem dissapeared, I just wanted to let you know. Thanks again.

  3. Hi , Can you help me man?
    So, I’m used your generic model in my project, but I’m having trouble with an insert, you could show how to do the operation on the controller? whenever I try I get an error related to the SET, print the variable $ data in the model, and array that goes to your model is empty,

    Thank’s for all

    1. Been quite a while since I messed with this and many improvements could obviously be made, but here is how I was doing inserts with it when authored…

      $this->load->model('generic_model');
      $this->generic_model->insert(array(
      'column1' => 'value1',
      'column2' => 'value2'
      ));

      Then check $this->generic_model->insert_id to see if it succeeded (eg: room for improvement, feel free to make it your own; the insert method could automatically return insert_id)

Leave a Reply

Your email address will not be published. Required fields are marked *