SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Feb 2008
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Use Results from Query to conduct 2nd Query

    I first query a table called Points for all rows. This table holds x and y points in longitude and latitude. I then while loop the results to get each row. I then want to run a new query on a second table called segments in which I will use the stored x,y data as a condition or variable in the query. I can get this to run for one point but not for all the points. Can you use data from one table to use for a query in another table? I would try to use JOIN but the tables do not relate, I just need to evaluate each point, the problem is there are hundreds of points. Here is my code.

    $pointresults = mysql_query("SELECT * FROM points");
    $num_points = mysql_num_rows($pointresults);
    if($num_points == 0) {
    echo "No Points in Table, There is an Error!";
    }
    else {
    // get each row
    while($mypointrow = mysql_fetch_array($pointresults))
    {
    $thelon = $mypointrow['Longitude'];
    $thelat = $mypointrow['Latitude'];

    $result = "SELECT poly_id, Segment_id FROM segments WHERE ( longa > $thelng OR longb > $thelng )
    AND ( (lata > $thelat AND latb < $thelat )
    OR (latb > $thelat AND lata < $thelat ) )";

    $result2=mysql_query($result);
    $resultnumrows = mysql_num_rows($result2);

    Any help would be appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, they are related, so, yes, you can join them
    Code:
    SELECT s.poly_id
         , s.Segment_id 
      FROM segments AS s
    INNER
      JOIN points AS p
        ON ( s.longa > p.Longitude 
          OR s.longb > p.Longitude )
       AND ( (s.lata > p.Latitude AND s.latb < p.Latitude )
          OR (s.latb > p.Latitude AND s.lata < p.Latitude ) )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •