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
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.