Retrieving data from 2 different tables using JOIN sql. Codeigniter

Question

I am using codeigniter and I need to get data from 2 different tables. while it returns data only from the works_image table. how can i get data from two tables?

Thank you very much!

  $ 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

While you are doing SELECT * ( Why is this a bad idea? ), You do not need to specify tables with a select() call. 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 of what you really have to do, it’s exactly what 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(); 

Thus, you can be sure that (a) you are not extracting unnecessary data from your database and (b) your code will not be interrupted if / when you change the database schema.

Answer 2

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

In short, you need to rewrite the select statement from

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

:

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

Please note that this was Google's first result for the 'CodeIgniter join'. First, try to figure out your questions. You can often get a quick response :)

Answer 3

You must 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 you will get your result