SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    Washington DC
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need Help: Reusing MySQL Query

    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. #2
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    Aveiro, Portugal
    Posts
    164
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You can perform the query with AJAX only when the user clicks on the state.
    Yours truely
    Mário Ramos

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    Washington DC
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by TMacFarlane View Post
    Gosh! I know less about AJAX than I do MySQL...
    and yet you're happily using jquery...




    Quote Originally Posted by TMacFarlane View Post
    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?
    something like that, yeah
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    Washington DC
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do I make an AJAX call?

  6. #6
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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. #8
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    Washington DC
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    SitePoint Enthusiast
    Join Date
    Jul 2008
    Location
    Washington DC
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TMacFarlane View Post
    As it is, when using Ajax, it only registers the first overlay (jqModal.)
    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. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Call a single PHP script, with 1 of 50 different arguments in the url.

    stores.php?state=NY


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •