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!