When building any type of web application, you will inevitably need to create, update, or delete information from a database. There are many great database abstraction layer or DAL available to PHP programmers, and CodeIgniter is no exception. CI comes bundled with its own DAL, Active Record. As of CodeIgniter version 1.7.0 the following database conection types are supported:

  1. mssql
  2. mysql
  3. mysqli
  4. Oracle
  5. odbc
  6. postgre
  7. sqlite


So what is the benefit of using Active Record? Lets say you began developing a web application and are connecting to an MSSQL database. You use SQL statments designed for mssql to run all your queries. This works fine until your client asks you to use MySQL database instead of MSSQL. If you had been using Active Record, you wouldn't have to do anything but load a different database driver. Without Active Record you would be stuck with the task of re-writing your queries to work with MySQL.

Some people will say, using any kind of DAL adds extra overhead to your queries, and they would be right. There is an extra level of processing as the DAL will have to determine which datbase we are connecting to and how to properly format the query. In my opinion, the benefits far outweigh the negatives.

Now that we have the basics of Active Record out of the way, lets look at how to begin using the CodeIgniter Database Class in our models (you are putting your queries in your models....right?).

First, we need to load the database class. If your application will need to connect to the database on every page I highly recommend autoloading it (see /application/config/autoload.php). Otherwise, you will load the database connection manually like this:

Loading the Database / Running a Query


	$this->load->database();


Wow, that was easy. You can do this in your class construct or in each individual method that will need to connect to the database. There are several ways of running queries using Active Record and I will go over a few of them here. First, we have the query() funciton. This function takes a query as an argument and returns a database resource. It looks like this:

	$query = $this->db->query("SELECT * FROM my_table");


The $query variable would now hold a resource we can work with. Lets do something with the $query result now:

	$query = $this->db->query("SELECT name,date,email FROM my_table");

    //loop thru the records and echo out data

    foreach($query->result() as $row){

    	echo $row->name;

        echo $row->date;

        echo $row->email;

    }


The above example will run a query, loop thru the records (as an object) and echo out the given fields for each row. Pretty Simple! If you would prefer to work with an array, change your foreach loop to look like the below:

	//loop thru the records and echo out data

    foreach($query->result_array() as $row){

        echo $row['title'];

        echo $row['name'];

        echo $row['email'];    

    }


Method Chaining



One interesting feature available to those of us using PHP5 is Active Record method chaining. With method chaining, you can write very complicated queries with one line of code. Here is an example of a query using method chaining:

	$res = $this->db->select('*')->from('my_table')->where('id',22)->get()->row();


The above query in sudo code says, "Select everything from `my_table` where the id column equals 22. Go ahead and run the query (get()) and return the result as an object (row()). The beauty comes when you realize that you can write that code regardless of what database your connecting to. Again, method chaining is only available to those of us running PHP5.

I hope this has been an insightful article about why you should be using Active Record. I will also recommend taking a look at the User Guide for CodeIgniter as it is incredibly well documented, and contains much more information than I've included in this article. I welcome your comments here if you feel I've missed anything. Good luck and happy coding!