• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact

CodeSamplez.com

Programming, Web development, Cloud Technologies

You are here: Home / Database / Codeigniter Active Record Class Overview

Codeigniter Active Record Class Overview

November 26, 2010 by Rana Ahsan 13 Comments

CodeIgniter Tutorials

In this article, I will give a small overview of the Codeigniter Active Record class, show the basic way to use it, its drawbacks and potential solution of a common problem(union operation with it, which isn’t directly supported) that developers face often. I will be using a few possible PHP code examples for demonstration purposes 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, the “Active record class” is one of the most popular and useful features of Codeigniter. It reduces the developer’s 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 executed, results in 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 the controller or view section(not even in library/plugin/helpers also) as this actually belongs to the model section(data access layer). Here are few PHP code samples are given below:


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();
}
Code language: PHP (php)


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 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);
Code language: PHP (php)

For more details references on exactly which functions are allowed, and what parameters with details Explanation with an 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 do the work. But, for 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 database engines(create different SQL syntax for a different database like MySQL, SQL server, oracle etc). In this case, we will just need to get help from third-party tools like doctrine, which are easily integrable with Codeigniter as a plugin/library.

Also, another drawback(or should I say lacking enhancement?), the 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 alternatives to SQL syntax, like select() for ‘SELECT’, where() for ‘WHERE’, there was no implemented method for UNION operation. So, if you are going to need to execute a query that requires ‘UNION’ , you may be thinking about what to do, avoid using an active record class. If not, and its possible to use it 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, still, we can implement it with the use of an active record class. Here is how:
Let’s assume we are merging two different columns into one in the same database table(One of the possible reasons why we need to use UNION). The 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();
	}
Code language: PHP (php)


Here, what we are doing is simply getting help from the CodeIgniter active record class to prepare two different queries 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 to this so that I can start finding a solution to them and update this post by adding them and solution PHP code samples also. Happy coding 🙂

Share If Liked

  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)

You may also like

Filed Under: Database Tagged With: codeigniter, php

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Reader Interactions

Comments

  1. Nipop says

    February 7, 2011 at 1:08 pm

    Nice work. Simple and works like a charm!!!

    Reply
  2. ev45ive says

    March 7, 2011 at 5:47 am

    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

    Reply
    • Asanka says

      February 20, 2013 at 5:20 pm

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

      Reply
      • Md Ali Ahsan Rana says

        February 25, 2013 at 2:24 am

        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.

        Reply
  3. Laurah Misaha says

    March 13, 2011 at 11:01 pm

    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.

    Reply
    • Rana says

      March 14, 2011 at 3:48 am

      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.

      Reply
  4. Baptiste Prophete says

    November 4, 2014 at 12:28 pm

    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!

    Reply
  5. Alex Xavier Rosa says

    August 3, 2015 at 9:03 am

    Hi Md Ali Ahsan Rana, see if you can help me with this code, this model only taking the start date and the end date zeroing, follows the code so you can help me:

    VIEW

    echo form_open(‘agenda/createcons’, ‘class=”form-cadastro1″‘);
    //$field_array = array(‘Data do Exame’, ‘Horario’, ‘Nome Paciente’, ‘Medico Solicitante’);

    echo heading($headline, 3, ‘class=”form-cadastro-heading”‘);
    echo br();

    echo form_input(‘data_inicial’, ”, ‘id=”data_inicial” title=”Data Inicial da Pesquisa” class=”input-block-level input-xlarge data_inicial” placeholder=”Data Inicial” required’);

    echo form_input(‘data_final’, ”, ‘id=”data_final” title=”Data Inicial da Pesquisa” class=”input-block-level input-xlarge ” placeholder=”Data Final”‘);
    echo br();

    echo br();
    echo form_submit(”, ‘Consultar’, ‘class=”btn btn-primary”‘);
    echo form_close();

    MODEL

    function getAgendaFil()
    {
    echo $data_fini = date(‘Y-m-d’, strtotime($this->input->post(‘data_final’)));
    echo $data_ini = date(‘Y-m-d’, strtotime($this->input->post(‘data_inicial’)));
    echo $data_fini = date(‘Y-m-d’, strtotime($this->input->post(‘data_final’)));
    print_r($this->db->get_where(‘agenda’,’data_consulta BETWEEN “‘. $data_ini. ‘” and “‘. $data_fini.'”‘));
    $this->db->order_by(‘data_consulta,hora’,”asc”);
    return $this->db->get(‘agenda’);

    }

    Reply
    • Md Ali Ahsan Rana says

      August 3, 2015 at 5:46 pm

      “only taking the start date and the end date zeroing”
      – Sorry, I didn’t get your question. Please explain clearly with expected/actual output as well.

      Reply
  6. mathirajesh says

    March 16, 2016 at 3:46 am

    Thank you for your help.

    Reply

Trackbacks

  1. Codeigniter Tutorial For Routing Request URLs | codesamplez.com says:
    January 22, 2011 at 2:47 pm

    […] so that i can came to know and i will try my best to find a solution. Happy coding Related Posts:Codeigniter Active Record Class OverviewBeginning Regex Programming With PHPCreate a simple wordpress pluginHow to declare and use a […]

    Reply
  2. Beginners Guide To Codeigniter Application Development | codesamplez.com says:
    May 2, 2011 at 12:13 am

    […] that helps a lot creating/executing sql query easily. You can read more on my another article about codeigniter active record class. Here is the code of the model class we will be […]

    Reply
  3. Using Doctrine ORM With Codeigniter Framework | codesamplez.com says:
    June 15, 2011 at 3:01 am

    […] Codeigniter has good library/mechanism for manipulating database activity. Besides, it introduced active record class, which minimizes the complexity of writing sql query. However, database support in codeigniter […]

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Follow Us

  • Twitter
  • Facebook

Top Posts & Pages

  • How To Work With JSON In Node.js / JavaScript
    How To Work With JSON In Node.js / JavaScript
  • PHP HTML5 Video Streaming Tutorial
    PHP HTML5 Video Streaming Tutorial
  • How To Work With C# Serial Port Communication
    How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
    Facebook C# API Tutorials
  • Using Supervisord Web Interface And Plugin
    Using Supervisord Web Interface And Plugin
  • LinQ Query With Like Operator
    LinQ Query With Like Operator
  • Get Facebook C# Api Access Token
    Get Facebook C# Api Access Token
  • Getting Started With UDP Programming in Java
    Getting Started With UDP Programming in Java
  • Utilizing Config File In C#.NET Application
    Utilizing Config File In C#.NET Application
  • Generate HTTP Requests using c#
    Generate HTTP Requests using c#

Recent Posts

  • Building Auth With JWT – Part 2
  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read

Tags

.net angularjs apache api audio auth authenticatin aws c# cloud server codeigniter deployment docker doctrine facebook git github golang htaccess html5 http javascript jwt linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty socket.io tfs tips unit-test utility web application wordpress wpf

Footer

Archives

Follow Us

  • Twitter
  • Facebook

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Copyright © 2023