PHP - output result in ascending order of while() loop

Question

I need to show the result based on the calculations inside of a loop. The result of loop should be ascending order by $distance.

    $sql = "SELECT DISTINCT * FROM cinemas WHERE city='$city'";

    $result = $conn->query($sql);

    if ($result->num_rows > 0) {

        while($row = $result->fetch_assoc()) {



    $lat1 = $_GET['lat'];
    $lon1= $_GET['lon'];
    $lat2 = $row['latitude'];
    $lon2 = $row['longitude'];

    //starting calculating the distance

      $theta = $lon1 - $lon2;
      $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
      $dist = acos($dist);
      $dist = rad2deg($dist);
      $miles = $dist * 60 * 1.1515;
      $unit = $miles * 1.609344;

    $distance = substr($unit,0,4);
echo $row['cinemaname'].$distance;

}}

How to show the result in ascending order based on the $distance?

It shows as:

cinema name 20 km
cinema name 5 km
cinema name 30 km
cinema name 3 km

I need to show in:

cinema name 3 km
cinema name 5 km
cinema name 20 km
cinema name 30 km

save the values in one array and later sort the array – Rafael Shkembi Aug 29 '16 at 08:44 — Rafael Shkembi, Aug 29 '16 at 08:44
Thanks for your comment but I have no idea how to do that – J. Doe Aug 29 '16 at 08:45 — J. Doe, Aug 29 '16 at 08:45

Provie9 · Answer 1 · 2016-08-29T08:51:04.133

2

Save the return in the while in a array like this:

$cinema[$i]['cinemaname'] = $row['cinemaname'];
$cinema[$i]['distance'] = $distance;

and make after the while this:

function sortByOrder($a, $b) {
    return $a['distance'] - $b['distance'];
}

usort($cinema, 'sortByOrder');

now you have your order :)

This is your code now:

$sql = "SELECT DISTINCT * FROM cinemas WHERE city='$city'";

$result = $conn->query($sql);
$cinema = array();
$i = 0;
if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
       $lat1 = $_GET['lat'];
       $lon1= $_GET['lon'];
       $lat2 = $row['latitude'];
       $lon2 = $row['longitude'];

       //starting calculating the distance

       $theta = $lon1 - $lon2;
       $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
       $dist = acos($dist);
       $dist = rad2deg($dist);
       $miles = $dist * 60 * 1.1515;
       $unit = $miles * 1.609344;

       $distance = substr($unit,0,4);
       $cinema[$i]['cinemaname'] = $row['cinemaname'];
       $cinema[$i]['distance'] = $distance;
       $i++;
    }
}

function sortByOrder($a, $b) {
   return $a['distance'] - $b['distance'];
}

usort($cinema, 'sortByOrder');

edited Aug 29 '16 at 08:51

answered Aug 29 '16 at 08:45

Provie9

379
2
14

thanks for the answer, but shows empty page. What should I echo here? When I wrote echo to $cinema[$i]['distance'] it showed me not in a ascending order... – J. Doe Aug 29 '16 at 09:54
what is the output from print_r($cinema) after the usort? – Provie9 Aug 29 '16 at 10:01
`Array ( [0] => Array ( [cinemaname] => 44 [distance] => 2.00 ) [1] => Array ( [cinemaname] => 43 [distance] => 2.34 ) [2] => Array ( [cinemaname] => 47 [distance] => 5.61 ) [3] => Array ( [cinemaname] => 41 [distance] => 6.08 ) [4] => Array ( [cinem...` – J. Doe Aug 29 '16 at 18:12
I need to echo it/print_r it inside of the loop not outside. – J. Doe Aug 29 '16 at 18:13
the usort and print_r($cinema) should be presented inside of the loop because the name of the cinema is inside but the distance is outside now – J. Doe Aug 29 '16 at 19:32

score 0 · Answer 2 · answered Aug 29 '16 at 09:49

0

    $sql = "SELECT DISTINCT * FROM cinemas WHERE city='$city'";



       $result = $conn->query($sql);
    $distanceArray=array('');
$i=0;
        if ($result->num_rows > 0) {

        while($row = $result->fetch_assoc()) {



    $lat1 = $_GET['lat'];
    $lon1= $_GET['lon'];
    $lat2 = $row['latitude'];
    $lon2 = $row['longitude'];

    //starting calculating the distance

      $theta = $lon1 - $lon2;
      $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
      $dist = acos($dist);
      $dist = rad2deg($dist);
      $miles = $dist * 60 * 1.1515;
      $unit = $miles * 1.609344;

    $distance = substr($unit,0,4);
    $distanceArray[$i]=$row['cinemaname'].$distance;
$i++;
}}
sort($distanceArray);
foreach($distanceArray as $nameDistance)
{
echo $nameDistance; echo '<br>';
}

answered Aug 29 '16 at 09:49

Anish Abraham

255
4
20

Thank you Anish, but what if I have two loops? A while loop inside of the while loop, because there is two selections from two tables... – J. Doe Aug 29 '16 at 10:03
there are 3 tables: `$sql = "SELECT DISTINCT cinemaname,id FROM cinemas WHERE city='$city' "; $sql2 = "SELECT DISTINCT cinemaname,id,logo FROM cinema WHERE id = '$cinemaname'"; $sql3 = "SELECT DISTINCT * FROM moviecinemashowsassociation WHERE cinemaid = '$cinemaid' AND movieid='$movieid' AND showdate='$today' ";` – J. Doe Aug 30 '16 at 13:54
insert code in between two wile loop , and take array in out side `$theta = $lon1 - $lon2; $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); $dist = acos($dist); $dist = rad2deg($dist); $miles = $dist * 60 * 1.1515; $unit = $miles * 1.609344; $distance = substr($unit,0,4); $distanceArray[$i]=$row['cinemaname'].$distance;` – Anish Abraham Aug 31 '16 at 06:30
but there are 3 loops... please let me know how I can contact you? Your code works but still in 3 loops I getting confused how to use it... – J. Doe Aug 31 '16 at 20:02
you insert the needed condition in while loop `while( ){while( ){while( ){ $theta = $lon1 - $lon2; $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); $dist = acos($dist); $dist = rad2deg($dist); $miles = $dist * 60 * 1.1515; $unit = $miles * 1.609344; $distance = substr($unit,0,4); $distanceArray[$i]=$row['cinemaname'].$distance; }}}` – Anish Abraham Sep 01 '16 at 10:05

score 0 · Answer 3 · answered Jan 14 '17 at 12:37

0

You can also write a MySQL function to calculate the distance and just query it in ascending order. something like this:

$sql = "SELECT DISTINCT *, distance($lat1,$lon1) AS dist  FROM cinemas WHERE city='$city' ORDER BY dist ASC";

Of course you have to write the corresponding MySQL function beforehand.

answered Jan 14 '17 at 12:37

meteorite

736
1
8
17

You can check this to calculate the distance in SQL: http://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql – meteorite Jan 14 '17 at 12:40

PHP - output result in ascending order of while() loop

3 Answers3