Working with Comma Separated Values in MySQL

By: Unknown on 03:35

Print It

One of my latest project I worked on was where I decided to use a feature that I saw in SMF Coding, that values are stored in a MySQL Database Column separated with Commas. Though I don’t know how to retrieve these values as at that moment but I was sure it will make my work more easier then creating new tables for it.

In a situation where we are having 2 tables like
tbl_schools
|id |name |
————–
|1 |1st sch  |
—————–
|2 |2nd sch|
—————–
|3 |3rd Sch|

tbl_students
|id |name |schs  |
————————-
|1   |Jajo      |1,2,3   |
————————-
|2  |Don      |2,3       |

The above tables are tables of Schools and Students. Now in a situation where a students wants to select different schools at the same time, we store the School IDs in the database as list of schools the student selected. :)
Now I want to query out list of students in the School ID 2, MySQL now made it easy with the function FIND_IN_SET(). Watch the query below :)

 SELECT * FROM tbl_students  
 WHERE  
 FIND_IN_SET('2',tbl_students.schs)  

The above should return the normal tbl_students table because both are in School ID 2 .