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!
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.
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.
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.
Excellent…glad it helped!
Byron
Ahh, that one got me to for a bit, hehe.
damn, I was doing the steps of a book, and in the book was that error!!. You saved my day!
woww… that’s cool, tricky error huh 🙂
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
It helped me Thx a lot