[SOLVED] Here's a doozy for a php expert - select distinct cityname with php

[foreverdita]

OK, how best to explain this dilemma.

 

I have a database of zipcodes, lats, longs, etc.

 

I have successfully programmed that when a user inputs their zipcode and selects their radius (i.e. 5 miles, 10 miles, etc.) it will pull all cities that are less than or equal to the distance the user inputs.

 

HOWEVER - right now, there are multiple zipcodes with the same city name: i.e. New York city has about 15 different zip codes, so New York City appears 15 times.

 

My query in php to extract the data for distance relies on first:

 

select * the cities within the state that the user's zip code resides in.

 

Then, calculate the distance.

 

If the distance is less than radius, write the city name.

 

however, my list appears like this with my query.

 

New York

New York

New York

Yonkers

etc.

 

QAnyone know how to only display the city name one time?

[akitchin]

it would help to see your actual code, including the query, to determine how you're handling the data. if you're stuffing all the cities into an array and then display that, you can whittle it down to distinct cities by using array_unique.

[smerny]

SELECT DISTINCT city....

[foreverdita]

OK, I obtain the users coordinates, so I have two variables called $mylat and $mylon, as well as the state that matches their zipcode - called $thestate

 

 

 

I then hit the database with this:

 

$sqlqueryaccountra = "select * from zipcodes where st = '$thestate' order by city";
$resultaccountra = mysql_query($sqlqueryaccountra);
while ($row = @mysql_fetch_array ($resultaccountra)) 
{ 
$theirlat = $row['lat'];
$theirlon = $row['lon'];
$cityname = $row['city'];

$theusearea = distance($mylat, $mylon, $theirlat, $theirlon, "m");
if ($theusearea < $myradius) {

echo $cityname." - ".$theusearea."<br>";
}

}

 

Which does return the correct cities and distance, but also returns the same city name multiple times if the city has more than one zipcode.  Here is an example of the return based on a distance of 50 miles from zipcode 15222:

 

 

 

OLIVER - 39.615807861175
PARKER - 48.34820959569
PENN - 20.427333518102
PERRYOPOLIS - 27.83376350261
PETROLIA - 43.298564695736
PITCAIRN - 12.04019375759
PITTSBURGH - 3.3755447689099
PITTSBURGH - 5.3775272893544
PITTSBURGH - 1.6702588492031
PITTSBURGH - 3.3139908881765
PITTSBURGH - 3.8403015937219
PITTSBURGH - 4.6566443197142
PITTSBURGH - 4.5017561920602
PITTSBURGH - 5.4400028857247
PITTSBURGH - 4.0524847328842
PITTSBURGH - 2.4202903029092
PITTSBURGH - 1.0875065296215

[foreverdita]

SELECT DISTINCT city....

Unfortunately, that returns only one row.

[akitchin]

to do what you're wanting it to do, you'll want to plug the results into an array rather than echoing them in the while() loop, and then running array_unique against the results:

 

$sqlqueryaccountra = "select * from zipcodes where st = '$thestate' order by city";
$resultaccountra = mysql_query($sqlqueryaccountra);
$cities = array();
while ($row = @mysql_fetch_array ($resultaccountra))
{
$theirlat = $row['lat'];
$theirlon = $row['lon'];
$cityname = $row['city'];

$theusearea = distance($mylat, $mylon, $theirlat, $theirlon, "m");
if ($theusearea < $myradius) {

$cities += array($theuserarea => $cityname);
}

}

$unique_cities = array_unique($cities);
if (empty($unique_cities))
{
  foreach($unique_cities AS $distance => $city)
  {
    echo $city.' - '.$distance.'<br />';
  }
}
else
{
  echo 'No cities found! You are all alone!';
}

[foreverdita]

Thank you for the code help - however, now I only get the you are all alone message - not any results?!

[kickstart]

Hi

 

I take it you are calculating the distance in MySQL.

 

I would have a subselect that returns the city name and the distance, then from that SELECT CityName, MIN(distance) AS CityDistance FROM (somesubselect) Derviv1 GROUP BY CityName ORDER BY CityDistance.

 

All the best

 

Keith

[akitchin]

whoops - there should be a ! before the empty():

 

if (!empty($unique_cities))

[foreverdita]

Hmmm.  Akitchin, I now get the first city name returned, but no distance returned and no other rows:

Here is the code plugged in:

 

 

$sqlqueryaccountra = "select * from zipcodes where st = '$thestate' order by city";
$resultaccountra = mysql_query($sqlqueryaccountra);
$cities = array();
while ($row = @mysql_fetch_array ($resultaccountra))
{
$theirlat = $row['lat'];
$theirlon = $row['lon'];
$cityname = $row['city'];

$theusearea = distance($mylat, $mylon, $theirlat, $theirlon, "m");
if ($theusearea < $myradius) {

$cities += array($theuserarea => $cityname);
}

}

$unique_cities = array_unique($cities);
if (!empty($unique_cities))
{
  foreach($unique_cities AS $theuserarea => $city)
  {
    echo $city.' - '.$theuserarea.'<br />';
  }
}
else
{
  echo 'No cities found! You are all alone!';
}

[foreverdita]

Hi

 

I take it you are calculating the distance in MySQL.

 

I would have a subselect that returns the city name and the distance, then from that SELECT CityName, MIN(distance) AS CityDistance FROM (somesubselect) Derviv1 GROUP BY CityName ORDER BY CityDistance.

 

All the best

 

Keith

Hi Keith - I am actually calculating distance with a php function on the page.  I then use the distance to call out the range of cities that fall under the distance limit.

[akitchin]

Hmmm.  Akitchin, I now get the first city name returned, but no distance returned and no other rows:

Here is the code plugged in:

 

 

$sqlqueryaccountra = "select * from zipcodes where st = '$thestate' order by city";

$resultaccountra = mysql_query($sqlqueryaccountra);

$cities = array();

while ($row = @mysql_fetch_array ($resultaccountra))

{

$theirlat = $row['lat'];

$theirlon = $row['lon'];

$cityname = $row['city'];

 

$theusearea = distance($mylat, $mylon, $theirlat, $theirlon, "m");

if ($theusearea < $myradius) {

 

$cities += array($theuserarea => $cityname);

}

 

}

 

$unique_cities = array_unique($cities);

if (!empty($unique_cities))

{

  foreach($unique_cities AS $theuserarea => $city)

  {

    echo $city.' - '.$theuserarea.'<br />';

  }

}

else

{

  echo 'No cities found! You are all alone!';

}

 

 

 

first - please use code tags in your posts. it makes things much easier to read through.

 

second, the issue is with your variable spelling. you're assigning and using "usearea" in the calculation and the if() statement below it, then any reference i'm using is "userarea." reconcile the two and take another stab.

[kickstart]

Hi

 

This is a MySQL way of getting distance from a longitude / latitude in MySQL:-

 

http://blog.peoplesdns.com/archives/24

 

Think this way of doing it in SQL would work (not tested but hopefully will give you an idea). Saves messing around with php arrays, etc:-

 

SELECT city, MIN(distance) AS CityDistance 
FROM (select city, acos(SIN( PI()* $mylat /180 )*SIN( PI()*lat/180 )
)+(cos(PI()* $mylat /180)*COS( PI()*lat/180) *COS(PI()*lon/180-PI()* $mylon /180)
)* 3963.191 AS distance
FROM zipcodes
HAVING distance < 15) Deriv1
GROUP BY city 
ORDER BY CityDistance

 

All the best

 

Keith

 

 

[foreverdita]

Thank you again akitchin - I am now getting a return set, however it is still not showing all of the cities within the range anymore.

 

Example is zipcode 15222 - a Pittsburgh zipcode - running this query with your code returns the following result:

 

 

 

ACME - 35

ADAH - 39

ADAMSBURG - 20

ADRIAN - 41

ALEPPO - 49

ALIQUIPPA - 18

ALLENPORT - 25

ALLISON - 32

ALLISON PARK - 9

ALVERTON - 31

AMBRIDGE - 15

AMITY - 28

APOLLO - 23

ARMBRUST - 27

ARONA - 21

AVELLA - 26

BADEN - 17

BAIRDFORD - 14

BEAVER - 24

BELLE VERNON - 22

BESSEMER - 45

BETHEL PARK - 8

BEYER - 48

BLACK LICK - 42

BOBTOWN - 47

BOLIVAR - 44

BOYERS - 46

BRADDOCK - 7

BRADENVILLE - 37

BRADFORDWOODS - 13

BRANCHTON - 43

BRIER HILL - 33

BROWNSVILLE - 29

BULGER - 19

CADOGAN - 30

CARDALE - 34

CARMICHAELS - 38

CARNEGIE - 5

CHESWICK - 12

CLAIRTON - 10

CONNELLSVILLE - 36

CORAOPOLIS - 11

DONEGAL - 40

GLENSHAW - 6

IRWIN - 16

PITTSBURGH - 3

 

Missing would be Butler, which has about 4 zipcodes and falls within the distance set by the test user, and stops at Pittsburgh, which has about 25 zipcodes.  Other cities that fall after Pittsburgh that are missing from the results are Oakmont, Oakland, Verona, etc. to name a few?

[akitchin]

the reason is that any two cities with the same distance will overwrite each other. instead we can use the city as the index and overwrite it, which actually saves you the trouble of using array_unique:

$sqlqueryaccountra = "select * from zipcodes where st = '$thestate' order by city";
$resultaccountra = mysql_query($sqlqueryaccountra);
$cities = array();
while ($row = @mysql_fetch_array ($resultaccountra))
{
$theirlat = $row['lat'];
$theirlon = $row['lon'];
$cityname = $row['city'];

$theuserarea = distance($mylat, $mylon, $theirlat, $theirlon, "m");
if ($theuserarea < $myradius) {
if (isset($cities["$cityname"]) && $theuserarea < $cities["$cityname"])
{
   $cities["$cityname"] = $theuserarea;
}
else
{
   $cities += array($cityname => $theuserarea);
}
}

}

if (!empty($cities))
{
  foreach($cities AS $city => $theuserarea)
  {
    echo $city.' - '.$theuserarea.'<br />';
  }
}
else
{
  echo 'No cities found! You are all alone!';
}

 

this will overwrite the distance stored if it is less than one that was previously stored for the same city, and will add it to the list of cities if it hasn't previously been added. however, i would also give kickstart's suggestion a try and see how that works out.

[foreverdita]

Thank you to everyone who tried to help with this solution.  akitchin - you are brilliant.  It now returns the exact results I needed.  Thank you so very much.