mysqlcodeigniter-3

How to display the days on the view page if days id is multiple in the single column?


I have two-three tables. First is days and in the second table I am inserting the data

days_id days_name
    1   Monday
    2   Tuesday
    3   Wednesday
    4   Thursday
    5   Friday
    6   Saturday
    7   Sunday

In the second table

  id    | days_id  
  1     | 2,3,5
  2     | 1,3,7
  3     | 1 
  4     | 2 

I know, it's the not correct way to insert multiple data in a column but still, I have to do this. Now my issue is, I have to display the list in the table.

(Note: This is not 100% my output. I just want to know how to display the days).

    id    | days_id  
    1     | Tuesday,Wednesday,Friday
    2     | Monday,Wednesday,Sunday
    3     | Monday 
    4     | Tuesday

What query I have to use it? I tried below query but it's displaying only first day.

SELECT *
FROM `b_list`
JOIN `days` ON `b_list`.`b_days` = `days`.`days_id`
WHERE `b_list`.`b_status` = 1

I am using CodeIgniter. What can I try next?

$result = $this->db->where(['b_list.batch_status'=>1])
            ->select('*, group_concat(days_name ORDER BY days_id ASC) as days_list')
            ->from('b_list')
            ->join('days','FIND_IN_SET(days.days_id,b_list.days_id)')
            ->get()
            ->result();

and using above code I am getting the below query

SELECT *, group_concat(days.days_name ORDER BY days_id ASC) as days_list FROM `b_list` JOIN `days` USING (FIND_IN_SET(days.days_id,b_list.days_id)) WHERE `b_list`.`batch_status` = 1

Solution

  • You can use FIND_IN_SET() to join on a list and GROUP_CONCAT() to concatenate them into a string:

    SELECT b_list.id, group_concat(days_name ORDER BY days_id ASC) as days_list
    FROM b_list
    INNER JOIN days ON FIND_IN_SET(days.days_id, b_list.days_ids)
    GROUP BY b_list.id;
    

    example output

    DEMO: https://www.db-fiddle.com/f/e9fCJRNiSPwk8jQtFWuEFB/4