Solved

MySql and product filter

Posted on 2013-10-24
4
697 Views
Last Modified: 2013-10-25
I have products on an ecommerce website that have options eg size and colour. If I display a product filter (form) with checkboxes for all available attributes for each available option and the selected attributes are posted to some code that selects the product records then the sql below will fetch any product that has an options_values_id of 1 (eg blue) or 2 (eg red) where the options_values might be colours blue and red
select p.products_id, pd.products_name from products p left join products_attributes pa on p.products_id = pa.products_id where pa.options_values_id in(1,2);

Open in new window

The following code would do the same:
select p.products_id, pd.products_name from products p left join products_attributes pa on p.products_id = pa.products_id where (pa.options_id = 1 and pa.options_values_id = 1) or (pa.options_id = 1 and pa.options_values_id = 2);

Open in new window

But how could I search for products with colour red or blue and size medium where size is pa.options_id = 2 and medium is options_values_id = 3, thereby reducing results rather than increasing, without looping through all the
records and using an 'if' clause, can a union do this or a sub select?

It's difficult to present the full code and web page so if you need that then please don't comment.
0
Comment
Question by:ncw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 39597984
Short answer is that you'd have to do another left join on the same table. I'd have to see your table structure to be sure, but something like this:

select p.products_id, pd.products_name from products p
left join products_attributes pa on p.products_id = pa.products_id
left join products_attributes pa2 on p.products_id = pa2.products_id

where (pa.options_id = 1 and pa.options_values_id in (1,2))
  AND (pa2.options_id = 2 and pa2.options_values_id=3);
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39597985
select p.products_id, pd.products_name
from products p left join products_attributes pa on p.products_id = pa.products_id
where pa.options_values_id in(1,2) and pa.options_id = 2;

or whatever value represents the correct size.

You need to create a Stored Procedure with parameters and pass in the values based on the UI selection(s)

http://forums.mysql.com/read.php?98,358569
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 39598021
A longer answer really depends on your table structure. The way you have it now, I can tell that the item exists in red, blue and green. I also know that the item exists in small, medium and large. What I can't tell is if the item exists as both small and red. Your database structure would not indicate that. See http://sqlfiddle.com/#!2/decdf/9

select p.products_id, 
  pa.products_name color, 
  pa2.products_name size 
from products p 
join products_attributes pa on p.products_id = pa.products_id 
join products_attributes pa2 on p.products_id = pa2.products_id 

where (pa.options_id = 1 and pa.options_values_id in (1,2))
  AND (pa2.options_id = 2 and pa2.options_values_id=3);

Open in new window

0
 
LVL 1

Author Closing Comment

by:ncw
ID: 39600150
Seems to work fine except I changed  
pa.options_id = 1 and pa.options_values_id in (1,2)
to
((pa.options_id = 1 and pa.options_values_id = 1) or (pa.options_id = 1 and pa.options_values_id = 2))
That fitted better when creating the sql dynamically depending on options selected.
Thanks
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question