jay.barnes Posted August 14, 2009 Share Posted August 14, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/170325-mysql-query-based-on-array-data/ Share on other sites More sharing options...
fenway Posted August 21, 2009 Share Posted August 21, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/170325-mysql-query-based-on-array-data/#findComment-903193 Share on other sites More sharing options...
suresh64633 Posted August 21, 2009 Share Posted August 21, 2009 Convert your checkbox array into commo separated values and than search these values in Table. Quote Link to comment https://forums.phpfreaks.com/topic/170325-mysql-query-based-on-array-data/#findComment-903202 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.