Extract the single value from a column containing multiple values using PHP/MySQL

Question

I have inserted multiple values from select statement in a single column using implode function. Now I want retrieve those values separately for my further queries. I have a student table in which I am storing their different area of interest in a single column using the implode function implode(',', $_POST['aoi']);. The structure of the table is like this:

id   |  Name   |           AOI
-------------------------------------------
101  | Paul    | Hardware,Networking
102  | Fred    | Finance,Insurance,Banking

Now I want to retrieve this student data separately based on their interest. I tried to extract data using explode function. But it is returning the data in array format.

And I have to extract area of interest separately. Can anybody tell me how to extract the single value from a column containing multiple value. So that I can execute my further queries.

Like $query = "select student.id,student.Name from student where AOI = 'Insurance'";

Thanks.

Tip number 1, normalise your database. Tip number 2 normalise your database. en.wikipedia.org/wiki/Database_normalization — Petah
– Petah, Commented Sep 24, 2012 at 11:37
If you use other database like MongoDB, you can add AOI as array and store values. But with MYSQL, you need to normalize the database. — Justin John
– Justin John, Commented Sep 24, 2012 at 11:46
I am a novice database user. I will definitely try to implement normalization concept. Thanks! — Yogesh Rawal
– Yogesh Rawal, Commented Sep 24, 2012 at 11:49

user399666 · Accepted Answer · 2012-10-03 21:09:38Z

5

Your database structure is fundamentally wrong and it will most likely lead to scalability issues further down the line. You should look into the concept of database normalization. In this particular scenario, you should be using a many to many relationship:

Students:

id   | name  
---------------
101  | Paul    
102  | Fred

StudentInterests

id   |  student_id  | interest_id
---------------------------------
1    |  101         | 1
2    |  101         | 2
3    |  102         | 1

Interests

id   | interest
----------------
1    | Insurance
2    | Networkings

edited Oct 3, 2012 at 21:09

answered Sep 24, 2012 at 11:40

user399666

20k7 gold badges49 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Petah Over a year ago

This is the answer, what the hell is with those other answers, geese.

Collectives™ on Stack Overflow

Extract the single value from a column containing multiple values using PHP/MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related