Skip to main content
M
MadMac01
Participant
June 19, 2012
Question

How do I do this? Repeat MySQL Query

  • June 19, 2012
  • 1 reply
  • 1858 views

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.

This topic has been closed for replies.

1 reply

B
bregent
Participating Frequently
June 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.

M
MadMac01Author
Participant
June 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 { ?>

<li><a href="<?php echo $row_getStore['store_link']; ?>"><?php echo $row_getStore['store_name']; ?></a></li>

<?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...

B
bregent
Participating Frequently
June 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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices