need to get result of joining multiple tables as one row

Question

I want to get result of joining multiple tables as one row and fetch multiple cuisine_name from t_cuisine table and get the cuisine_id in t_search.cuisineId column using php (CODEIGNITER) and joins

t_search table to get the cuisineId like this so that I can get the available cuisine names through the cuisineId.

t_search table

searchID|restaurant_name|cuisineId
1       |     XYZ       |  1,4,5       
2       |     KIH       |  2
3       |     GHY       |  4,5
4       |     UIO       |  1,2,3
5       |     RTY       |  3,5



t_cuisine table

cuisineId|cuisine_name
   1     |  ABC
   2     |  CDE
   3     |  EFG
   4     |  GHZ
   5     |  HJL

in my Model i've used

$this->db->select('*');
$this->db->from('t_search');
$this->db->join('t_cuisine','t_cuisine.cuisineId =  t_search.cuisineId');

which fetches data only based on single value in cuisineId in t_search.

This is one good reason not to store multiple values in one column. — Don't Panic, Mar 23 '17 at 21:12

score 1 · Answer 1 · answered Mar 23 '17 at 21:26

1

$this->db->select('*');
$this->db->from('t_search');
$this->db->join('t_cuisine','t_cuisine.cuisineId IN(t_search.cuisineId)'); 
$this->db->where('t_cuisine.cuisineId', X);

Change X to the ID of the cuisine you are looking for

answered Mar 23 '17 at 21:26

Simon K

1,503
1
8
10

score 0 · Accepted Answer · edited May 23 '17 at 11:54

What you had previously (when your query worked based on a single value in cuisineId) was a one to many relationship. Joining like that worked well because each search had one cuisine.

This is a many to many relationship, and this table structure doesn't support it well. Instead of storing a delimited list in the cuisineId column of your t_search table, you need another table to represent the relationship between search and cuisine, like this:

t_search_cuisine table

searchID|cuisineId
   1    |   1
   1    |   4
   1    |   5
   2    |   2
   3    |   4
   3    |   5
   4    |   1
   4    |   2
   4    |   3
   5    |   3
   5    |   5

Then you should be able to get all your data with one additional join.

$this->db->select('*');
$this->db->from('t_search');
$this->db->join('t_search_cuisine','t_search.searchID =  t_search_cuisine.searchID');
$this->db->join('t_cuisine','t_search_cuisine.cuisineId =  t_cuisine.cuisineId');

score 0 · Answer 3 · answered Mar 24 '17 at 00:09

Based on the structure of your table, joining those table would be not easy. Perhaps you could do with two queries instead.

    $this->db->select("cuisineId");
    $this->where("searchID", $searchID);
    $qry1 = $this->db->get("t_search");
    $res1 = $qry1->row_array();
    $qry1->free_result();

    if ($res1) {
        $this->db->select("*");
        $this->db->where_in('cuisineId', $res1);
        $qry2 = $this->db->get("t_cuisine");
        $res2 = $qry2->result();
        return ($res2) ? $res2 : false;
    }

need to get result of joining multiple tables as one row

3 Answers3