MySQL query based on array data

[jay.barnes]

Hello!

 

I've kind of hit a wall, here, and may need some help from some of the more seasoned veterans of MySQL, here

 

I have a table with > 10,000 records, like such, as an example:

 

|Name |Luggage |Origin_Country |Origin_State |Origin_City   |Dest_Country   |Dest_State |Dest_City |
|__________________________________________________________________________________________________|
|Bob  |CO      |USA            |ME           |Portland      |USA            |RI         |Providence|
|Jack |Trunk   |CAN            |ON           |Toronto       |USA            |TX         |Dallas    |
|Mary |Handbag |CAN            |BC           |Vancouver     |CAN            |ON         |Timmons   |
|Ed   |Backpack|USA            |AZ           |Phoenix       |USA            |MI         |Detroit   |
|Jen  |Trunk   |USA            |FL           |Orlando       |CAN            |NS         |Halifax   |
|Rich |CO      |CAN            |NS           |Amherst       |USA            |CT         |Hartford  |
____________________________________________________________________________________________________

 

 

And, I've also put together a form, to search the table, allowing users to select multiple values for a field:

 

Luggage:
[] CO    [] Trunk     [] Handbag     [] Backpack

ORIGIN
[] CAN
[]ALL CAN STATES
[]AB []BC []MB []NB []NL []NS []NT
[]NU []ON []PE []QC []SK []YT
[] USA
[]All USA states
[]AK []AL []AR []AZ []CA []CO []CT []DC []DE []FL []GA []HI []IA []ID []IL []IN []KS
[]KY []LA []MA []MD []ME []MI []MN []MO []MS []MT []NC []ND []NE []NH []NJ []NM []NV
[]NY []OH []OK []OR []PA []RI []SC []SD []TN []TX []UT []VA []VT []WA []WI []WV []WY
[] MEX
[]All MEX states
[]AGS []BCN  []BCS []CAM []CHIH []CHIS []COAH
[]COL []DF   []DGO []GRO []GTO  []HGO  []JAL
[]MEX []MICH []MOR []NAY []NL   []YUC  []ZAC


DESTINATION COUNTRY
[] CAN
[]ALL CAN STATES
[]AB []BC []MB []NB []NL []NS []NT
[]NU []ON []PE []QC []SK []YT
[] USA
[]ALL USA STATES
[]AK []AL []AR []AZ []CA []CO []CT []DC []DE []FL []GA []HI []IA []ID []IL []IN []KS
[]KY []LA []MA []MD []ME []MI []MN []MO []MS []MT []NC []ND []NE []NH []NJ []NM []NV
[]NY []OH []OK []OR []PA []RI []SC []SD []TN []TX []UT []VA []VT []WA []WI []WV []WY
[] MEX
[]ALL MEX STATES
[]AGS []BCN  []BCS []CAM []CHIH []CHIS []COAH
[]COL []DF   []DGO []GRO []GTO  []HGO  []JAL
[]MEX []MICH []MOR []NAY []NL   []YUC  []ZAC

 

Now, how do I phrase a MySQL query to search through a table, using the contents of the arrays that the checkboxes return?  I was thinking of using a "foreach" statement for every condition, but, with the potential complexity of such a search, up against a table with so many records it seems to time out PHP after 30 seconds- I'm just thinking that there may be a more efficient way to construct the query and its parameters.

 

Anybody have any suggestions?

 

Thank you!

[fenway]

Well, your table isn't normalized... but regardless, you'll have to determine which fields to search based on the criteria provided, and optimize accordingly.

[suresh64633]

Convert your checkbox array into commo separated values and than search these values in Table.