homepage Welcome to WebmasterWorld Guest from 54.227.182.191
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Complicated PHP MySQL Query
Having Trouble Wrapping My Head Around It :)
wfernley




msg:4267615
 10:05 pm on Feb 15, 2011 (gmt 0)

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




msg:4267616
 10:16 pm on Feb 15, 2011 (gmt 0)


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




msg:4267631
 10:36 pm on Feb 15, 2011 (gmt 0)

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




msg:4267639
 10:52 pm on Feb 15, 2011 (gmt 0)

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




msg:4267727
 2:45 am on Feb 16, 2011 (gmt 0)

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

Thanks again for your help! :)

wfernley




msg:4267757
 3:55 am on Feb 16, 2011 (gmt 0)

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




msg:4267939
 2:08 pm on Feb 16, 2011 (gmt 0)

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




msg:4268067
 6:10 pm on Feb 16, 2011 (gmt 0)

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




msg:4268382
 12:49 pm on Feb 17, 2011 (gmt 0)

>> 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




msg:4268405
 2:00 pm on Feb 17, 2011 (gmt 0)

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




msg:4268899
 8:51 am on Feb 18, 2011 (gmt 0)

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




msg:4268915
 9:08 am on Feb 18, 2011 (gmt 0)

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




msg:4269008
 1:41 pm on Feb 18, 2011 (gmt 0)

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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved