I have a site where i need to pull up data from a certain category, since one data can have multiple categories, i have them in a field where the categories are comma delimited, the IN clause does not work so, is there any other built in mysql function that i can use? also i have tried the FIND_IN_SET function but it also doesnt work thanks in advanced...
This is a bad way to store data in a database, and undermines one of the main reasons to use a database and not just a flat xml file. The easiest way is probably to explode out the field and run another query based on the values that you get while looping through the results. If possible, you're far better off using a table for categories and a third table defining the relationship between the two tables. This way you can have as many categories as you want to a single entry, and you can use joins to select the proper data.
well, i'm well aware of normalization(and i hate how this script stores data like this) lets just say that its not an option
In that case, I would either go with the explode in php, or try to use a LIKE '%%' Clause in the query.
hmm, i dont think like would work though, 1 scenario is if i have a category id set as 1 and then i have 11,12,21 or just about anything with 1
WHERE id = '1' OR id LIKE ',1%' OR id LIKE '1,%' OR id LIKE '%,1,%' Another option is to use regex: http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp