Codeigniter Active Record Class Overview


 Codeigniter Active Record Class Overview  
 

On this article, I will give a small overview of Codeigniter Active Record class, show basic way to use it, its drawbacks and potential solution of a common problem(union operation with it, which aren’t directly supported) that developers face often. I will be using few possible php code examples for demonstration purpose also, wherever applicable.

So, you are working with database functionality in Codeginiter framework. So, I guess, you already know some basics? If yes, go ahead, no problem. But if your answer is ‘NO’? Why don’t walk through a codeigniter basic application development tutorial first? It will make your life easier for doing the exercises of this tutorial.

What is CodeIgniter Active record class:

Among many others, “Active record class” is one of the most popular and useful features of Codeigniter. It reduces the developers effort to create and execute SQL queries on ‘model’ section. Instead of writing query syntax, here we have to call functions with proper parameters sequentially, which when executing, results a full length SQL query and returns corresponding database results. This is a great help, as while writing ourselves, sometimes, for long/complex queries, we have to correct SQL syntax several times before getting it functional.

Using This Class:

Using this active record class in a Codeigniter application is quite easy. Remember, not to use this on controller or view section(not even in library/plugin/helpers also) as this actually belongs to model section(data access layer). Here are few PHP code samples are given bellow:

function get_result($id){	
    //For selecting one or more columns
    $this->db->select('title, content, date');
    //For determine one or more tables to select from 
    $this->db->from('table1');
    //For joining with another table, table name as first argument and condition string as second argument
    $this->db->join('table2', 'comments.id = blogs.id');
    //assign where condition
    $this->db->where('id', $id); 
    //function without any argument. It actually runs the query that was built on last few statements.
    $this->db->get();
    //returns result objects array
    return $query->result();
}

Note that, on ‘where’ method, you can pass an associative array as parameters as well. That will assume an ‘AND’ operator in every key-value (key = db column name, value = result to be checked against) pair and will construct the query accordingly.
Inserting data is also quite easy as follows:

$data = array(               
               'id' => "8",
               'title' => "test title");
$this->db->insert('table_name', $data);

For more details reference on exactly which functions are allowed, what parameters with details Explanation with example, please visit official documentation on it.

Do you know? How to see what query is actually getting constructed? You can get it by using “$this->db->last_query()” method after execution. We are gonna use it soon, keep reading. Also, if you keep your profiler enabled by “$this->output->enable_profiler(TRUE);”, then you should see the executed query, time taken etc benchmark at the bottom of the page.


Read The Complete CodeIgniter Tutorials Series By CodeSamplez.com

Drawbacks:

Actually, if we talk about finding a way to create SQL queries easier, then its simply do the works. But, for a better development or if we want to follow another technology like, ORM(Object relational mapper), then it won’t fulfill our needs.Moreover, it also doesn’t provide support for long-range of database engines(create different SQL syntax for different database like MySQL, SQL server,oracle etc). In this cases, we will must need to get help from third-party tools like doctrine, which are easily integrable with Codeigniter as plugin/library.

Also, another drawback(or should I tell lacking of enhancement?), active record class doesn’t provide any function to perform “UNION” operation directly, at least I didn’t find any(Correct me if I am wrong).

Perform Union operation using active record class:

Although most of the functions are provided as alternative from SQL syntax like select() for ‘SELECT’, where() for ‘WHERE’, but there was no implemented method for UNION operation. So, if you are going to need executing a query that requires ‘UNION’ , you may be thinking what to do, avoid using active record class? If not and its possible to use there then how?

If you are thinking for performance, best is to avoid active record and write raw query. But, if you are not comfortable in writing raw query and looking for a codeigniter active record based alternative solution, there is one for you as well.

Although it’s not possible to use ‘UNION’ directly, but still, we can implement it with use of active record class. Here is how:
Lets assume we are merging two different column into one in a same database table(One of the possible reasons for which we need to use UNION). Code should be something like this:

function get_merged_result($ids){					
		$this->db->select("column");
		$this->db->distinct();
		$this->db->from("table_name");
		$this->db->where_in("id",$model_ids);
		$this->db->get(); 
		$query1 = $this->db->last_query();
		
		$this->db->select("column2 as column");
		$this->db->distinct();
		$this->db->from("table_name");
		$this->db->where_in("id",$model_ids);
		
		$this->db->get();	
		$query2 =  $this->db->last_query();
		$query = $this->db->query($query1." UNION ".$query2);
		
		return $query->result();
	}

Here, what we are doing is simply getting help from codeigniter active record class to prepare two different query and then we are manually constructing our original query. That’s all. The above function will return the correct results with desired UNION operation.

Let me know(by commenting) if you found something more which aren’t implemented properly or if you have any other question related this, so that i can start finding solution on them and update this post by adding them and solution php code samples also. Happy coding :)

Comments

  1. says

    The problem is – it runs 3 queries – which 2 of has no use, except to compile sql statement. Bit of a waste, isn’t it?

    I know that CI_DB_active_record::_compile_select() is a private function, but still bit of a overkill.

    ~ev45ive

    • says

      +1

      You might as well write the query as a string. Why run 2 additional queries to get the string output?

      @author – I suggest you correct this as it can lead a newbie towards another bad practice.

      • says

        Hi, Thanks for your feedback. Yes, I do know also that its not a quite good practice. However, it can be a work around for developers who doesn’t feel comfortable or doesn’t know to write raw sql query. I better hope that, codeigniter active record class do get a new release with this features in near future. Additionally, as you suggested I will mention it in the original post.

  2. Laurah Misaha says

    Thank you for sharing this information. I did implement this code in my script, but I didn’t manage to get the expected results since I am using 2 databases from 2 different hosts / servers. I think, this only works to manipulate databases from the same server. I will be very much appreciated if you can help me to figure out how to union two databases from two different server. Once again, thank you very much.

    • says

      I think, in that case, It will be best to retrieve them individually and then merge them using php itself(with array_merge($array1, $array2) function). Hope this helps.

  3. Baptiste Prophete says

    thank you so much for this useful teaching about codeigniter, i have an issue with a graph to display with codeigniter, my sql request cannot work when i use codeigniter however it works when i don’t use it, how hard i tried to make it work with codeigniter i fail. this code is about: $query = mysqli_query($GLOBALS[“___mysqli_ston”], “Select monthname(TranDate) as MyDate, sum(T.Newark) as Newark,
    sum(T.Berkeley) as Berkeley, sum(T.Elizabeth) as Elizabeth
    From

    (SELECT TranDate, Sum(Production) as Newark, 0 as Berkeley, 0 as
    Elizabeth
    FROM OpenDent.production
    Where
    year(TranDate) = year(now()) AND
    oid=1007
    group by month(TranDate)
    having sum(production)>0
    Union

    SELECT TranDate, 0 as Newark, Sum(Production) as Berkeley, 0 as
    Elizabeth
    FROM OpenDent.production
    Where
    year(TranDate) = year(now()) AND oid=1008
    group by month(TranDate)
    having sum(production)>0
    Union

    SELECT TranDate, 0 as Newark, 0 as Berkeley, sum(Production) as
    Elizabeth
    FROM OpenDent.production
    Where
    year(TranDate) = year(now()) AND oid=1009
    group by month(TranDate)
    having sum(production)>0

    ) as T
    Group By month(T.TranDate)”);

    $category = array();
    $category[‘name’] = ‘MyDate';

    $series1 = array();
    $series1[‘name’] = ‘Newark';

    $series2 = array();
    $series2[‘name’] = ‘Berkeley';

    $series3 = array();
    $series3[‘name’] = ‘Elizabeth';

    while($r = mysqli_fetch_array($query)) {
    $category[‘data’][] = $r[‘MyDate’];
    $series1[‘data’][] = $r[‘Newark’];
    $series2[‘data’][] = $r[‘Berkeley’];
    $series3[‘data’][] = $r[‘Elizabeth’];
    }

    $result = array();
    array_push($result,$category);
    array_push($result,$series1);
    array_push($result,$series2);
    array_push($result,$series3);

    print json_encode($result, JSON_NUMERIC_CHECK);

    ((is_null($___mysqli_res = mysqli_close($con))) ? false : $___mysqli_res);

    i have issue to separate these codes in model file and controller file.. would you help me please!

Trackbacks

Leave a Reply