Inserting Database Records with a CodeIgniter PHP Framework Model

t’s not surprising that the CodeIgniter PHP framework has a large community of users. It comes packaged with a number of valuable features that make it appealing to many web developers: excellent online documentation (quite possibly its strongest point) and a flat learning curve, a solid set of classes and helpers, and finally, it allows developers to create web applications by using the Model-View-Controller pattern.

However, there’s one feature lacking in CodeIgniter that’s a detriment to its unquestionable quality. At the time of writing this article, it doesn’t provide a native ORM model. Of course, this issue can be easily fixed by picking up well-trusted third-party libraries like Simon Stensi’s DataMapper or IgniterRecord, since both of them come equipped with enough functionality to handle not only basic CRUD operations, but the most common types of relations between database tables (one-to-one, one-to-many, many-to-many, etc.).

Even so, it’s also possible to develop a customized generic model for CI that permits you to quickly build PHP applications in a truly painless fashion. That’s tje goal of this series of articles. So, if you’re interested in learning how to accomplish this in a few simple steps, then don’t hesitate anymore; begin reading now!

Naturally, if you already went through the previous articles, it’s probable that at this time you have an approximate idea of how to create a generic model for CodeIgniter. And I’m saying this simply because in the earlier tutorials, not only did I create the bare bones structure of this model, but I provided it with some basic functionality, including the ability for fetching records from a specified database table according to certain conditions.

Nonetheless, this generic model needs to be enhanced even more. Thus, with that premise in mind, in this fourth chapter of the series, I’m going to add another method to it, which will be responsible for inserting new records into the associated database table, thus extending its current functionality.

So, are you ready to learn how this whole new method for the generic model of CodeIgniter will be coded? Then let’s get started!

It’s possible that you want to know how to implement a method within the generic model class shown in the previous article of this series that allows to insert new rows into the specified database table. But before I show you the signature of the method, I’d first like to list the complete source code of the model in its current state, so you can recall in a snap how it looked initially.

That being said, here’s the complete signature of the aforementioned model class:

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;

}

}

As you’ll probably recall, the above “AbstractModel” class includes enough functionality for setting the data and the validation rules that will be used by it when performing CRUD operations. Most importantly, however, it implements a core method called “fetch(),” which fetches rows from the specified database table in accordance with certain conditions.

From the signature of this method, it’s clear to see that it acts as a simple wrapper for many of the methods of the native Active Record class provided by CodeIgniter, so again I recommend that you read the corresponding user guide if you’re not very familiar with it yet.

Well, at this stage you hopefully recalled how the “AbstractModel” class looks now, so it’s time to continue enhancing its current functionality. As I expressed in the introduction, it’s necessary to give this class the ability to insert new rows into the associated database class, so in the next section I’m going to code another core method that will be charged with performing this crucial task.

As usual, to see how this method will be defined, click on the link that appears below and keep reading.

Definitely, one of the principal features that any decent model class must have is the capability to insert and update rows in a specified database table. With that in mind, in the next few lines I’m going to add another method to the “AbstractModel” class, which will be tasked with performing insertions and updates against its associated table.

Here’s how this brand new method looks:

/** 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->affected_rows();

}

}

As you can see, the logic implemented by the previous “save()” method is fairly easy to follow. In this case, the method will insert a new record into the specified database only if the $this->id property has been previously set. Otherwise, it’ll perform an update operation using the aforementioned property.

Finally, it’s valid to mention that if no data is supplied for inserting or updating a record in the database, then the corresponding error message will be stored in the $this->errors array and the method will return a FALSE value. Not too difficult to grasp, right?

So far, so good. Now that you hopefully understood how the previous “save()” method does its thing, it’s time to see how the generic model looks after incorporating this new method. So, in the last section of this tutorial I’m going to list for you the complete source code of the model, so can see more clearly how it is now structured.

Please click on the link below and read the following segment.

As I stated in the previous segment, in the next few lines I list the complete source code of the “AbstractModel” class, this time including the “save()” method that you learned before. Take a look at it, please:

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;

}

}

}

Definitely, the above generic model class is starting to look a bit more interesting. It’s now capable of fetching rows from the specified database table, in addition to inserting new records and updating existing ones via the “save()” method. This means that almost all of the basic CRUD operations can be performed by the class in a straightforward manner, except for deletions.

Yes, it’s necessary to implement another method that allows you to delete rows from the model’s associated table, but guess what? This topic will be covered in the next tutorial, so in the meantime have fun tweaking the code in its current state.