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
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;