Problem with while loop

[flynismo]

Hey all,

What I am trying to do is create a search by zip code form. When the form is submitted, it will search for members in nearby cities within the given radius.

For example, I submit a form saying find me all members within a 20 mile radius of NYC. That should then display everyone in NYC, Bronx, Manhattan, etc...

Now, the actual radius part of my code is working properly; it is pretty accurate in estimating the distance from point A to point B.

But where I am having a problem is figuring out how to properly loop through the results.

How would I set up the loop, given the pseudo query below?

/* This query will pull data from the table containing zip codes, city / state names, etc */

$query = mysql_query(" SQL Query fetching all cities within 20 miles of current location ");
$result = mysql_fetch_assoc($query);

/* Now here is where I am lost. This query will need to get all the applicable cities contained in $result above, and then search a table that stores the user information for members who live in any of the cities above. 
NOTE: Both the tables being queried (the initial zipcode table, and then the following users table) have a column called "city". */

$user = mysql_query(' SELECT all FROM users_table WHERE city = $result ');

Thanks very much for any tips!

[GliderPilot]

Try this:

$city_query = mysql_query(" SQL Query fetching all cities within 20 miles of current location ");

while($current_city = mysql_fetch_assoc($city_query))
{

  $user_query = mysql_query("SELECT * FROM users_table WHERE city='" . $current_city['city'] ."'");

  while($current_user = mysql_fetch_assoc($user_query))
  {
    //you can do what ever you want to do with the users you get here
    echo $current_user['name'] . " lives in " . $current_city['city'] . ".<br />";
  }
}

[flynismo]

hmm, getting a strange result using the code above ---
what happens is that it is repeating the same username a bunch of times.

I think what is happening is that it just gets the first user in a matching city, and just keeps printing that same information out without searching the next city or scanning for more members other than the first result found...what it should do is search X-city and list all members in that city, and then move on to Y-city, list all it's members, etc..

[flynismo]

Would a foreach loop be more appropriate?

[GliderPilot]

That's odd, you shouldn't get the repeat. What do you get as output if you do the following:

$city_query = mysql_query(" SQL Query fetching all cities within 20 miles of current location ");

while($current_city = mysql_fetch_assoc($city_query)){

  $user_query = mysql_query("SELECT * FROM users_table WHERE city='" . $current_city['city'] ."'");

  echo "The following users live in " . $current_city['city'] ":<ul>";

  while($current_user = mysql_fetch_assoc($user_query)){
    //you can do what ever you want to do with the users you get here
    echo "<li>" . $current_user['name'] . "</li>";
  }

  echo "</ul><br /><br />";

}

[adam.adamski.96155]

It may well be possible to get all the things you want in one query, using the Join technique, but that is for someone else to tell you, have a look here :D
As for looping through the way you mentioned:

<?php
$cityArray = new array();
$sql = "SELECT city from table where radius <20miles";
if(!$res = mysql_query($sql)){
    echo mysql_error(); die();
} else {
    while ($row = mysql_fetch_assoc($res)){
        $user_sql = "select users from table where city='".$row['city']."'";
        if(!$user_res = mysql_query($user_sql)){
            echo mysql_error(); die();
        } else {
            while($user_row = mysql_fetch_assoc($user_res)){
                $cityArray[$row['city']] = $user_row['users'];
            }
        }
    }
}
//Now you have an array called $cityArray, the key being the city, the value being an array of users in that city:
foreach ($cityArray as $city => $userArr){
    echo "The city of $city has count($userArr) users living there:\n<br />";
    foreach($userArr as $user){
        echo $user."\n<br />";
    }
}

We really should be moving onto PDO or mysqli now:
Using PHP/PDO with error checking
Using PHP/MySQLi with error checking

I haven't tested the above code, so it may have a typo or two, but I hope it helps you to understand. :D

[flynismo]

Here is the output from the code you just listed (it is blank where the "lives in" part is)

> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33
> 
> 
> 
> The following users live in :
> 
>     cinch33

[LastMitch]

@flynismo

Can you post your table? It would be more accurate if there is a table than a query.

[flynismo]

Ignore my previous post -- I made an error.

Here is what it outputs:

The following users live in Baresville:

    cinch33



The following users live in Bowman Addition:

    cinch33



The following users live in Brushtown:

    cinch33



The following users live in Edgegrove:

    cinch33



The following users live in Fairview Drive:

    cinch33



The following users live in Gitts Run:

    cinch33



The following users live in Gnatstown:

    cinch33



The following users live in Grangeville:

    cinch33



The following users live in Green Springs:

    cinch33



The following users live in Hanover:

    cinch33



The following users live in Hershey Heights:

    cinch33



The following users live in Hobart:

    cinch33

[GliderPilot]

Does the city column in your zipcode table use a capital C "City" ??
if you add this before your $user_query it will give you the structure of the array:

var_dump($current_city);

[flynismo]

@adam,

I am currently using PDO, I'm just posting mysql psuedo queries since a lot of people may not be familar with PDO ...
I'll check out your link in just a moment

[flynismo]

@lastmitch,

Here is the table structures:

LOCATION table:

id, code, city, state, latitude, longitude

USERS table:

id, username, city, state, zip, lat, long

[LastMitch]

Now, the actual radius part of my code is working properly; it is pretty accurate in estimating the distance from point A to point B.

If you are gonna search a zip code, shouldn't there be a preg_match() function when you submited the form? Right now, it won't work because your zip code is not matching from the form. You're not matching the zip code to the nearest instead you are just fetching the zip code. I thought your intention is to find me all members within a 20 mile radius of NYC

[LastMitch]

There will be 2 querys base on your table.

The first query is the usertable because the person is searching the zip code.

The second query is the locationtable because it will gather the zip code.

[flynismo]

@lastmitch,

Sorry for the confusion: I am actually using the latitude and longitude coordinates to determine the physical location of nearby cities. I only have the zip codes in the table for other scripts on the site.

[LastMitch]

@flynismo

I am actually using the latitude and longitude coordinates to determine the physical location of nearby cities.

Do you have like an ititial point?

Since you are using latitude and longitude coordinates then the query should be something like this:

This query is to verify the zipcode:

$query = "SELECT zip, lat, lon FROM USERS WHERE zip = '$id'";

This query is to gather the coordinate:

$query = "SELECT id, code, city, state, latitude, longitude FROM LOCATION INNER JOIN USERS ON LOCATION.code = USERS.zip";

What you can do is that you can still used adam code because it's gonna be the same format.

[GliderPilot]

Coverted into PDO with Error catching, the duplicates may have been caused by not finding any users with that city:

$cityQuery = 'SELECT Query fetching all cities within 20 miles of current location';

$try { 
  $cityResult = $pdo->query($cityQuery);
}
catch (PDOException $error_msg)
{
  echo "The following query caused an error: $cityQuery<br />Error: ";
  echo $error_msg->getMessage();
}

if($cityResult->rowCount() > 0)
{
  while($cityRow = $cityResult->fetchObject())
  {

    echo "The following user live in " . $cityRow->city . ": <br />";

    $userQuery = 'SELECT * FROM users_table WHERE city="' . $cityRow->city . '"';

    $try { 
      $userResult = $pdo->query($userQuery);
    }
    catch (PDOException $error_msg)
    {
      echo "The following query caused an error: $userQuery<br />Error: ";
      echo $error_msg->getMessage();
    }

    if($userResult->rowCount() > 0)
    {
      while($userRow = $userResult->fetchObject())
      {
        echo $userRow->username;
        echo "<br />";
      }
    }
    else
    {
        echo "Sorry, no users found";
    }
  }
}
else
{
  echo "Sorry, we didn't find any cities close to you";
}

[flynismo]

@lastmitch,

I'm really glad that you asked me about the zip, because it made me realize that I forgot to include the zip code as part of the query.

The reason why I started the thread is because I had this script working properly, with the exception that it repeated the data over and over again.
I thought it was something wrong with the way I was looping, when in reality, the problem was that I did not consider the fact that two different states can have a city with the same name in it (Hanover, Pa and Hanover, MD for example)...so what I had to do was simply include the zip code to the WHERE clause when selecting from the user table.

Just in case this will help anyone out somehow, here is the final, working psuedo code:

    $q = mysql_query(" QUERY THAT FETCHES NEARBY CITIES ");

    while($radius = mysql_fetch_assoc($q))
    {
        $z = mysql_query("SELECT USER INFORMATION FROM USERS TABLE WHERE city = '".$radius['city']."' AND zip = '".$radius['zip']."'");
        while($z1 = mysql_fetch_assoc($z))
        echo $z1['username'] . $z1['city'] .', ' .$z1['state']. '<br>';  
    }