MYSQL how to use joins!?

[farzher]

$ip = $_GET['ip'];
$array = array();

$result = mysql_query("SELECT ip_group_city.location
				  FROM ip_group_city
				  WHERE ip_start <= INET_ATON('$ip')
				  ORDER BY ip_start DESC LIMIT 1");
$row = mysql_fetch_array($result);

$location = $row['location'];
$result = mysql_query("SELECT *
				  FROM locations
				  WHERE locations.id='$location'
				  LIMIT 1");

$row = mysql_fetch_array($result);
$array = array_merge($array, array("city" => $row['city']));
$array = array_merge($array, array("zip" => $row['zipcode']));

$result = mysql_query("SELECT name
				  FROM fips_regions
				  WHERE code='".$row['region_code']."'
				  AND country_code='".$row['country_code']."'
				  LIMIT 1");

$row = mysql_fetch_array($result);

$array = array_merge($array, array("region" => $row['name']));

 

How can I get this down to 1 or 2 queries? I've tried a bunch of different thing but nothing works

It's an IP geolocation database that I downloaded btw.

HALP!

[kickstart]

Hi

 

Something like this should do it (not tested so might be a typo or 2)

 

SELECT a.ip_group_city.location, b.city, b.zipcode, c.name
FROM ip_group_city a
LEFT OUTER JOIN Locations b ON a.location = b.id
LEFT OUTER JOIN  c ON a.region_code = c.code AND a.country_code = c.country_code
WHERE ip_start <= INET_ATON('$ip')
ORDER BY ip_start DESC LIMIT 1

 

All the best

 

Keith

[farzher]

Hi

 

Something like this should do it (not tested so might be a typo or 2)

 

SELECT a.ip_group_city.location, b.city, b.zipcode, c.name
FROM ip_group_city a
LEFT OUTER JOIN Locations b ON a.location = b.id
LEFT OUTER JOIN  c ON a.region_code = c.code AND a.country_code = c.country_code
WHERE ip_start <= INET_ATON('$ip')
ORDER BY ip_start DESC LIMIT 1

 

All the best

 

Keith

 

LMAO a typo or two, there were a lot haha. but no worries only took a minute to fix. Thanks so much, I'll use this as a reference for future stuff

 

Here's the working version:

SELECT a.location, b.city, b.zipcode, c.name
FROM ip_group_city a
LEFT OUTER JOIN locations b ON a.location = b.id
LEFT OUTER JOIN fips_regions c ON b.region_code = c.code AND b.country_code = c.country_code
WHERE a.ip_start <= INET_ATON('$ip')
ORDER BY a.ip_start DESC LIMIT 1

 

thxthxthxthx