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 . "
";
}

MAybe this post will help you: https://stackoverflow.com/questions/6024800/codeigniter-table-join — Vitaliy Matveev, Jul 24 '17 at 06:08

score 4 · Answer 1 · edited May 23 '17 at 10:33

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.

thanks for advice : ) I used .* because of I already have only few parameters at table. they are not big tables. or do you think no matter how smal table is that, i should specify exactly which fields i need? — , Jul 24 '09 at 18:52
It's always a good idea, regardless of the size of the table, to specify exactly which fields you need. — pix0r, Jul 24 '09 at 21:08

score 1 · Answer 2 · answered Jul 24 '09 at 18:44

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 :)

answered Jul 24 '09 at 18:44

Michael Mior

28,107
9
89
113

I actually have searched at google for hours and hours :/ to find a quick solution, but I could not notice that ' ' point :/ blind me! it is working great now!!! thanks! :) – Jul 24 '09 at 18:50

score 0 · Answer 3 · answered Dec 31 '12 at 10:54

You should be to write below code

$this->db->select('works_image.*', 'works.*');
$this->db->from('works_image');
$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 . "
";
}

I think that you get your result

getting data from 2 different table with JOIN sql. Codeigniter

3 Answers3