getting data from 2 different table with JOIN sql. Codeigniter

Question

I use codeigniter, and I need to get data from 2 different table. for now it returns data only from works_image table. how can I get data from both 2 table ?

thanks a lot!!

$this->db->select('works_image.*', 'works.*');
$this->db->from('works_image', 'works');
$this->db->join('works', 'works.id = works_image.id_work');
$result = $this->db->get();

foreach ($result->result() as $row) {
    echo " # " . $row->id . " - " . $row->thumb . " - " . $row->wname . "
";
} 

Answer 1

This post should answer your question: http://www.whypad.com/posts/codeigniter-activerecord-join-tip/178/

In short, you need to rewrite your select statement from

$this->db->select('works_image.*', 'works.*');

to this:

$this->db->select('works_image.*, works.*');

Note that this was the first result on Google for 'CodeIgniter join'. Try Googling your questions first. You can often get yourself a faster answer :)

Answer 2

As long as you're doing a SELECT * (Why is this a bad idea?), you shouldn't need to specify tables with the call to select(). It will select all fields by default.

$this->db->from('works_image', 'works');
$this->db->join('works', 'works.id = works_image.id_work');
$result = $this->db->get();

Should work fine.

Instead, what you really should be doing, is specifying exactly which fields you need:

$this->db->select('works_image.id, works_image.name, works_image.id_work, works.id, works.name'); // (or whichever fields you're interested in)
$this->db->from('works_image', 'works');
$this->db->join('works', 'works.id = works_image.id_work');
$result = $this->db->get();

This way you can be sure that (a) you're not pulling unnecessary data from your DB, and (b) your code won't break if/when you modify your DB schema.