I have a table results with this fields:
- number_one (int)
- number_two (int)
- number_three (int)
- number_four (int)
- number_five (int)
Each number_* column can contain a number from 01 to 90.
I have a search form with 90 checkboxes. A user can tick up to 5 checkboxes.
Once an user select n checkbox, I need to build a query that look for a row that contains all those values but a number can be in any of the 5 columns.
an user ticks checkbox 02, 12 and 20. I need to retrieve all the rows with all three number but I a number can be in any of the number_* column. The 20 can be in column number_one, 02 could be in column number_three and 12 in column number_four. How can I build this query?
Also, would it be better to use one column instead of five columns? results table:
- numbers (string)
column numbers contains 02-12-20-57-84 where - is the separator.
Example: an user ticks checkbox 02, 12 and 20. so my query would be
SELECT * FROM results WHERE numbers LIKE "%02%" AND numbers LIKE "%12%" AND numbers LIKE "%20%";