mysql query using array result of another query

Question

I have a simple query that finds cities that are within a certain distance of a given latitude and longitude.

$query = "SELECT city,state,((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM us_cities WHERE population<>'' HAVING distance<=30 ORDER BY distance ASC limit 1, 10";

And then displays the results:

$rows = mysqli_num_rows($result);           
for ($i = 0; $i < $rows; ++$i) {            
$row = mysqli_fetch_assoc($result);
echo $i+1;
echo $row['city'] . "<br />";
echo $row['state'] . "<br />";
}

What I want to do is instead of echoing the results, take that array and do another query that has information about those cities in another table and then echo the results. Is there a way I can do this w/o having to do 10 subqueries:

Select * FROM table2 WHERE city=city AND state=state;

How do I say "take the array of cities and states from table1, and select everything from table2 using that array?

You have to read about JOIN.

Viacheslav Kondratiuk
– Viacheslav Kondratiuk

2012-06-17 19:43:23 +00:00
Commented Jun 17, 2012 at 19:43 — Viacheslav Kondratiuk
– Viacheslav Kondratiuk, Commented Jun 17, 2012 at 19:43

Michael Berkowski · Accepted Answer · 2012-06-17 19:40:59Z

4

A simple INNER JOIN between the two will do the job:

SELECT 
  /* Need to add table name or alias in select list since both tables have these cols */
  us_cities.city,
  us_cities.state,
  ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance 
FROM
   us_cities
   /* Join on city and state */
   INNER JOIN table2 
      ON us_cities.city = table2.cities 
      AND us_cities.state = table2.state
WHERE population <>''
HAVING distance<=30
ORDER BY distance ASC 
limit 1, 10

answered Jun 17, 2012 at 19:40

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

whatdafrak Over a year ago

Perfect!!! It was timing out so added some indexes and it works great. Very good idea. Thank you

Collectives™ on Stack Overflow

mysql query using array result of another query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related