CODEWITHSUNDEEP
sqlcodeigniterjoin

Reputation:

getting data from 2 different table with JOIN sql. Codeigniter

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

Upvotes: 4

Views: 17115

Answers (3)

MD. ABDUL Halim
MD. ABDUL Halim

Reputation: 722

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

Upvotes: 0

pix0r
pix0r

Reputation: 31280

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.

Upvotes: 4

Michael Mior
Michael Mior

Reputation: 28753

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

Upvotes: 1

Related Questions