Looping MySQL queries

[cdoverlaw]

<?php 
		$ExecutiveClubPoints = 0;
		$Email = $_SESSION['Username'];
		//Calculates next avaliable flight number
		$Count = 0;
  			include("includes/config.php");
  
		$con = mysql_connect("$dbhost","$dbuser","$dbpass");
  			if (!$con)
    		{
    			die('Could not connect: ' . mysql_error());
   		 	}
  		
		mysql_select_db("$dbname", $con);
  
		$booking = mysql_query("SELECT BookingID, FlightID FROM booking WHERE Email = '$Email';");

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

			$FlightID = $_row['FlightID'];
			$flight = mysql_query("SELECT Departing, Destination FROM flight WHERE FlightID = '$FlightID';");
			while($row = mysql_fetch_array($flight)){
				$Depart = $_row['Departing'];
				$Departing = mysql_query("SELECT Country FROM airport WHERE Airport = '$Depart';");
				while($row = mysql_fetch_array($Departing)){
					$DepartCountry = $_row['Country'];
				}
				$Dest = $_row['Departing'];
				$Destination = mysql_query("SELECT Country FROM airport WHERE Airport = '$Dest';");
				while($row = mysql_fetch_array($Destination)){
					$DestCountry = $_row['Country'];
				}
				if ($Depart == $Dest)
    				{
    					$ExecutiveClubPoints = $ExecutiveClubPoints + 5;
   		 			}
			}
		}

		echo $ExecutiveClubPoints;
?>

This is my code, im using loops to try and calculate a ExecutiveClubPoints, but it doesnt work, can anyone help me find the errors

[Psycho]

You should never, never do queries within a loop. It is very inefficient - and you are doing them 3 levels deep! You need to learn how to do joins.

 

But specifically to your question, I don't see what you are trying to accomplish. You are only adding to  $ExecutiveClubPoints if the Departure and Destination cities are the same for a particular flight. That would never happen, at least I've never taken a flight that took off and returned to the same city. So, please be specific as to what you are trying to accomplish.

 

Going back to your looping queries, the last two queries don't server any purpose in the above code. The following query should get you all the information you need that the four queries you have above get you:

 

SELECT b.BookingID, b.FlightID, f.Departing, f.Destination,
       a1.Airport as depart_airport, a2.dest_airport

FROM booking b
LEFT JOIN flight f ON b.FlightID = f.FlightID
LEFT JOIN airport a1 ON f.Departing = a1.Airport
LEFT JOIN airport a2 ON f.Departing = a2.Airport

WHERE b.Email = '$Email';"

"SELECT Departing, Destination FROM flight WHERE FlightID = '$FlightID';"