CODEWITHSUNDEEP
mysql
tom
tom

Reputation: 23

How to match csv stored in one column in mysql table?

iam a newbie in mysql

I have stored interests of user in csv format

say person_interests COLUMN stores multiple interests in person TABLE, say 

$row['person_interests']='11,22,33,44'

interests TABLE 11=Music,22=Travel, and so on

Now i want to list all persons who have 11 as interest, what should i use after WHERE clause?

SELECT * FROM persons WHERE ????? 
INNER JOIN
interests 
ON 
persons.person_interest
 =
interests.interest_id
WHERE
interest.interest_id=11

Upvotes: 0

Views: 898

Answers (1)

Barmar
Barmar

Reputation: 780899

It's a bad idea to store comma-separated data in SQL tables. You should use a many-to-many relation table to hold this. It makes searching and modifying the data more complicated, and matching values can't make use of indexes, so queries will be inefficient.

But if you're stuck with it, you can use FIND_IN_SET to match them.

SELECT *
FROM persons AS p
INNER JOIN interests AS i ON FIND_IN_SET(i.interest_id, p.person_interest)
WHERE i.interest_id = 11

Instead of putting all the interests in a single column, you should have a relation table:

CREATE TABLE person_interests (
    person_id INT NOT NULL, -- Foreign key to persons table 
    interest_id INT NOT NULL, -- Foreign key to interests table
    UNIQUE INDEX (person_id, interest_id),
    INDEX (interest_id)
);

Upvotes: 1

Related Questions