José Pereira wrote:
Sorry I wasn't clear before. It goes like this.To make it cleaner: database has tables, tables have columns, pilots and report are tables, these are columns:
I have a Virtual Airline and I have a DB called pilots with these tables:
pilot_id, pilot_name, ivao, vatsim, statusAdd report_id here (INT PRIMARY autoincrement)
Also have a report DB which contains these tables:
pilot_id, pilot_name, aircraft, flight_time, origin, destination, originweather, destinationweather, etc., etc., (<--- this is per report)
SELECT *,
I want to get all the information from these tables, for each pilot, since a pilot has many reports there are many lines in the Report DB.
Now I need to create a page showing the all the current pilots that active (stated in the status table of the PILOT DB), and next to that show their TOTAL reports filed and TOTAL hours flown for the company.
COUNT(report_id) count, # nuber of reports
SUM(flight_time) as total_time # total time
FROM pilots p LEFT JOIN report r ON p.pilot_d=r.pilot_id # left join so even pilots with no report show up
WHERE p.status=1 # only active pilots
GROUP BY p.pilot_id # this magic alows us to get info from pilot table together with grouping info (COUNT, SUM...) from reports table
ORDER BY pilot_name # order it
so be it
The PILOT_ID must link to another page which will show all the reports in detail for that pilot.
I down't know if this is possible or adviseable, but I have about 20 pilots sending 2-3 report per day, you can imagine how confusing the REPORT section is now, since it has all the report for all the pilots ordered by pilot_id....I have about 160 reports so far, and I'm going nuts....
Thanks in advance. Joe
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
