Need Help: Reusing MySQL Query

PHP
1

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.

2

You can perform the query with AJAX only when the user clicks on the state.

3

Gosh! I know less about AJAX than I do MySQL… how do I go about doing that?

Shouldn’t I be able to do it in straight PHP/MySQL, if I assign the location a variable, and then call that variable with a value?

4

and yet you’re happily using jquery…

:slight_smile:

something like that, yeah

SELECT store_descriptions.store_name
     , store_descriptions.store_link
     , store_locations.locat_location
  FROM store_descriptions
INNER
  JOIN store_locations 
    ON store_locations.locat_id = store_descriptions.locat_id
 WHERE store_descriptions.locat_id = $loc

here $loc is the variable, that gets its value from the ajax call

5

How do I make an AJAX call?

6

jQuery makes AJAX really easy. Go to jQuery.com and check out the documentation section. There are a bunch of tutorials there on AJAX.

7

Your options boil down to these two.

a) Grab everything from the db, and send the lot (as js arrays probably) along with the html of the page.

b) Grab only what is needed but fetch the data as and when the user requires it. (Ajax)

Both have pros and cons to do with pages size, and dealing with delays.

A third option is a take on a) in that you cache all the js arrays in a separate stores.js file which the browser loads.

Why don’t you work out how to do it the slow but sure way first, if the size is prohibitive then break the job down.

Is someone looking for a store in an east coast state really going to jump to a west coast state?

8

I agree, that option a) is where I’ve already done the most work. I’ve looked into Ajax quite a bit in the meantime, but all I can find deals with form data. I am getting up to speed on it, but I do not think it is appropriate for this project–mainly because the entire query is run at the start. I already have all the data as a PHP object. I just need a runtime variable that can be called in each overlay div.

There is another wrinkle with using Ajax as well: the overlay plugin needs serious hacking to make work. As it is, when using Ajax, it only registers the first overlay (jqModal.)

I believe r937 has provided the MySQL variable I need. I just need to figure out how to inject it into the overlay div. Here is what I have already (gets the whole kit & kaboodle):

<?php do { ?>
<li><a href=“<?php echo $row_getStore[‘store_link’]; ?>”><?php echo $row_getProj[‘store_name’]; ?></a></li>
<?php } while ($row_getStore = mysql_fetch_assoc($getStore)); ?>

I tried …while ($row_getStore = mysql_fetch_assoc($getStore) && $loc=‘6’)

But it just returned an empty row.

Again, I appreciate the suggestion to go with Ajax, and I will experiment as I learn more–and when I work on a project that doesn’t require as much complex interactivity. But at this point, I am pretty sure I just need to figure out how to manipulate a variable in the query.

I also appreciate very much the feedback I’ve received on this project. I feel so close, yet so far away.

9

What I said yesterday kept me awake all night. Perhaps this is working as intended, I wondered. Rather than have more than one modal box, I just keep changing the AJAX call, and load it into one single DIV.

So I tried it today, and it worked. Rather than have fifity modal boxes, each with a separate ID, I made one modal box and used an AJAX callback for it. The downside, is now AJAX calls fifty different PHP files, each with an iteration of the MySQL query. Now, each area of the imagemap links to one of the PHP files. When it is clicked, it loads the PHP file–and the embedded query–into the modal box. This actually resulted in a much faster page load (only one DIV in the host file, and only the PHP file with the query loads when clicked), and it also makes for a more progressively enhanced imagemap (if the user does not use javascript, the PHP file loads anyway.)

Still, there has got to be a better way. There’s got to be a more economical alternative to fifty “include” files, where the only content difference is the name of the query and the number of the location ID. Any further thoughts?

10

Call a single PHP script, with 1 of 50 different arguments in the url.

stores.php?state=NY