Codeigniter: ActiveRecord Join Tip

I ran into a problem with a SQL JOIN in Codeigniter’s ActiveRecord class. This post will help you get past that, but first a little intro to frameworks. If you’re a PHP developer, you have probably considered one of the incredibly useful frameworks that are available today. Cake, Zend, Symfony, and Codeigniter are some of the biggest names in the PHP framework game today. I’ve tried both Cake and Codeigniter, and found Codeigniter less strict, so I’ve been gravitating towards it. If you haven’t tried a framework, I highly recommend doing so. Any of the four I’ve listed are excellent choices, each with strengths and weaknesses, and rabid fans who can take their zealotry to extremes at times. As with anything that requires a computer, there is always a learning curve when taking on a framework, whether it’s figuring out MVC or just learning the syntax and structure of the framework itself. But the payoff is tremendous amounts of functionality that you don’t have to code, somewhat enforced structure, and possibly even cleaner, more manageable code. Check out Codeigniter’s User’s Guide to give you some idea of its features (you’ll have to click the little black “Table of Contents” tab at the very top & right of the page…it’s sort of hidden).

My Codeigniter SQL JOIN Problem

Let me start by saying that this was my problem, not Codeigniter’s. If only I’d read the freakin’ manual a little closer. So anyhoo, my problem was that I was trying to join 2 tables with a LEFT OUTER JOIN, but it wasn’t working. My code was looking like:

        $this->db->select('tr_dests.*', 'tr_states.state_name');
        $this->db->where('dest_id',$dest_id);
        $this->db->from('tr_dests');
	$this->db->join('tr_states','tr_dests.state = tr_states.state', 'LEFT OUTER');

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

This was giving me the left hand table data just fine, but I was getting an empty result for my “state_name” column. It is shameful how long I spent trying to figure out what my problem was. Of course, I was convinced that there was something wrong with Codeigniter Activerecord class. So, I really didn’t try everything.

The Answer:

The problem was in my SELECT statement. I really thought I was ok there, but how wrong I was! Only use one string of fields per line. This is how you would do the SELECT:

        
$this->db->select('tr_dests.*, tr_states.state_name');

or

        
        $this->db->select('tr_dests.*');
        $this->db->select('tr_states.state_name');

        //or as Derek notes below in the comments

        $this->db->select('tr_dests.*, tr_states.state_name', FALSE);

The FALSE argument is optional. It tells CodeIgniter not to protect the fields or table names with back slashes. Generally, you won’t need to use that.

Maybe that will save someone an hour or two…or three. I feel stupid…lesson learned!

9 Responses to Codeigniter: ActiveRecord Join Tip

  1. Derek Allard May 22, 2008 at 8:13 pm #

    I bet

    $this->db->select(‘tr_dests.*, tr_states.state_name’, FALSE);

    would have worked for you also. Looks like active record escaping.

    Great to see some writing on CodeIgniter! Keep up the great work.

  2. byron May 22, 2008 at 9:01 pm #

    Derek!
    Wow! Thanks for dropping by. You guys have a great framework in CodeIgniter, and it just keeps getting better. Congrats on getting ExpressionEngine moved over to CI. It’s always good PR when you can say that your eating your own dog food.

    For all of you who have not explored a PHP framework, you should really give CI a look. Derek is one of the driving forces (if not “the” driving force) behind Codeigniter.

  3. bambangp December 25, 2008 at 1:25 am #

    Thanks byron, it works for me, I almost stuck about joining 2 tables in CodeIgniter.
    Your posting give me some other explanation, really good. Thanks.

  4. Byron Bennett December 25, 2008 at 7:02 pm #

    Excellent…glad it helped!

    Byron

  5. Adam February 18, 2009 at 12:50 pm #

    Ahh, that one got me to for a bit, hehe.

  6. Roberto October 18, 2009 at 10:46 pm #

    damn, I was doing the steps of a book, and in the book was that error!!. You saved my day!

  7. Chan November 2, 2009 at 7:48 pm #

    woww… that’s cool, tricky error huh 🙂

  8. harpreet bhatia January 7, 2010 at 6:20 am #

    well did not help much. need to join three table with two common keys. The third table had redundancy, having multiple images from the same product. The result showed all the products twice who had 2 images and 3 for three n so on.

    i have something like this
    —————————————————————-
    $this->db->select(‘*’);
    $this->db->from(‘product_category’);
    $this->db->where(‘product_category.pcat’, $pcat);
    $this->db->join(‘products_details’, ‘product_category.id = products_details.product_cat’, ‘inner’);
    $this->db->join(‘pro_images’, ‘pro_images.pro_id = products_details.id’);
    $query = $this->db->get()->result_array();
    foreach($query as $row)
    {
    $data[] = $row;
    }
    return $data;
    $this->db->close();

    PLease help

  9. AHO May 28, 2010 at 9:42 am #

    It helped me Thx a lot

Powered by WordPress. Designed by Woo Themes