0

So this has been driving me nuts for several days. I've tried THIS and THAT but nothing is working. Here's the basic rundown:

I have a MySQL database with 200 or so locations. The user enters their zip code, and I want to give them 6 random locations within 100 miles of home. Everything works great, except I can't get it to limit the results to 6. It will give me ALL the results for <100. When I try the code below (adding the for ($x=0...) bit), I get the same result, only each one repeats 6 times before listing the next one. Here's the pertinent code that returns all the locations. Any help would save me from throwing my computer out the window.

$sql = "SELECT * FROM locations ORDER BY RAND()"; 
$result = mysql_query($sql, $dbase2);

while ($row = mysql_fetch_array($result))
    {

$city = $row['city']; 
$id= $row['id'];
$miles = calculateDistance($lat, $lon, $point2_lat,$point2_lon); 

  if ($miles < "100")
      { for ($x=0; $x<6; $x++){
       echo $city . " is about " . round($miles) . " miles away.<br />";
         };
      };           
  };

Like I said, for the sake of this post, I tried to pare it down to the important bits. Let me know if something vital is missing from my example.

Community
  • 1
  • 1
travallia_jeff
  • 88
  • 6
  • Looks like you are just looping six times and printing the same thing six times for every city within 100 miles. – Tristan Jun 25 '14 at 23:26
  • Sidenote: change `if ($miles < "100") {...}` to `if (intval($miles) < 100) {...}` – algorhythm Jun 25 '14 at 23:27
  • selecting everything by order rand() and calculating in php will be slow. if possible, calculate the distance on the db. see https://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points – FuzzyTree Jun 25 '14 at 23:29
  • FuzzyTree, thanks for the link. My db will max out around 300-400 locations, so I think rand() should be ok, no? So far, it's plenty fast enough. – travallia_jeff Jun 26 '14 at 01:14

1 Answers1

2

The following change should do it:

$count = 0;
while (($row = mysql_fetch_array($result)) && ($count < 6))
    {

    $city = $row['city']; 
    $id= $row['id'];
    $miles = calculateDistance($lat, $lon, $point2_lat,$point2_lon); 

    if ($miles < 100.0) {
       echo $city . " is about " . round($miles) . " miles away.<br />";
       $count = $count + 1;
      };           
  };

There are more elegant ways...

Floris
  • 45,857
  • 6
  • 70
  • 122
  • I see what you did there. Seems so simple now that I see it. Works like a charm! Only correction is that line 2 is missing some containers: `while (($row = mysql_fetch_array($result)) && ($count < 6))` Thanks. – travallia_jeff Jun 26 '14 at 01:50