How do I do this? Repeat MySQL Query

Report · Jun 19, 2012

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.

P.S. I am scripting in PHP.

Report · Jun 19, 2012

You don't hardcode the state id in the WHERE clause. Instead, you use a either a variable or placeholder and pass the state id value in the querystring. Your PHP code then retrieves this value and uses that in the WHERE clause. To have DW code this for you, check out Master/Detail pages.

Report · Jun 20, 2012

Yes-- this is the direction I want to go in: a variable. But I cannot figure out how to code it, and everything I find online deals with forms. This isn't a form-- it is a jQuery function from an imagemap. I don't think I can make Master/Detail pages because the link and the overlay are all in the same document (tried using Ajax, but then only the first overlay would work.)

I tried getting Dreamweaver to create the variable, but it kept throwing syntax errors when done (which I could not debug.) So let's say that the variable will be called "location." I appended the query above with "AND store_descriptions.locat_id = location". Then I opened the recordset in DW and clicked the + Variable button. I gave it Name:location; Type: Integer; Default Value:-1; Runtime value:-1.

The code I got out of that (besides the "sprintf" statement,) is this:

$location_getStore = "-1";

if (isset(-1)) {

$location_geStore = -1;

}

And there is a syntax error on the second line, which I could not figure out.

Even so, suppose I get this snarl untangled, then what? What am I supposed to put into place?

So far, I have:

<?php do { ?>

<?php } while ($row_getStore = mysql_fetch_assoc($getStore)); ?>

I thought about saying:

... while ($row_getStore = mysql_fetch_assoc($getStore) && $location_geStore = 6); ?>

But this resulted in a blank overlay.

Does this make any sense? I feel so close, and yet so far...

Report · Jun 20, 2012

>This isn't a form-- it is a jQuery

>function from an imagemap.

Yes sorry, I forgot the context you were using this in.

>And there is a syntax error on the second line, which I could not figure out.

The isset() function only works with variables, hence the error. The way it is often used in DW is to check if a querystring or posted name/value is set. If it is set, then you would assign the variable to the value passed in from the form/querystring, otherwise it is left at the default value.

There are two piece that you need to complete. Neither is very difficult but I'm not a php programmer so I can't give you specific code except in small bits.

The first is to make sure that the imagemap link contains the querystring that has the state id in it.

>I appended the query above with "AND store_descriptions.locat_id = location

PHP variables need to begin with the $ so the correct code would probably be "AND store_descriptions.locat_id = $location". But I suspect you may have other problems so lets start my question regarding the link from the imagemap and work from there.

Report · Jun 21, 2012

> PHP variables need to begin with the $ so the correct code would probably be "AND store_descriptions.locat_id = $location".

This was just the variable name set up so that the Dreamweaver query wizard could make a runtime variable out of it. "location" was replaced by "$%" after the wizard ran.

I did use the $ in the mysql_fetch command above. That is not the cause for error here. After your suggestion above, I tried href="#louisianaTip?location=6", but again, nada.

So I took a different tack, and used AJAX, and this actually 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?

Report · Jun 21, 2012

>I did use the $ in the mysql_fetch command above. That is not the cause for error here.

>After your suggestion above, I tried href="#louisianaTip?location=6", but again, nada.

I'm confused at what you are doing here. I thought you had an image map and were opening a new page or popup with the details? Why are you using a named anchor?

>Still, there has got to be a better way.

Of course there is - I've already explained that. You'll need to include the complete code if you want help tweaking it. Providing only small snippets doesn't really tell us enough.