I have a list of values that I need to compare to a list in a field. rather than doing a bunch of nested loops... i thought one of you geniuses here at 12s would have an idea of how to do this.
i.e.
find out if one of these values "4,9,11,14"
is in this field "3,6,7,11,15,17"
Weird. You're not actually storing the string "3,6,7,11,15,17" in the database, are you?
You shouldn't. But, if you are you're not going to be able to do what you want with SQL. You can, however pull out that list and parse it as JSON
x = JSON.parse(object.field);
And then use whatever set operations you have in your language to check for matches.
it's a list of ID numbers from another table...
i.e. product A has fabrics 3,6,8,12
i figured that was the most efficient way to store the data... is there a better way?
my solution was to run a loop and use FIND_IN_SET() to check the list against the field value. seems to work.
No. What you need to do is store the relationship in a join table.
products
id name
fabrics
id name
product_fabrics
product_id fabric_id
For a bit more on the logic, read up on Normal Forms
Then you can do things like
SELECT DISTINCT products.* from products p JOIN product_fabrics pf ON pf.product_id = p.id WHERE pf.fabric_id in (3,4,5);
What he said. (Almost) never store multiple values in one comma separated field - especially if you need to query on a subset of these values.
Personally I would do a split based on the comma and create an array of values, which is why I am not the best person to ask 
Originally posted by: JimmyTheGent Personally I would do a split based on the comma and create an array of values, which is why I am not the best person to ask
yeah, that was my ultimate solution...
i'll revisit this next year when i launch the 2011 site for this client 
You might want to look into hiring someone to do your database design, that sort of thing tells me that there are probably some pretty big issues hiding under the surface.
Count yourself lucky, one of my ongoing clients uses a system that was literally the first piece of programming the developer had ever done. Among many, many other horrors, every single field in the database is a VARCHAR.
Originally posted by: Stickman Count yourself lucky, one of my ongoing clients uses a system that was literally the first piece of programming the developer had ever done. Among many, many other horrors, every single field in the database is a VARCHAR.
:oof:
Originally posted by: Stinky No. What you need to do is store the relationship in a join table.
products
id name
fabrics
id name
product_fabrics
product_id fabric_id
For a bit more on the logic, read up on Normal Forms
Then you can do things like
SELECT DISTINCT products.* from products p JOIN product_fabrics pf ON pf.product_id = p.id WHERE pf.fabric_id in (3,4,5);
THIS times a billion