Searching on multiple dropdowns

[dowzer]

I want to be able to allow a user to choose one option from three drop downs and then be able to return all matching products.

At the moment I have a form which collects the input and a single table in a database which lists each product along with the value 1 or 0 for each option.

e.g.
productname, producturl, colour_red, colour_green, colour_yellow, colour_blue, colour_all, size_small, size_medium, size_large, size_all

I set it up like this as it is possible for a product to be in more than one colour and more than one size and I couldnt think of a way of building a table which allowed me to have none, one or more colours, for example, in the table.

I considered using productname, producturl, colour_1, colour_2, colour_3 etc and then inserting red, green etc into the fields and leaving unused ones empty.

Neither of these seem to be logical or make any sense and both involve way too may nested selects etc which will be a killer for performance but I can't think how else to do it - I have used OSC derivatives a lot in the past and they have a seperate table called product attributes where they bring together the attribute with the product but I can't figure out how to implement that.

Any pointers? How would you approach this if you were doing it?

Thanks

Jase

[TheMadScientist]

Not sure if I'm missing something, because this seems fairly simple:

SELECT producturl FROM db_table WHERE productname=$selected_product AND $selected_color=1 AND $selected_size=1
if($result=mysql_fetch_array($query)) {
header('location: http://www.example.com/'.$result['producturl']);
}
else {
# Your 'Hey, we don't have that 'error' handler here
}

Obviously, that's the 'give you a point code' and is missing quite a bit of detail, such as scrubbing user input, etc. and isn't valid by any stretch, but it should hopefully give you an idea or two.

[dowzer]

Thank you for replying. I think I have done a crap job of explaining this :)

The problem is that a product might have 2 colour and 2 size options but the next product 4 might have 4 colour and 3 size options so how do I build a query on that?

First time in the loop it might have to search "where colour1='$choice' or colour2='$choice' but the second time round the loop it might have to search "where colour1='$choice' or colour2='$choice' or colour3='$choice' or colour4='$choice'"?

Does that make any sense?

[TheMadScientist]

Not sure what you're saying, because I don't know how there could be multiple size / color options from a drop down to be used in a single select statement?

You have a col in the db for color_all and size_all, and they would likely be options on the drop down (select) so how do you anticipate a user selecting more than one size or color option?

Using my example above, you would just set $selected_colour to colour_all and $selected_size to size_all when those options were selected...

I really don't understand how you see the drop down (select) working to allow users multiple options other than a specific color and size or all colors and sizes ... What am I missing?

[dowzer]

There is not multiple options for the user but there are multiple options in the database - the drop down will have each colour option listed in one ($solour) and then each size option listed in another ($size) BUT lets take two products as an example.

Product one is only available in red and size medium whereas product two is available in red, blue or green and in sizes small, medium, large or extra large. To complicate it further often something is available in red, blue or green but only size large in red, small, medium or large in blue and all sizes in green.

With that in mind how do I construct the database/tables to take this into account as there is no sensible way I can think of to have products listed with anywhere from one to four colour options and one to four size options.

I cannot have a table called products with colour and size as a field as there could be one or many entries - likewise I cannot really have a field called colour1, colour2, colour3, colour4 and so on.

At the moment I have each product with the actual colours and actual sizes listed as field names but there has got to be a better way of doing it to make searches easier without having to code a select statement for every single possible variant?

[TheMadScientist]

Uh, you do what I said in my first reply?

SELECT producturl FROM db_table WHERE productname=$selected_product AND $selected_color=1 AND $selected_size=1
if($result=mysql_fetch_array($query)) {
header('location: http://www.example.com/'.$result['producturl']);
}
else {
# Your 'Hey, we don't have that 'error' handler here
SELECT colour_red,colour_blue,colour_yellow,colour_green,size_small,size_med,size_large,size_xl FROM db_table WHERE productname=$selected_product

while($result=mysql_fetch_array($query)){
# Put some code here to figure out what size and color combinations you do have and then tell the visitor about those.
}

}

[dowzer]

My challenge is how best to set up the tables to make the queries most efficient, not so much the queries themselves...

[TheMadScientist]

Like you have them might be fine, imo ... You will probably actually lose select efficiency if you go to normalized, relational ... I always try to keep the selects as simple and straight forward as possible ... Of course it depends on your needs for future expansion too, but I would try to keep it to two or 3 tables at the most if you can. I like 1 table best myself, even if I have to store a bit more data or have some non-normalization present to do it.

One of the biggest questions is will you need to read / write often or mainly read? If you need to read / write frequently, it may be better to slow down the selects a bit and use normalization, because it's 'safer' and less prone to errors, but if you mainly want to read for speed, then keep it simple and straight forward with the minimal tables to do the job.

My .05¢ anyway.

[rocknbil]

My challenge is how best to set up the tables to make the queries most efficient, not so much the queries themselves...

Then don't limit the options themselves to columns in a single row . . . if you need to add some different option later, what then? You have to modify the database. see if this helps [webmasterworld.com].

[TheMadScientist]

I break all the rules as far as DBs go huh rocknbil? lol
They are screaminFast though... ;)

I'll let you two figure the rest of this out ... I don't post in here too often any more and actually forgot about this thread ... I do really do some 'different' things when it comes to DBs and selects though, so probably better to just follow rocknbil's advice and build it as flexible and relational.

[rocknbil]

Nah . . . it's just that you start off thinking, "I will never need more than "color and size, right?" If this turns out to be true, more simple is more efficient. Unfortunately, that theory has always been disproven in almost everything I do. Last week we'd completed a project that they swore that any more than three images in their slide show rotation would be too boring to the user, they would never use more than three, code it for three, it will be perfect. I coded it so they could add as many as they want, and set it up with three.

The request for four pictures in the rotation came in yesterday. They think I'm a star (which I'm not lol . . )

[TheMadScientist]

Yeah, I understand, and I usually code the same way, but have been known to do, uh, odd? things to keep away from relational tables ... They're a bit tough to explain, so I figured I would drop out, but let me see if I can give an example...

$key_color=$_POST['color'];
$key_size=$_POST['size'];
$key_prod_id=$_POST['prod_id'];

if($key_color=='red') {
$searched_color_code='r';
}
elseif($key_color=='yellow') {
$searched_color_code='y';
}

// etc.

if($key_size=='small') {
$searched_size_related_num='0';
}
elseif($key_size=='med') {
$searched_size_related_num='1';
}

// etc.

id | color | size / quan
1 | r!g!b!y | 0^1^4^2!1^2^0^3!0^4^1^3!0^0^0^0

SELECT color, size / quan FROM db_table WHERE id=$key_prod_id

if($result=mysql_fetch_array($query)) {
$colors=explode('!',$result['color']);
$find_sizes=explode('!',$result['size / quan']);
}
for($cnt=0;$cnt<count($colors);$cnt++) {
$$colors[$cnt]=explode('^',$find_sizes[$cnt]);
}

if($$searched_color_code[$searched_size_related_num]>0) {
$result='We have '.$$searched_color_code[$searched_size_related_num]].' '.$key_size.' widgets in the color '.$key_color;
}

I can stay flat-file, add another color, size and quan simply by adding another delimited value to color and size / quan & color or size definition ... They're a bit tougher to maintain, but I can get all the info I could want or need with a 1 row 2 col select and I really don't use very much 'space' to do it.

Basically, I do more with php and less with selects, because I've found so many times I want the rest of the information for a 'close' answer if I don't have an exact answer and I really dislike having to go back to the db, but like I said they're a bit 'different' to look after and work with, plus they can get really complicated ... lol.

Anyway, I guess you could say I use relational php sometimes rather than relational databases...