So far we’ve looked at Controllers and Views in the MVC or Model View Controller paradigm. With just those two facets of MVC you can actually build some pretty cool, albeit simple, applications that have basic logic and features. Now we come to the Model. We save the best for last! The Model is really the workhorse in your MVC website. Most people will try to keep their controllers and views skinny while the model becomes fat. It does the heavy lifting for us and interacts with the database to facilitate CRUD or Create Read Update and Delete. Let’s jump in.

Models in Codeigniter

Just like Controllers, the Model is a PHP class which is designed to work with your chosen database. Of course in the Open Source world the most popular option would be MySQL. Models are stored in application/models when following the conventions. Your basic model would look like this:

class Model_name extends CI_Model {

    function __construct()
    {
        parent::__construct();
    }
}

Just like your Controllers, your Model classes will extend the base Codeigniter Model. Remember, this is how your newly created class gets to inherit all the great built in methods of the parent class. The convention is for the Model name to have an Uppercase letter followed by all lowercase letters with words separated by an underscore. Save your models in application/models/ with lowercase letters like so model_name.php

Making Use of Models

In MVC, we know the incoming HTTP request gets sent to the controller. The controller is the director of the symphony sort to speak so she gets to determine what happens next. If the user’s request involves Creating, Reading, Updating, or Deleting of information, aka Database Interaction, then the Controller will make use of a Model to do so. In your controller, you can load your model like so:

$this->load->model('Model_name');

Once loaded you can then use all of the methods of that class by this convention:

$this->Model_name->function(); 

This controller would be an example of an RSS reader that reads the five most recent blog updates from a database:

class Rss_controller extends CI_Controller {

    function rss()
    {
        $this->load->model('Rss');

        $data['query'] = $this->Rss->get_last_five_entries();

        $this->load->view('rss', $data);
    }
}

Connecting to the Database Automagically

So far we have not talked about connecting to the database. That is because in a framework like Codeigniter, we typically want to forget about such repetitive and arduous tasks. The easiest way to ensure you don’t have to worry about manually connecting to the database is to set your credentials in the database.php file then add the word database to the library array found in application/config/autoload.php which may look something like this:

$autoload['libraries'] = array('database', 'session', 'xmlrpc');

The Active Record Class

Active Record is a design pattern to help make CRUD create read update delete functions easy. Active Record has the goal of making interacting with the database easy and transparent, although it helps greatly to have some knowledge of SQL statements in order to understand what Active Record is actually doing for you. The CRUD equates to Inserting Data, Selecting Data, Updating Data, and Deleting Data. Let’s check these features out.

Retrieving or Selecting Data

Selecting Data is normally the most used of the four CRUD operations. As such, Codeigniter has a plethora of built in functions that make this process easy. Instead of trying to cover every single option in Codeigniter, we’ll review the most popular functions. These will allow you to hit the ground running and build useful applications right away. The following functions allow you to build SQL SELECT statements so it will be helpful to have some familiarity with the actual SQL statements as well.

$this->db->get_where();

The WHERE clause is used to extract only those records that fulfill a specified criterion. One of the most popular functions of the entire model class, this allows you to add an SQL WHERE to the query which would look something like this:

$query = $this->db->get_where('thetable', array('id' => $id));
// or //
$query = $this->db->get_where('thetable', array('id' => $id), $limit, $offset);

$this->db->where();

Next up is the where() function which enables you to set WHERE clauses using one of four methods. This method is VERY popular since you can run this function multiple times to prepare your query and then when you are ready, you can run the $this->db->get(); function to actually send the query.

First: Simple key/value method:

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

When using multiple function calls as described above, they will be chained together with AND between them:

$this->db->where('name', $name);
$this->db->where('title', $title);
$this->db->where('status', $status);

Second: Custom key/value method:
You can include an operator in the first parameter in order to control the comparison:

$this->db->where('name !=', $name);
$this->db->where('id <', $id);

Third: Associative array method:

$array = array('name' => $name, 'title' => $title, 'status' => $status);
$this->db->where($array);

You can include your own operators using this method as well:

$array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);
$this->db->where($array);	

Fourth: Custom string:

You can write your own clauses manually:

$where = "name='Rick' AND status='Legend' OR status='active'";
$this->db->where($where);

$this->db->get();

The SQL SELECT command is used to fetch data from MySQL database. Once we prepare our query using things like $this->db->where(); $this->db->select(); $this->db->order_by(); $this->db->from(); $this->db->join(); $this->db->limit(); $this->db->or_where(); $this->db->select_max(); and $this->db->distinct() we need to run the query and then return the result using $this->db->get(); This function is very powerful as it can fetch all records from a table in one swoop:

$query = $this->db->get('thetable');

An optional second and third parameter allow you to set a limit and offset to the query

$query = $this->db->get('thetable', 10, 20);

Limits and offsets are a bit confusing, just remember that the above would show a limit of 10 records returned with a starting offset of 20, a test in phpmyadmin produces Showing rows 20 - 29 ( 10 total, Query took 0.0006 sec)

You’ll notice that the above function is assigned to a variable named $query, which can be used to show the results but be advised the $this->db->get(); function will get the result from the query, however you won’t be able to access that data until you use one of the built in result functions. You will usually use a for or foreach loop to iterate over results like so:

$query = $this->db->get('thetable');

foreach ($query->result() as $row)
{
    echo $row->website_name;
}

This is just one example of how to actually extract results from the query. We’ll take a look at the details of several result functions shortly, for now we’ll cover a few other very popular Selecting Data Functions that you could assign to a $query variable. Just know that once you have the results in a $query variable, you will always need to apply some type of result() function to that query to get the data. When in doubt, add a print_r($query); to your $query variable to dump the entire contents.

$this->db->select();

This allows you to write the SELECT statement in a granular fashion.

$this->db->select('website_name, website_url, website_description');

$query = $this->db->get('thetable');

$this->db->select(); accepts an optional second parameter. When this is set to FALSE, the framework will not try to protect your field or table names with backticks. This is useful if you need a compound select statement like so:

$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=7') AS amount_paid', FALSE);
$query = $this->db->get('thetable');

$this->db->order_by();

The ORDER BY keyword is used to sort the data in a recordset. This function enables you to set an ORDER BY clause in your query preparation. The first parameter will be the name of the column you would like to order by and the second parameter allows you to set the direction of the result. Options include asc, desc, and random.

$this->db->order_by("title", "desc");

You can also pass your own string in the first parameter:

$this->db->order_by('title desc, name asc');

I find it easier to use the multiple function calls option like so:

$this->db->order_by("title", "desc");
$this->db->order_by("name", "asc");

$this->db->from();

This function makes it easy to create the FROM clause of your SQL query. Specifies the database table from which to pull your data.

$this->db->select('website_name, website_url, website_description');
$this->db->from('website');

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

$this->db->join();

The JOIN clause matches rows in one table with rows in other tables and allows you to query rows that contain columns from both tables.

$this->db->select('*');
$this->db->from('website');
$this->db->join('framework', 'framework.id = website.id');

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

You can also use the $this->db->join(); function more than once before running your $this->db->get(); function to have more than one JOIN in the query. The third parameter of the function can be populated with left, right, outer, inner, left outer, and right outer for the various JOIN options as well.

$this->db->limit();

Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X – Y results. It is phrased as Limit X, Y and included at the end of your query. The second parameter lets you set a result offset.

$this->db->limit(10, 20);

$this->db->or_where();

The OR operator displays a record if either the first condition OR the second condition is true. This function is the same as $this->db->where(); but with $this->db->or_where(); you can have multiple instances that are joined by OR:

$this->db->where('name !=', $name);
$this->db->or_where('id >', $id);

$this->db->select_max();

The MAX() function returns the largest value of the selected column. This function writes a “SELECT MAX(field)” portion for your query.

$this->db->select_max('age');
$query = $this->db->get('members');

$this->db->distinct();

In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values. The DISTINCT keyword can be used to return only distinct (different) values.

$this->db->distinct();
$this->db->get('table');

Extracting Data from your Query

Remember we said earlier that you will need to apply some type of result function to your completed query. In all of the Active Record examples above, we build up our query with one or more functions, then run the query and assign the resource result to a variable most typically in the format of $query = $this->db->get(); It is with this $query variable that we can now apply the result() function to get our data. The following code examples use the standard $this->db->query(); function from the Database Class to perform the example query. Note that you can use this function exclusively if you would rather skip the Active Record pattern of building your query line by line using PHP, and simply write your own SQL statements directly and pass them to this function. Both approaches work and you can do all of the same things with either approach. Choose whichever feels more natural to you.

result();

This function returns the query result as an array of objects, or an empty array on failure. Often used in a foreach loop like you see here:

$query = $this->db->query('SELECT website_name, website_url, website_description FROM my_table');

foreach ($query->result() as $row)
{
    echo $row->website_url;
    echo $row->website_name;
    echo $row->website_description;
}

echo 'Total Results: ' . $query->num_rows(); 

result_array();

If you would rather work with your result set in the form of an array instead of an object you can do that using this command.

$query = $this->db->query('SELECT website_name, website_url, website_description FROM my_table');

foreach ($query->result_array() as $row)
{
    echo $row['website_url'];
    echo $row['website_name'];
    echo $row['website_description'];
}

row()

This function returns a single result row and if your query has more than one row, it returns only the first row. Check it out:

$query = $this->db->query("SELECT * FROM users LIMIT 1;");

if ($query->num_rows() > 0)
{
   $row = $query->row();

   echo $row->website_website_url;
   echo $row->website_url;
   echo $row->website_description;
}

//In a multi row result, If you want a specific row returned 
//you can submit the row number as a digit in the first parameter:
$row = $query->row(5);

//You can also add a second String parameter, which is
//the name of a class to instantiate the row with:
$query = $this->db->query("SELECT * FROM users LIMIT 1;");

$query->row(0, 'Linkr')
echo $row->website_url; // call attributes
echo $row->shorten_website_url(); // or methods defined on the 'Linkr' class

Just like the result() function has a corresponding result_array() function, so to does the row() function have a row_array() equivalent if you prefer to work with arrays rather than objects. I won’t hold that against you 🙂

One more really neat feature set in the result collection of functions are the traversal functions which can be used in both object and array form:

//You can walk through your results using these variations:
$row = $query->first_row()
$row = $query->last_row()
$row = $query->next_row()
$row = $query->previous_row()

//And work with arrays if you so desire
$row = $query->first_row('array')
$row = $query->last_row('array')
$row = $query->next_row('array')
$row = $query->previous_row('array') 

Wow! We covered a lot of ground in dealing with selecting data by building queries and then extracting data with the various result() and row() functions. With the functions described so far, which I find to be the most widely used by the way, you can build powerful and flexible options for interacting with your database. We’ll be a lot less granular with the remaining sections of Inserting Data, Deleting Data, and Updating Data.

Creating or Inserting Data

$this->db->insert();

Let’s say you have a MySQL table named website with the fields of website_name website_url and website_description. This is how you would insert a row of information into that website table using $this->db->insert();

$data = array(
   'website_name' => 'Vegi Bit' ,
   'website_url' => 'http://www.vegibit.com' ,
   'website_description' => 'Random Bits of Awesomeness'
);

$this->db->insert('website', $data);

With this example we took one group of values, related to each other in an array, and inserted that data into 1 row of our database table which has 3 fields. This is really cool, but you can also insert multiple groups of information at a time by using the $this->db->insert_batch(); function, check it out:

$this->db->insert_batch();

$data = array(
   'website_name' => 'Vegi Bit' ,
   'website_url' => 'http://www.vegibit.com' ,
   'website_description' => 'Random Bits of Awesomeness'
),
   array(
   'website_name' => 'Google' ,
   'website_url' => 'http://www.google.com' ,
   'website_description' => 'The Google Search Engine Homepage'
   )
);

$this->db->insert_batch('website', $data);

Deleting Data

$this->db->delete();

The DELETE statement is used to delete records in a table. This function generates a delete SQL string and runs the query.

$this->db->delete('mytable', array('id' => $id));

The first parameter is the table name, the second is the where clause. You can also use the where() or or_where() functions instead of passing the data to the second parameter of the function:

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

An array of table names can be passed into delete() if you would like to delete data from more than 1 table.

$tables = array('table1', 'table2', 'table3');
$this->db->where('id', '5');
$this->db->delete($tables);

Updating Data

$this->db->update();

The UPDATE statement is used to update records in a table. This function generates an update string and runs the query based on the data you supply. You can pass an array or an object to the function. Here the array notation:

$data = array(
               'title' => $title,
               'name' => $name,
               'date' => $date
            );

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

and here is the object notation:

    class Myclass {
        var $title = 'My Title';
        var $content = 'My Content';
        var $date = 'My Date';
    }


$object = new Myclass;

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

Instead of using the $this->db->where() function, you can optionally pass this information directly into the update function as a string or an array:

//string
$this->db->update('mytable', $data, "id = 4");

//array
$this->db->update('mytable', $data, array('id' => $id));

Well that sums up our discussion of Models in the MVC Model View Controller programming paradigm!

How to Retweet on Twitter 3 Concepts of Administering Windows Server 2012 JavaScript Types and Objects Tutorial How To Process HTTP Requests In Django What are Codeigniter Sessions Moving From Routes to Controllers in Laravel How To Write Validation Test Cases Sorting Threads By Number Of Replies CSS Selectors Tutorial Django Model Tutorial