I am trying to do something I really do not understand how to do: reuse the same MySQL query with different results.
I have a map that I've scripted in jQuery MapHilight, so that when you mouse over a state, it highlights, and when you click it, it opens a modal box. Now what I want to do is populate the modal box with all the stores in that state.
So I have a MySQL database with several tables: most importantly, "Store Descriptions" and "Locations" (Linked to "Store Descriptions" via a foreign key.) The tables are named "store_descriptions" and "store_locations" respectively. So my query says this:
SELECT store_descriptions.store_name, store_descriptions.store_link, store_descriptions.locat_id, store_locations.locat_location, store_locations.locat_id FROM store_descriptions, store_locations WHERE store_descriptions.locat_id = store_locations.locat_id
All fine and dandy. This gives me a result set where I can see all the stores and their locations. I can make a new query by adding "AND store_descriptions.locat_id = 6". (6 is, say, Louisiana.) This shows me all the stores in Louisiana, and I can use it to populate the modal box for Louisiana.
However, I cannot do this 50 times! It puts such a drain on my database that the page takes forever to load. Is there a simpler way to do this? I'd really appreciate even just being shown the direction to go in learning how to solve this problem.