Query Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Query Help

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Query Help

Post 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)
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

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