PHP MySQL Search Form with Multiple Categories of Multiple Checkboxes

Question

This seems too straightforward as first to be a problem - but here is is anyway.

Lets say you have a search form, and you're looking for companies which deal in certain branches of an industry. The form will present a set of set of checkboxes for each question, and the user may choose any amount they want.

What are your favorite colors?

[ ] Red

[ ] Blue

[ ] Yellow

What are your favorite fruits?

[ ] Apple

[ ] Orange

[ ] Banana

Problem No.1

If the user checks red and blue - do we pull from the database rows that match only both? Or do we include rows that also match just red or just blue? In my mind the end-user may expect to see one outcome or the other, or both!

Problem No.2

Baring in mind that the CMS I have to use stores data in arrays for each question. So Company A for the first question may hold values "Red|Blue" or "Red" or "Red|Blue|Yellow" etc - plus the fast that the form I have has 10 categories each with at least 5 checkboxes - how can I translate this into an efficient and optimised query without having to resort to if/else mish mash?

Any help much appreciated.

Colin Fine · Accepted Answer · 2011-10-24 13:19:18Z

1

It's very likely to be a conjunction (red or blue) not a disjunction (red and blue),, but it depends on the semantics of the question. If you give options that can co-occur, then you might need a disjunction.
I can't see a better way than something like

$colours = $POST["colour[]"];
$items = array();

foreach ($colours as $colour) {

$items[] = "'$colour' IN colours";

}

$SQL .= "AND (".implode(' OR ', $items).")";

answered Oct 24, 2011 at 13:19

Colin Fine

3,3641 gold badge22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ka_lin · Accepted Answer · 2011-10-24 13:34:20Z

0

For P2, I would make a table "many-to-many", and would use a query with IN statement. For P1,something like ex: red,blue,white

select id,color,count(color) as occurrences from table inner join table_attributes where color in ('red','blue','white') order by occurrences

based on the number of occurrences you can sort the results

edited Oct 24, 2011 at 13:34

answered Oct 24, 2011 at 13:21

ka_lin

9,4226 gold badges39 silver badges58 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Search Form with Multiple Categories of Multiple Checkboxes

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related