Codeigniter Model Tutorial

Codeigniter Model

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:

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:

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

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

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:

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:

The second parameter takes an array so as to mimic the WHERE clause syntax of WHERE field = somevalue.

$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:

Produces: WHERE name = ‘Rick’

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

Produces: WHERE name = ‘Rick’ AND title = ‘Rock Star’ AND status = ‘Legend’

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

Produces: WHERE name != ‘Rick’ AND id < 155

Third: Associative array method:

Produces: WHERE name = ‘Rick’ AND title = ‘Rock Star’ AND status = ‘Legend’

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

Fourth: Custom string:

You can write your own clauses manually:

$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:

Produces: SELECT * FROM thetable

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

Produces: SELECT * FROM thetable LIMIT 20, 10

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:

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.

Produces: SELECT website_name, website_url, website_description FROM 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->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.

Produces: ORDER BY title DESC

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

Produces: ORDER BY title DESC, name ASC

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

Produces: ORDER BY title DESC, 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.

Produces: SELECT website_name, website_url, website_description FROM website

$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.

Produces:SELECT * FROM website JOIN framework ON framework.id = website.id

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.

Produces: LIMIT 20, 10

$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:

Produces: WHERE name != ‘Rick’ OR id > 93

$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.

Produces: SELECT MAX(age) as age FROM 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.

Produces: SELECT DISTINCT * FROM 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:

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.

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:

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:

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();

Produces: INSERT INTO website (website_name, website_url, website_description) VALUES (‘Vegi Bit’, ‘http://www.vegibit.com’, ‘Random Bits of Awesomeness’)

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();

Produces: INSERT INTO website (website_name, website_url, website_description) VALUES (‘Vegi Bit’, ‘http://www.vegibit.com’, ‘Random Bits of Awesomeness’), (‘Google’, ‘http://www.google.com’, ‘The Google Search Engine Homepage’)

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.

Produces: DELETE FROM mytable WHERE 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:

Produces: DELETE FROM mytable WHERE id = $id

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

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:

Produces: UPDATE mytable SET title = ‘{$title}’, name = ‘{$name}’, date = ‘{$date}’ WHERE id = $id

and here is the object notation:

Produces: UPDATE mytable SET title = ‘{$title}’, name = ‘{$name}’, date = ‘{$date}’ WHERE id = $id

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:

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