Question - Looping through queries

[Mr_Mako]

Hi...

I've been stuck on this for the last few weeks, and am no closer to a solution.

I have code where a user can select (through a POST menu) a departure airport, and then the code randomly generates a arrival airport. The database table that the airports reside in also includes latitude and longitude data for each airport. A function then calculates the distance between the two airports utilizing the latitude and longitude of each.

Included also is the code that randomly (or selectively by POST) grabs the data from the aircraft table for the aircraft selected, and this includes the maximum range of the aircraft.

What I need to accomplish is to run a query / statement that will take the aircraft range data, and then loop through the departure / arrival function until two airports are selected where the distance between them is LESS THAN the maximum range of the aircraft.

I have no idea how to code this to accomplish what I'm attempting to do.

I know that's odd based on my code below but I've never used or had the need to learn Loops or the like before... and I'm just not fully understanding how to implement or code one to do what I need. The possibility also exists that I may not even need a loop... it's been suggested to just run the whole thing through a While statement... would that work? I haven't tried that because it's my understanding that a While statement (like, "WHERE $range < '$ac_rng'") won't loop through the results the way I need them to.

My code for each is listed below :

// Create Aircraft Query //
$ac = "SELECT * FROM aircraft WHERE aircraft_id = '$_POST[aircraft]'";
$ac_result = @mysql_query($ac, $connect) or die(mysql_error());

while ($acrow = mysql_fetch_array($ac_result)) {
	$ac_id = $acrow['aircraft_id'];
	$ac_type = $acrow['aircraft_type'];
	$ac_name = $acrow['aircraft_name'];
	$ac_rng = $acrow['aircraft_range'];
}

// Create Departure Query //
$dep = "SELECT * FROM ref_airport WHERE ap_id = '$_POST[departure]'";
$dep_result = @mysql_query($dep, $connect) or die(mysql_error());

while ($dprow = mysql_fetch_array($dep_result)) {
	$departure_id = $dprow['ap_id'];
	$departure_icao = $dprow['ap_icao'];
	$departure_name = $dprow['ap_name'];
	$departure_lat = $dprow['ap_lat'];
	$departure_long = $dprow['ap_long'];
}

// Create Destination Query //
$destrand = rand(1, $destnum);

$destin = "SELECT * FROM ref_airport WHERE ap_id = '$destrand'";
$destin_result = @mysql_query($destin, $connect) or die(mysql_error());

while ($dsrow = mysql_fetch_array($destin_result)) {
	$destination_id = $dsrow['ap_id'];
	$destination_icao = $dsrow['ap_icao'];
	$destination_name = $dsrow['ap_name'];
	$destination_lat = $dsrow['ap_lat'];
	$destination_long = $dsrow['ap_long'];
}

// Create Distance Function //
distance($departure_long['longitude'], $departure_lat['latitude'], $destination_long['longitude'], $destination_lat['latitude']);

function distance($departure_lat, $departure_long, $destination_lat, $destination_long) {
	$dist = acos(sin(deg2rad($departure_lat))  // Calculates distance from latitude and longitude.
		* sin(deg2rad($destination_lat))
		+ cos(deg2rad($departure_lat))
		* cos(deg2rad($destination_lat))
		* cos(deg2rad($departure_long - $destination_long)));

	$dist   = rad2deg($dist);
	$miles  = (float) $dist * 69;
	$nm = $miles * 0.8696;

	$display  = sprintf("%0.2f",$nm).' Nautical Miles' ;

return $display ;
}

I'd really appreciate any suggestions on how I can accomplish this. This one bit of code is hanging up the whole project, and I'd really like to resolve this.

[bbreslauer]

A while loop is exactly what you need. You can create a while loop to go through each possibility, and then check each distance with an if statement. At the end of the if statement, you would use a "break;" (without the quotes) to get out of the while loop and continue with the program.

[Mr_Mako]

Ok... the only real possibility I need is just to find the distance between airports at a value less than the aircraft maximum range.

I'm not exactly certain I understand completely what your saying... I'm still fairly new to PHP in regards to the more complex functions. Could you by chance show me a quick example? I've checked the PHP manual for examples and am not sure how it correlates with my problem. I don't want you to write my code for me... that's not what I'm after, just something that shows me exactly how your suggestion translates to code.

[bbreslauer]

Ok, here's how it would look:

while (get result query here, like your other while loops) {
    $max_range;  // this is the max range of the aircraft
    $distance;  // this is the distance between the airports
    if ($distance <= $max_range) {
        //do something here, this is when the distance is less than or equal to the max range
        break;  // this is used to break out of the while loop
    }
}

[Mr_Mako]

Ok... I've been attempting to work on the code for this for a while now using your suggestion. What I've come up with is this :

// Database connection stuff here //

// Create Aircraft Query //
$ac = "SELECT * FROM aircraft WHERE aircraft_id = '3'";
$ac_result = @mysql_query($ac, $connect) or die(mysql_error());

while ($acrow = mysql_fetch_array($ac_result)) {
    $ac_id = $acrow['aircraft_id'];
    $ac_type = $acrow['aircraft_type'];
    $ac_name = $acrow['aircraft_name'];
    $ac_rng = $acrow['aircraft_rng'];
}

// Create Departure Query //
$dep_rand = rand(1, 13);

$dep = "SELECT * FROM ref_airport WHERE ap_id = '$dep_rand'";
$dep_result = @mysql_query($dep, $connect) or die(mysql_error());

while ($dprow = mysql_fetch_array($dep_result)) {
    $departure_id = $dprow['ap_id'];
    $departure_icao = $dprow['ap_icao'];
    $departure_name = $dprow['ap_name'];
    $departure_lat = $dprow['ap_lat'];
    $departure_long = $dprow['ap_long'];
}

// Create Destination Query //
$dest_rand = rand(1, 13);

$destin = "SELECT * FROM ref_airport WHERE ap_id = '$dest_rand' AND ap_id != '$departure_id'";
$destin_result = @mysql_query($destin, $connect) or die(mysql_error());

while ($dsrow = mysql_fetch_array($destin_result)) {
    $destination_id = $dsrow['ap_id'];
    $destination_icao = $dsrow['ap_icao'];
    $destination_name = $dsrow['ap_name'];
    $destination_lat = $dsrow['ap_lat'];
    $destination_long = $dsrow['ap_long'];

// Create Distance Function //
function distance($departure_lat, $departure_long, $destination_lat, $destination_long) {
    $dist = acos(sin(deg2rad($departure_lat))  // Calculates distance from latitude and longitude.
        * sin(deg2rad($destination_lat))
        + cos(deg2rad($departure_lat))
        * cos(deg2rad($destination_lat))
        * cos(deg2rad($departure_long - $destination_long)));

        $dist   = rad2deg($dist);
        $miles  = (float) $dist * 69;
        $nm = $miles * 0.8696;

        $display  = sprintf("%0.2f",$nm).' Nautical Miles' ;
        $distance = sprintf("%0.2f", $nm);

    return $display ;
}

distance($departure_long['ap_long'], $departure_lat['ap_lat'], $destination_long['ap_long'], $destination_lat['ap_lat']);

if ($distance < $ac_rng) {
	echo "Distance from $departure_icao ($departure_name) is ". distance($departure_lat, $departure_long, $destination_lat, $destination_long) ." : Aircraft Maximum range is $ac_rng.<br><br>";
	break;
}
}

Unfortunately, I'm not having much success. The code works to some degree... I get a departure airport and an arrival airport and the distance between the two is calculated correctly, but the distance isn't being held to less than 250 miles (aircraft ID #3 has a 250 mile maximum range). I'm fairly certain this is because of $distance variable.

Now my question is twofold :

First, how does one get the distance data from the function into a variable that my "if" statement can use? And second, is my code even correct to accomplish what I'm attempting to do?

[bbreslauer]

Well, first, your function "distance" shouldn't be inside the while loop. Second, you need to assign the distance() function output to some variable (probably $distance, since that's what you're using in the if statement). This means, of course, that you'll need to return $distance, and not $display, in your function.

What should be happening now is that the first distance calculated is being outputted. This is happening for two (related) reasons. First, $distance is not being defined, which means that it is set to a default (which i believe is null or 0, depending on how it is used). And while you use $distance in the function (which should be outside of the while loop, as I said above), $distance isn't defined as a global variable, so whatever is bound to it is being discarded after the function gets called. You can read more about this at php.net by searching for "variable scope".

[Mr_Mako]

Ok... now I'm really confused. Let me see if I'm understanding you correctly.

By what you're stating above, my

function distance(($departure_lat....

should remain outside of the while loop (again, if I'm understanding you right).

But, what I don't understand at that point is where I need to place the

if ($distance <= $ac_rng) {

The information I'm working with is coming from two separate tables... an aircraft data table (for the aircraft range) and an airports table (for the airport and lat / long data). In your example above it looks like you're suggesting a while loop that combines those two tables into a single query? Or is that not what you're getting at?

The $distance variable I created above was simply a variable I created to capture the results of the distance function without the "Nautical Miles" at the end. It didn't occur to me that it wouldn't pass without the return... so I changed it so that $distance is now returned in the function.

I apologize... I know this is probably a fairly easy thing to resolve, and I'm sure I'm going to beat my head on the desk once I figure it out... but for the life of me I'm not grasping this.

[bbreslauer]

Here's what the code above should look like:

 // Database connection stuff here //


// Create Distance Function //
function distance($departure_lat, $departure_long, $destination_lat, $destination_long) {
    $dist = acos(sin(deg2rad($departure_lat))  // Calculates distance from latitude and longitude.
        * sin(deg2rad($destination_lat))
        + cos(deg2rad($departure_lat))
        * cos(deg2rad($destination_lat))
        * cos(deg2rad($departure_long - $destination_long)));

        $dist   = rad2deg($dist);
        $miles  = (float) $dist * 69;
        $nm = $miles * 0.8696;

        $display  = sprintf("%0.2f",$nm).' Nautical Miles' ;
        $distance = sprintf("%0.2f", $nm);

    return $distance ;
}


// Create Aircraft Query //
$ac = "SELECT * FROM aircraft WHERE aircraft_id = '3'";
$ac_result = @mysql_query($ac, $connect) or die(mysql_error());

while ($acrow = mysql_fetch_array($ac_result)) {
    $ac_id = $acrow['aircraft_id'];
    $ac_type = $acrow['aircraft_type'];
    $ac_name = $acrow['aircraft_name'];
    $ac_rng = $acrow['aircraft_rng'];
}

// Create Departure Query //
$dep_rand = rand(1, 13);

$dep = "SELECT * FROM ref_airport WHERE ap_id = '$dep_rand'";
$dep_result = @mysql_query($dep, $connect) or die(mysql_error());

while ($dprow = mysql_fetch_array($dep_result)) {
    $departure_id = $dprow['ap_id'];
    $departure_icao = $dprow['ap_icao'];
    $departure_name = $dprow['ap_name'];
    $departure_lat = $dprow['ap_lat'];
    $departure_long = $dprow['ap_long'];
}

// Create Destination Query //
$dest_rand = rand(1, 13);

$destin = "SELECT * FROM ref_airport WHERE ap_id = '$dest_rand' AND ap_id != '$departure_id'";
$destin_result = @mysql_query($destin, $connect) or die(mysql_error());

while ($dsrow = mysql_fetch_array($destin_result)) {
    $destination_id = $dsrow['ap_id'];
    $destination_icao = $dsrow['ap_icao'];
    $destination_name = $dsrow['ap_name'];
    $destination_lat = $dsrow['ap_lat'];
    $destination_long = $dsrow['ap_long'];



$distance = distance($departure_long['ap_long'], $departure_lat['ap_lat'], $destination_long['ap_long'], $destination_lat['ap_lat']);

if ($distance < $ac_rng) {
    echo "Distance from $departure_icao ($departure_name) is ". distance($departure_lat, $departure_long, $destination_lat, $destination_long) ." : Aircraft Maximum range is $ac_rng.<br><br>";
    break;
}
}  

What I've done is moved the function definition outside of the while loop, changed what it returns, and add $distance= to the distance() call.

[Mr_Mako]

Sheesh. Ok, now I feel like an idiot.

This is the first time I've worked with functions and for some reason I thought that I had to have the function code itself below the variable queries or else it wouldn't work correctly (NULL and / or empty variable problems).

I've changed things around like you suggested though... but I'm still getting the problem where the aircraft max range is exceeded and not held to. Just for some checking I placed an "echo" (echo "$distance"😉 immediately below the $distance variable to see what it's outputting... and I'm getting a 0.00. However, the code still gives me an accurate distance measurement as the final output.

Out of curiousity I tried switching the "if" statement so that it was

if ($distance > $ac_rng)

and then no matter what results I got the echo

echo "Distance from $departure_icao...

would not display.

Is there perhaps something wrong with my function that I'm not aware of?

[bbreslauer]

What do you get as the output of the following?

 // Database connection stuff here //


// Create Distance Function //
function distance($departure_lat, $departure_long, $destination_lat, $destination_long) {
    $dist = acos(sin(deg2rad($departure_lat))  // Calculates distance from latitude and longitude.
        * sin(deg2rad($destination_lat))
        + cos(deg2rad($departure_lat))
        * cos(deg2rad($destination_lat))
        * cos(deg2rad($departure_long - $destination_long)));
echo "dist1: ". $dist;
        $dist   = rad2deg($dist);
echo "dist2: ". $dist;
            $miles  = (float) $dist * 69;
            $nm = $miles * 0.8696;

        $display  = sprintf("%0.2f",$nm).' Nautical Miles' ;
        $distance = sprintf("%0.2f", $nm);
echo "distance1: ". distance;
    return $distance ;
}


// Create Aircraft Query //
$ac = "SELECT * FROM aircraft WHERE aircraft_id = '3'";
$ac_result = @mysql_query($ac, $connect) or die(mysql_error());

while ($acrow = mysql_fetch_array($ac_result)) {
    $ac_id = $acrow['aircraft_id'];
    $ac_type = $acrow['aircraft_type'];
    $ac_name = $acrow['aircraft_name'];
    $ac_rng = $acrow['aircraft_rng'];
}

// Create Departure Query //
$dep_rand = rand(1, 13);

$dep = "SELECT * FROM ref_airport WHERE ap_id = '$dep_rand'";
$dep_result = @mysql_query($dep, $connect) or die(mysql_error());

while ($dprow = mysql_fetch_array($dep_result)) {
    $departure_id = $dprow['ap_id'];
    $departure_icao = $dprow['ap_icao'];
    $departure_name = $dprow['ap_name'];
    $departure_lat = $dprow['ap_lat'];
    $departure_long = $dprow['ap_long'];
}

// Create Destination Query //
$dest_rand = rand(1, 13);

$destin = "SELECT * FROM ref_airport WHERE ap_id = '$dest_rand' AND ap_id != '$departure_id'";
$destin_result = @mysql_query($destin, $connect) or die(mysql_error());

while ($dsrow = mysql_fetch_array($destin_result)) {
    $destination_id = $dsrow['ap_id'];
    $destination_icao = $dsrow['ap_icao'];
    $destination_name = $dsrow['ap_name'];
    $destination_lat = $dsrow['ap_lat'];
    $destination_long = $dsrow['ap_long'];



$distance = distance($departure_long['ap_long'], $departure_lat['ap_lat'], $destination_long['ap_long'], $destination_lat['ap_lat']);
echo "distance: ". $distance ." and range: ". $ac_rng ."<br />\n";
if ($distance < $ac_rng) {
    echo "Distance from $departure_icao ($departure_name) is ". distance($departure_lat, $departure_long, $destination_lat, $destination_long) ." : Aircraft Maximum range is $ac_rng.<br><br>";
    break;
}
} 

This is basically the same code I gave you above, with some echo's in it to check what variables are set to.

[Mr_Mako]

If I take your code above and paste it into a file and then view it, I get this :

dist1: 0
dist2: 0
distance1: distance
distance: 0.00 and range: 

(I added some line break tags to make it easier to see the results, and I did test the code both before I added them and after with the same results)

Like I mentioned before tho... the strange thing is that the code is correctly calculating the distances between any two randomly selected airports.

So... I'm lost. 🙁

[bbreslauer]

How do you know that it's correctly calculating the distances? Where in the code can you output this?

[Mr_Mako]

I know because if you go to http://www.pugetair.com/dist_test.php, it shows you the echo'ed outputs I've created for testing purposes. That page is running exactly the code listed above, the only difference being the necessary database connection data. The distances are being cross referenced with information from http://www.airnav.com (and that's where the Lat/Long data for each airport is coming from as well).

If you refresh the page you'll get differing results every time. The "DISTANCE VARIABLE" is outputting the $distance variable as it's output.

The really strange thing is that the $distance variable stays at 0.00 with every single airport UNLESS you have LAX as either the departing or arrival airport. And THAT has me really baffled, since the code is generating the distance data based on Latitude and Longitude. I've triple and quad-rulple checked the Lat/Long data for LAX to ensure it's correct, but it's the only one that this happens on.

Essentially the code is generating the correct information, but I need to come up with a method to be able to capture the distance function output to a variable so I can insert it into the "IF" statement to create the loop.

[bbreslauer]

I'm totally baffled as to why it works on that page, but no other, and doesn't display the $dist variable in the function.

[Mr_Mako]

The function code itself works regardless of where or which file it's appended to, as long as the database connection is inserted (or included in this case) and the database is present. I've never had any trouble with the distance function itself. I only ran into problems when I tried to figure out how to loop the function results into a code statement that will return a function result "LESS THAN" the aircraft max range... and that is because, as we've determined, for some reason we're not able to capture the function output into a variable that can be used in a string. I guess that is really the biggest problem... although there could be plenty more I'm just not seeing too.