4

I have two tables- One with attendance details and another one with student detail. The following are the table structures:

tbl_attendance

aid             date              attendance    

1          2017-03-09            5,6,9            
2          2017-04-06          12,6,10

tbl_students

student_id         name

5                    John
6                   Bryan

9                   Anna

10                 Mathew

12                 Susan

Now, I want to display the names of the absentees in the view as something like say. for example:

Date                 Absentees

2017-03-09   John, Bryan, Anna

2017-03-06   Susan, Bryan, Mathew

I was trying to do it with FIND_IN_SET()..but it seems bad luck..Is there a better way to sort this out?

UPDATE

I used this query instead and it echoed only the first id's name in each row...

    $query = $this->db
   ->select("tbl_attendance.*,tbl_students.name")
   ->from("tbl_attendance")
   ->join("tbl_students","tbl_students.student_id=tbl_attendance.attendance")
   ->where('FIND_IN_SET(tbl_students.student_id, tbl_attendance.attendance)')
   ->GROUP_BY('tbl_students.student_id')
   ->get()->result_array();

But as there are three numbers separated by commas in each row I want the rest to be echoed as well.

Cœur
  • 37,241
  • 25
  • 195
  • 267
julie
  • 111
  • 1
  • 2
  • 13

4 Answers4

5

This Works 

$query = $this->db
    ->select("td.Date, GROUP_CONCAT(ts.student_name SEPARATOR ',')")
    ->from("tbl_students AS ts")
    ->join("tbl_attendance AS ta","find_in_set(ts.st_id,ta.attendance)<> 0","left",false)
    ->get();
Stephan T.
  • 5,843
  • 3
  • 20
  • 42
Goshika Mahesh
  • 689
  • 6
  • 5
2

How about that ?

$query = $this->db
    ->select("td.Date, GROUP_CONCAT(ts.student_name)")
    ->from("tbl_students AS ts")
    ->join("tbl_attendance AS ta","find_in_set(ts.st_id,ta.attendance)","left",false)
    ->get();
Atural
  • 5,389
  • 5
  • 18
  • 35
  • It shows MYSQL Syntax error..bad luck for me... :-( – julie Mar 31 '17 at 08:17
  • oh my bad - i updated my answer - the left as 3rd parameter was missing – Atural Mar 31 '17 at 08:53
  • This is the error I Get: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(ts.st_id,ta.attendance))' at line 3 – julie Mar 31 '17 at 09:05
  • yeah - i updated my answer because i left out the 3rd parameter - try it again – Atural Mar 31 '17 at 09:08
  • Query returned this error -> You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ts.st_id,ta.attendance) – Shubham Azad Dec 28 '18 at 08:35
0

You can try query like this,

SELECT a.`date`,group_concat(s.student_name)  
FROM tbl_attendance a,tbl_students s  
WHERE FIND_IN_SET(s.st_id, a.attendance) group by `date`;

Description :
FIND_IN_SET that allows you to find the position of a string within a comma-separated list of strings.

Syntax:

FIND_IN_SET(needle,haystack);

Hope this will solve your problem.

Rahul
  • 18,271
  • 7
  • 41
  • 60
  • Actually I want to echo all the dates and its corresponding absentees list. Above shown is just an example... – julie Mar 31 '17 at 08:22
  • I var_dumped the result of the above query and it echoed the result with repeating names for so many times like this "Achu,Achu,Achu,Achu,Achu,Achu,Achu,Achu,Achu,Achu,Achu,sruthy,sruthy,sruthy,sruthy,sruthy,sruthy,sruthy,sruthy,sruthy,sruthy,sruthy,sruthy,sruthy, – julie Mar 31 '17 at 08:26
  • I made some changes please try again – Rahul Apr 03 '17 at 08:11
  • I wanted the query to use in codeigniter ...when I tried this query in the codeigniter form, I am getting syntax errors...so could you please help me in that aspect as well... – julie Apr 08 '17 at 14:25
0

Here comma separated category IDs are saved in row 'category' eg., '12,15,7,19'

$category_ID = 15;

$this->db->select('*'); $this->db->from('products'); $this->db->where('FIND_IN_SET("'.$category_ID.'","category") <>','0'); $this->db->where('deleted','0'); $this->db->order_by('product_ID', 'DESC');

I hope this helps CI developers to use FIND_IN_SET.

Vinith
  • 1,264
  • 14
  • 25