Re: [PHP] Re: Database Question

[Marek Kilimajer]

No, because the query specifies 

SELECT *, COUNT ...
      ^
and GROUP BY p.pilot_id
Just try it

José Pereira wrote:

Ok, but  won't this count ALL the reports and ALL the hours together???

"Marek Kilimajer" <[EMAIL PROTECTED]> escreveu na mensagem
news:[EMAIL PROTECTED]
José Pereira wrote:

 

Sorry I wasn't clear before.  It goes like this.

I have a Virtual Airline and I have a DB called pilots with these tables:

   

To make it cleaner: database has tables, tables have columns, pilots and
report are tables, these are columns:
 

pilot_id, pilot_name, ivao, vatsim, status

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)
   

Add report_id here (INT PRIMARY autoincrement)

 

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.
   

SELECT *,
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
 

The PILOT_ID must link to another page which will show all the reports in
detail for that pilot.
   

so be it

 

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