SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot jsk137's Avatar
    Join Date
    Jan 2002
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using a result set (array) to do new query

    I think this is a lame question (sorry), but the antihistamines have my head in a fog.

    I am doing one query that returns a set of zipcodes. Now I want to use those results to display all the records in another table that match one of those zipcodes.

    I will appreciate your help, I can't even think of decent search phrase to hunt by.

    Thanks,
    Jeff

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You want to display all records from another table that matches each of those zipcodes? Or just one of them?

    I'm thinking you can just join the 2 tables.

  3. #3
    SitePoint Zealot jsk137's Avatar
    Join Date
    Jan 2002
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, each of those. I am reading about join queries now, but haven't cracked this yet.

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So you're going to tackle it yourself?

    Anyway, if you run into trouble, just let us know again!

  5. #5
    SitePoint Zealot jsk137's Avatar
    Join Date
    Jan 2002
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I always tackle it myself, but help from the board is always appreciated. I was serious about not being able to think clearly today (anthistamines), and I have to have this ready tomorrow.

    Also, I always post when I find a solution such that others can find an answer later in their search and the thread doesn't go w/o a conclusion.

  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want an example, then you should post your schema.

  7. #7
    SitePoint Zealot jsk137's Avatar
    Join Date
    Jan 2002
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the table definition for the first table (contains ~42,000 records):

    Code:
      CREATE TABLE ZIPCODE (
    zipcode mediumint(5) unsigned zerofill NOT NULL default '00000',
    state char(2) default NULL,
    city char(30) default NULL,
    lon float(7,6) default NULL,
    lat float(7,6) default NULL,
    PRIMARY KEY (zipcode)
    ) TYPE=MyISAM;
    This query:

    PHP Code:
     $results mysql_db_query(jsk137_dbpool,"Select * FROM ZIPCODE WHERE zipcode=$zip LIMIT 1"); 
    Gives me lat and lon reading from one record.

    I then perform a calulation on this as so and do another query to find all zip code matches in that range:

    PHP Code:
     $lon=mysql_result($results,$i,3);
    $lat=mysql_result($results,$i,4);
    $lon_range=abs($miles/(cos($lat)*69.172));
    $lat_range=$miles/69.172;
     
      
    $lowerLat=$lat-$lat_range;
    $upperLat=$lat+$lat_range;
    $lowerLon=$lon-$lon_range;
    $upperLon=$lon+$lon_range;
     
    $xresults mysql_db_query(jsk137_dbpool,"Select zipcode FROM ZIPCODE WHERE lon>=$lowerLon AND lon<=$upperLon AND lat>=$lowerLat AND lat<=$upperLat"); 
    NOW here is where I am:

    With this table (contains ~400 records):
    Code:
     CREATE TABLE users (
    ID int(11) NOT NULL auto_increment,
    name text NOT NULL,
    Street1 text NOT NULL,
    Street2 text NOT NULL,
    City text NOT NULL,
    State text NOT NULL,
    Zip text NOT NULL,
    Country text NOT NULL,
    Phone text NOT NULL,
    Fax text NOT NULL,
    EMail text NOT NULL,
    URL text NOT NULL,
    ContactName text NOT NULL,
    ContactPosition text NOT NULL,
    User1 text NOT NULL,
    User2 text NOT NULL,
    User3 text NOT NULL,
    DateEntered text NOT NULL,
    Approved text NOT NULL,
    PRIMARY KEY (ID)
    ) TYPE=MyISAM;
    
    I want to use the results from that last query (it lists the zipcodes that where within the range of miles). I need to use these results to display all the results from this table where there is a zip matches within the previous results.

    Thanks,
    Jeff




  8. #8
    SitePoint Zealot jsk137's Avatar
    Join Date
    Jan 2002
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This seems to be working as a replacement for that second query.

    PHP Code:
     $xresults mysql_db_query(jsk137_dbpool,"Select users.name FROM users INNER JOIN ZIPCODE ON ZIPCODE.zipcode = users.zip WHERE ZIPCODE.lon>=$lowerLon AND ZIPCODE.lon<=$upperLon AND ZIPCODE.lat>=$lowerLat AND ZIPCODE.lat<=$upperLat"); 


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
  •