Page 1 of 1

Searching SQL fields where values are seperate by commas

Posted: Fri Feb 19, 2010 2:09 am
by social_experiment
I need some help with an SQL query. If there is a field in a database that contains non-numeric values such as 'tag, string, simplicity' what is the correct sql syntax to match only one of the values. Currently i am using the following php code :

Code: Select all

<?php $query = mysql_query("SELECT * FROM table WHERE field LIKE '%value%' "); ?>
If my search term was to be 'string' the query would also return 'simplicity' and infact, if I were to search just for any single character matching any of those in the field, a match would be returned.

Any ideas on this?

Re: Searching SQL fields where values are seperate by commas

Posted: Fri Feb 19, 2010 2:24 am
by requinix
The correct thing is to normalize your database. A little extra work now for a lot less pain later.

Create a table: each row matches one item with one tag. If you need more help, post the structure of your tables.

Re: Searching SQL fields where values are seperate by commas

Posted: Fri Feb 19, 2010 3:15 am
by social_experiment
Here is my table structure :

Code: Select all

 
 id, int(3), PK
 tags, vc(255)
 pagename, vc(255)
 

Re: Searching SQL fields where values are seperate by commas

Posted: Fri Feb 19, 2010 4:26 am
by requinix
That table will turn into

Code: Select all

id, int(3), PK
pagename, vc(255)
and instead of the tags field you'll create another table:

Code: Select all

tagid, autoincrement int, PK
pageid, int
tag, varchar, indexed
Also add a unique index on (pageid, tag).

Each tag in the old table will become a row in the new table, with pageid in the new table being the id in the old table. So if you had page #1 (apple, banana, car) and page #2 (doll, elephant) this new table would have

Code: Select all

tagid | pageid | tag
------+--------+-------
    1 |      1 | apple
    2 |      1 | banana
    3 |      1 | car
    4 |      2 | doll
    5 |      2 | elephant
And now your problem of searching for a specific tag is solved, not to mention past problems you worked around and future problems you would have had.

Re: Searching SQL fields where values are seperate by commas

Posted: Fri Feb 19, 2010 7:19 am
by social_experiment
Thanks for the help.