Page 1 of 1
Query Help
Posted: Fri Mar 30, 2007 11:35 am
by shiznatix
Ok heres my situation. I have a field that looks like this:
Code: Select all
--field 'product_categories'--
record 1: 12,14,1,4
record 2: 19
record 3: 15,1,2
so basically I have a product and it can be in several different 'product categories' at once, makes it easier to maintain the product.
Now the problem comes when I am on the 'product category' page and am looking to display the products. At first you could only give a product 1 product category so all I would do would be to run a simple query like this:
Code: Select all
SELECT
*
FROM
products
WHERE
fk_product_category_id = "4"
with 4 being the id of the current category i was looking at. Now that I have the products in multiple categories that query of course does not work so I tried this query:
Code: Select all
SELECT
*
FROM
`kb_products`
WHERE
`active` = "1"
AND
"19" IN (product_categories)
now this works just fine and dandy if 19 is the first part of the product_categories field (like the product categories field looks like this: 19,12,20) but it does not work if the 19 is anywhere else in the field like 20,19,12.
So, how do I write a query that will get the correct results for me regardless of where the number is in the list
Re: Query Help
Posted: Fri Mar 30, 2007 1:57 pm
by timvw
shiznatix wrote:Ok heres my situation. I have a field that looks like this:
Code: Select all
--field 'product_categories'--
record 1: 12,14,1,4
record 2: 19
record 3: 15,1,2
so basically I have a product and it can be in several different 'product categories' at once, makes it easier to maintain the product.
I fail to see how it's easier to maintain the product... Simply adding referrential constraints becomes hard with this model...
shiznatix wrote:
Now the problem comes when I am on the 'product category' page and am looking to display the products. At first you could only give a product 1 product category so all I would do would be to run a simple query like this:
So, how do I write a query that will get the correct results for me regardless of where the number is in the list
You may want to have a look at the FIND_IN_SET function... (
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html)
Posted: Fri Mar 30, 2007 2:04 pm
by Begby
To start with, don't use comma delimited lists for linking to categories like this. What is your plan if you delete a category and have 25,000 products many of which might reference that category? Also doing these kind of searches against text fields are slow.
Instead use another table to join on, like this
Code: Select all
prodID catID
10 15
10 1
10 2
38 18
30 15
30 38
Then you can do this for say category 15
Code: Select all
SELECT *
FROM products p
LEFT JOIN product_cats c
ON c.prodID = p.prodID
WHERE c.catID = 15
Posted: Fri Mar 30, 2007 2:34 pm
by shiznatix
aye i am aware that would be the better way but I am looking for a quick fix way which would be to get this query working without having to do major code changes.
Posted: Sat Mar 31, 2007 2:21 pm
by califdon
Probably THE most fundamental issue when people who have never studied databases try to design a database is that they are starting with a database schema that is not "normalized." That's a term that describes what kind of data fields contain and in what tables they are stored. Begby was being kind by avoiding the technical language, but the real fact is that until you take his advice and change the structure of your schema, you will never have a workable system. This point is lost on beginners, and that's too bad.
Posted: Sat Mar 31, 2007 11:43 pm
by Benjamin
I believe what shiznatix is trying to say is that he needs to find a solution to an existing problem and that creating a new database schema may not be an option as it could be legacy code. With that in mind, the best solution, if all else fails, would probably be to create a class which would parse all the records and return an appropriate result. If there aren't too many records, this could possibly be done in memory, if that isn't possible, perhaps a secondary "index" could be created for fast lookups.
Posted: Sun Apr 01, 2007 3:43 am
by timvw
astions wrote:I believe what shiznatix is trying to say is that he needs to find a solution to an existing problem and that creating a new database schema may not be an option as it could be legacy code.
Even if this were the case changing the model is less expensive than not changing it on the long run...
A possible way to select all the categories that have the product with product_id 4:
Code: Select all
SELECT prods FROM cats WHERE product_categories = '4' OR product_categories REGEXP '(^4,)|(,4,)|(,4$)';
Since you probably are going to generate this query @runtime you can use CONCAT...
Code: Select all
SELECT prods FROM cats WHERE product_categories = CONCAT(@id) OR product_categories REGEXP(CONCAT('(^', @id, ',)|(,', @id, ',)|(,', @id, '$)'));
I suppose the rest is trivial from here on...