Complicated PHP MySQL Query

Having Trouble Wrapping My Head Around It :)

[wfernley]

I'm trying to create a query that will pull product id's from a database based on options the user will select.

For example there are three options:

Green (ID 1)
Blue (ID 2)
Red (ID 3)

Product A (ID 1) comes in green and blue. Product B (ID 2) comes in red and green. Product C (ID 3) only comes in red. So the table in the db has

option_id | products_id |
---------------------------
1 | 1
2 | 1
1 | 2
3 | 2
3 | 3

How can I create a SQL command that will only pull products that match the options the user selects. My products are more complex than this and come with many more options :) This should hopefully explain what I'm looking to do though.

Thanks in advance for your help!

[stajer]

select p.products_id, p.products_name
from table.products p, table.options o
where p.products_id = o.products_id
 and o.option_id IN (1,3)

[wfernley]

Thanks for your reply Stajer! Sorry I just realized I made a mistake on the question. I don't have two tables but instead one table with two columns - options_id and products_id.

The options are more complex than just one color. There would be other options like size (Small - ID 4, Medium - ID 5 and Large - ID 5). All options have a unique id.

So the user would select what sizes they are interested in and what colors. The SQL command would then find products that match that size AND color.

[topr8]

SELECT products_id FROM table WHERE options_id = 2
//this is the colour
AND products_id IN (SELECT products_id FROM table WHERE options_id = 9)
//this is the size

[wfernley]

Thanks topr8. Is there any way to optimize this as we have over 100 option types?

Thanks again for your help! :)

[wfernley]

Well I'm going to try and clarify this again because I think I'm not explaining it correctly. The more I work on this, the more I see how complex it is! :)

Here are a few of the many options:

Color
- Green
- Blue
- Red

Size
- Small
- Medium
- Large

Arm Length
- Short
- Long

Neck
- V Neck
- Collar
- Standard

Each of my products are unique so an example is a product that is green, medium sized with a long arm length and a v neck.

I'm creating a "finder" tool that will allow the user to select multiple options and the finder will display the matching products.

Here is the key, they will be able to select multiple options within each category. For example, they can select medium and large within the size category.

The options have unique id's and the products have unique id's. This means they will each have multiple entries in the table. Again the table structure is :
option_id | products_id |
---------------------------
1 | 1
2 | 1
1 | 2
3 | 2
3 | 3

While all options are in the table, when the finder form is "posted" the ids are sorted by category so it will be easy to say the color can be "this" or "this", size just "this" and color "this", "this" or "this".

So an example will be "select a product that is green or blue, medium sized, with either a v neck, collar, or standard neck"

I hope that makes sense. I have been trying to wrap me head around this and its getting late! :)

Thanks again in advance for your help!

[henry0]

Maybe you could conceive it differently
example: (refine your options)
search by type -> choose T-top
then ajax -> collar option
then ajax -> color option
which leads to an easy query

[wfernley]

Thanks. I already have all the structure complete so I just need the sql command to work. While ajax would work, the way it is currently structured would mean recoding most of the finder. Plus I found a great dropdown menu script that allows checkboxes within a selectbox. It makes selection very organized and easy.

[topr8]

>> Is there any way to optimize this as we have over 100 option types?

100 option types! and could they all apply to one item? if they do that would be a very slow query.

who designed the database? did you inherit it from before? or has it been built new?

[wfernley]

Yes, over 100 :) The items have nothing to do with shirts, just thought that would be an easier for you to understand. In reality, the products are electronics that have various features both in hardware and software.

I designed the database and built it from scratch.

[topr8]

... and the user would select from 100 groups of options with each item?

if so then obviously 100 sub-queries would be insane (it could be done with joins too but the complexity of 100 joins would equally be inappropriate)

i'll be honest, i haven't worked with 100 groups of options for one item before.

i think you might have to consider henry0's idea or think carefully about your database schema and maybe redesign it.

[brotherhood of LAN]

SELECT products_id FROM table WHERE options_id = 2
//this is the colour
AND products_id IN (SELECT products_id FROM table WHERE options_id = 9)
//this is the size

A query similar to this should be ok... rather than do a subquery how about using IN

SELECT products_id,COUNT(*) AS nummatches
FROM table
WHERE options_id IN (1,3,5,6,7,9)
GROUP BY products_id

That would match any product where options_id is matched. `nummatches` would tell you how many of the options matched and might be useful for an ORDER BY clause.

You could slightly alter it to return all the matched options (grouped) alongside each matched product

SELECT products_id,COUNT(*) AS nummatches,GROUP_CONCAT(options_id) AS matchedoptions
FROM table
WHERE options_id IN (1,3,5,6,7,9)
GROUP BY products_id

[wfernley]

Thanks again for your replies and suggestions. While there are over 100 options, each product only gets associated to around 30. The form is separated in a basic and advanced search depending on how complex the user wants it to be. I have created an ajax solution before but feel the current setup would work better as the user can select the options quicker and only have to do one sql query. It is a good idea though henry0.

I will do some playing around and let you know with your suggestions and let you know how it goes.

Thanks again, it is a huge help!