Completing a Model for the CodeIgniter PHP Framework - Performing generic SQL queries

As I stated in the introduction, the generic model still lacks some features that need to be implemented quickly, including the ability to perform any type of SQL query, as well as clean up the model’s status. Thus, to address these specific issues, I’m going to add to it a simple method that will allow it to execute different queries against the associated database table.

That being explained, here’s the complete signature of the generic model class,  incorporating the extra methods I just mentioned. Have a look at it:

abstract class MY_Model extends Model

{

protected $table = ''; // table associated to the model

protected $fields = array(); // fields of table associated to the model

protected $id = NULL; // value of the primary key of the table associated to the model

protected $data = array(); // model input data

protected $insertID = NULL; // insertion ID

protected $numRows = NULL; // number of rows returned by SELECTS

protected $validation = array(); // model validation rules

protected $errors = array(); // model errors

/**

* Constructor

*

* @access protected

*/

protected function __construct()

{

parent::Model();

// get CI super object as a model property

$this->ci =& get_instance();

}

/**

* Sets associated table data for the model

*

* @author Alejandro Gervasio

* @return void

* @access public

*/

public function setTableData($table = 'default')

{

if ($this->db->table_exists($table))

{

$this->table = $table;

$this->fields = $this->db->field_names($this->table);

}

}

/**

* Sets value of primary key of the associated table for the model

*

* @author Alejandro Gervasio

* @param integer

* @return void

* @access public

*/

public function setID($id)

{

$this->id = is_integer($id) AND $id > 0 ? $id : 1;

}

/**

* Gets value of primary key of the associated table for the model

*

* @author Alejandro Gervasio

* @return integer

* @access public

*/

public function getID()

{

return $this->id;

}

/** Sets input data for the model

*

* @author Alejandro Gervasio

* @param array

* @return void

* @access public

*/

public function setData($data)

{

if ( is_array($data) AND count($data) > 0)

{

foreach ($data as $key => $value)

{

if (array_search($key, $this->fields) === FALSE)

{

unset($data[$key]);

}

}

$this->data = $data;

}

}

/**

* Sets validation rules for model data

*

* @author Alejandro Gervasio

* @param array

* @return void

* @access public

*/

public function setValidation($validation)

{

if ( is_array($validation) AND count($validation) > 0)

{

foreach ($validation as $field => $rule)

{

if (array_search($field, $this->fields) === FALSE)

{

unset($validation[$key]);

}

}

$this->validation = $validation;

}

}

/**

* Returns a result set with specified fields according to given conditions.

*

* @author Alejandro Gervasio

* @return query result on success - Boolean FALSE on failure

* @access public

*/

public function fetch($fields = '*', $where = NULL, $order = 'id ASC', $limit = NULL, $offset = 0, $join = NULL)

{

if ($fields != '*')

{

$this->db->select($fields);

}

if ($this->id != NULL)

{

$this->db->where('id', $this->id);

}

elseif($where != NULL)

{

$this->db->where($where);

}

if ($order != 'id ASC')

{

$this->db->orderby($order);

}

if ($limit != NULL)

{

$this->db->limit($limit, $offset);

}

if( $join != NULL)

{

$this->db->join($join);

}

$query = $this->db->get($this->table);

$this->numRows = $query->num_rows();

if ($this->numRows > 0)

{

return ($this->numRows > 1 ) ? $query->result() : $query->row();

}

$this->errors[] ='No rows were returned by the query.';

return FALSE;

}

/** Saves model data into associated table (validation rules are applied to input data)

*

*

* @author Alejandro Gervasio

* @return integer on success - Boolean FALSE on failure

* @access public

*/

public function save()

{

if ($this->data == NULL)

{

$this->errors[] = 'Error saving row.';

return FALSE;

}

// validate input data

if( !$this->validate())

{

return FALSE;

}

// Insert new row if ID was not set in the model

if ($this->id == NULL)

{

$this->db->insert($this->table, $this->data);

$this->insertID = $this->db->insert_id();

return $this->insertID;

}

// Otherwise update existing row

else

{

$this->db->where('id', $this->id)->update($this->table, $this->data);

return $this->db->affectedRows;

}

}

/** Deletes model data from associated table (validation rules are applied to input data)

*

*

* @author Alejandro Gervasio

* @return Boolean TRUE on success - Boolean FALSE on failure

* @access public

*/

public function delete()

{

if ($this->id == NULL)

{

$this->errors[] = 'Error deleting row.';

return FALSE;

}

$this->db->where('id', $this->id)->delete($this->table);

return TRUE;

}

/**

* Validates model input data

*

* @author Alejandro Gervasio

* @return Boolean TRUE on success - FALSE on failure

* @access protected

*/

protected function validate()

{

// If no validation rules or no model were provided data set appropriate error

if (count($this->validation) === 0)

{

$this->errors[] = 'No validation rules were set for the model.';

return FALSE;

}

// Loads CI validation library

$this->ci->load->library('validation');

// Load CI language file for validation

$this->ci->lang->load('validation');

// resets error messages

$this->errors = array();

foreach ($this->validation as $field => $rules)

{

$expRules = explode('|', $rules);

// if the field is not required check next one

if (! in_array('required', $expRules, TRUE))

{

continue;

}

// Iterates through the validation rules

foreach ($expRules as $rule)

{

// Removes the parameter from the rule (when specified)

$param = FALSE;

if (preg_match("/(.*?)[(.*?)]/", $rule, $match))

{

$rule = $match[1];

$param = $match[2];

}

// Calls the validation method that corresponds to the rule

if (method_exists($this->ci->validation, $rule))

{

$result = $this->ci->validation->$rule($this->data[$field], $param);

}

else

{

// Tries to run a native PHP function if method of CI validation class doesn't exist

if (function_exists($rule))

{

$result = $rule($this->data[$field]);

}

}

// if an offending field was found store error message in error array

if ($result === FALSE)

{

$this->errors[] = sprintf($this->ci->lang->line($rule),$field);

}

}

}

return (count($this->errors)) === 0 ? TRUE : FALSE;

}

/**

* Executes a given query

*

* @author Alejandro Gervasio

* @param string

* @return result set/Boolean TRUE on success - Boolean FALSE on failure

* @access public

*/

public function query($query)

{

if (is_string($query))

{

if (FALSE === ($query = $this->db->query($query)))

{

$this->errors[] = 'Error performing query.';

}

return $query;

}

}

}

As shown above, apart from the methods that are responsible for performing CRUD database operations and validating incoming data, the model class now incorporates a new one, called “query().” As I explained previously, this method is conceived to execute any type of SQL query; it’ll return a value of FALSE if the query fails its execution, or a result set if it is successful.

Undoubtedly, the logic that drives the “query()” method is extremely simple to grasp, so it’s time to continue adding more functionality to the “AbstractModel” class. In accordance with the concepts deployed in the introduction, I'm going to code a few additional methods that will be charged with resetting the state of the model, as well as with building the corresponding error messages that will be displayed to users when it fails to perform a given task.

This topic will be covered in depth in the next section, so click on the link that appears below and read the next few lines.

Next: Completing the AbstractModel class >>
 

More Miscellaneous Code Articles
More By Alejandros Gervasio