Hi all.
I have a couple of databases I'm working from... they are listed below :
REPORTS
*******
ID FLTID NOTES
-------------------------
1 2 Flight 20
2 1 Flight 10
3 1 Flight 10
4 3 Flight 30
5 1 Flight 10
6 2 Flight 20
FLIGHTS
*******
ID NUM TO FROM DIST
-------------------------------------
1 10 KLAX KSFO 238
2 20 KSEA KLAS 409
3 30 KEWR KDEN 1756
I have two queries I'm trying to run from these tables, and I am having trouble figuring out how to code the queries to get the results I'm looking for.
For the first query, I want to query the REPORT table to find the flights flown the most, listed in descending order. The results I'm looking for would be -
Flight 10
Flight 20
Flight 30
For the life of me I can't figure out how to code the query to retrieve this result. The last attempt I made was this :
// Create Total Flights and Most Flown Flights Query //
$rpt = "SELECT * FROM reports";
$rpt_result = @mysql_query($rpt, $connect) or die(mysql_error());
$rptnum = mysql_num_rows($rpt_result); // TOTAL REPORTS
while ($rpt_row = mysql_fetch_array($rpt_result)) {
$rpt_id = $rpt_row['reports_id'];
$rtp_fltid = $rpt_row['reports_fltid'];
$flt = "SELECT * FROM flights WHERE flights_id = '$rpt_fltid'";
$flt_result = @mysql_query($flt, $connect) or die(mysql_error());
while ($flt_row = mysql_fetch_array($flt_result)) {
$flt_num = $flt_row['flights_num'];
}
$display_most_flights .= "$flt_num"; // FLIGHTS MOST FLOWN
}
I know this isn't right... but I have been wracking my brains trying to figure out how to get the results I'm looking for.
The second query I need is to get the total flight distance of all the reports SUMed together. The result from the tables above would be 3288 (the SUM of the distances of each flight in the REPORTS table). The code I ended up with after a few hours of headache was this :
$effn = "SELECT * FROM reports";
$effnresult = @mysql_query($effn, $connect) or die(mysql_error());
while ($effnrow = mysql_fetch_array($effnresult)) {
$fltd = $effnrow['reports_fltid'];
// Create Total VA Flight Distance Query //
$dst = "SELECT SUM(flights_dist) FROM flights WHERE flights_num = '$fltd'";
$dstresult = @mysql_query($dst, $connect) or die(mysql_error());
while ($dstrow = mysql_fetch_array($dstresult)) {
$dst_flt = $dstrow['SUM(flight_distance)']; // VA TOTAL FLIGHT DISTANCE
}
}
All this does is give me the result for a single flight.
I've searched for a solution and wasn't able to locate anything that resembled my problem... although I admit phrasing my search may have been the problem. This issue has me at a stopping point on my site, and I really need to find a solution. If anyone can shed some light on where I've gone wrong, I would greatly appreciate it.
