Comma Seperated Values and LIKE php/mysql Troubles

Question

The Set up This is more or less a follow up question to something I had previously posted regarding comma separated values (explode,implode). Here's the scenario which has been stomping me the last few days as I'm a noob--sorry for the lengthy post.

I'm passing a variable via the url (index.php?id=variable), I then check the database to find the rows containing that variable using

SELECT * FROM table WHERE column LIKE '%$variable%' 

I'm using the wildcards because the results are a comma separated value with the variable appearing multiple times in the database.

So if we were assigning-- say schools to popular tv shows..my database is set up so that the user can assign more than one school to the tv show. IE.

South Park--> fsu, nyu ,mit

Archer --> harvard, nyu

Index.php?id=nyu would display Sourth Park & Archer.

The Problem Because I am using Like '%variable%'

If I have the following: South Park-->uark Archer-->ua

index.php?=ua Instead of just Archer showing, Southpark would also show.

Which makes sense due to the wildcards...but can anyone think of a way to do this achieving the results I want?..Is there any way to achieve more precise results using a comma separated value?..I'm completely stomped and will appreciate any help.

Answer 1

A better option is to create two other tables. One table is the one you already have 'tv_shows' and the second table is the 'tags' table and the third table is the connection table.

Here is a sample:

 CREATE TABLE tags(id INT, tag VARCHAR(200));
 CREATE TABLE tv_tags(show_id INT, tag_id INT);

 SELECT FROM tv_shows,tv_tags WHERE tv_shows.id=tv_tags.show_id AND tag_id IN (SELECT id FROM tags WHERE tag LIKE 'tag');

Answer 2

have you tried the REGEXP operator ? sth like

SELECT * FROM table WHERE column REGEXP('(^|,)%variable%(,|$)')  

it can be slightly different if you have spaces between your tags, but the idea is here