CODEWITHSUNDEEP
phparraysfunctioncodeigniter
julie
julie

Reputation: 111

How to use FIND_IN_SET() query in codeigniter

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.

Upvotes: 4

Views: 21661

Answers (4)

Vinith
Vinith

Reputation: 1274

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.

Upvotes: 0

Goshika Mahesh
Goshika Mahesh

Reputation: 759

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

Upvotes: 5

Rahul
Rahul

Reputation: 18567

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.

Upvotes: 0

Atural
Atural

Reputation: 5439

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

Upvotes: 2

Related Questions