Re: [PHP] Re: Database Question
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_idI 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
Re: [PHP] Re: Database Question
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_idI 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
Re: [PHP] Re: Database Question
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_idI 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
Re: [PHP] Re: Database Question
What is the problem? You need to be more clear about it, especially you
did not tell anything about the tables, how they are related and what
they contain
José Pereira wrote:
I Posted this and no one has helpedis this possible to get working or do
I have to do it differently??
Thanks in advance
José Pereira [EMAIL PROTECTED] escreveu na mensagem
news:[EMAIL PROTECTED]
Hi all,
I'm trying to grab information from two DBs one being a mamber db and the
other a report DB
I want to print a list of users(pilots for a Virtual Airline) from the
Pilot
db and then next to them the nº of reports and total hours they have so
far
from the REPORT DB. The Pilot ID on this list must be a link so that when
clicked it will show the details of the reports.
I got this to but in a general for using a variable $login
so when the pilot/member logins it stores his ID in the $login and then I
use the SELECT * FROM table WHERE column = $login
to be more specific using this code:
?
require(require/config.php);
require(require/authentication.php);
$auth=authenticate($login, $password);
$link = mysql_connect(localhost, user, password)
or die(Could not connect);
mysql_select_db(databse) or die(Could not select database);
$query = SELECT flight_hhmm FROM pirep WHERE pilot_id='$login';
$result = mysql_query($query) or die(Query failed);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$hhmm=explode(':',$line['flight_hhmm']);
$totalm+=$hhmm[0]*60+$hhmm[1];
}
mysql_free_result($result);
?
then to print out the number of hours I use this:
?
echo $auth[login]. '. You have ';
echo floor($totalm / 60).':'.($totalm % 60). ' total hours.';
?
this show the total hours for $login which is the pilot id in the report
DB.
now I have this code to produce TOTAL reports for the site:
$link = mysql_connect(localhost, user, password)
or die(Could not connect);
mysql_select_db(database) or die(Could not select database);
$query = SELECT * FROM pirep ;
$result = mysql_query($query) or die(Query failed);
$nb1 = mysql_numrows($result);
This gets me the nº of rows for all pilots the using echo $nb1 will print
the total reports.
Now I tried alot but no luck. I wanted to get a list like so:
MVC103 - 100 reports Filed - 130 Hours Total
MVC104 - 10 report filed - 50 hours total
etc,
having the ID (MVCxxx) being a link so when clicked it will show the
datails. I tried the count() statement but like I said I new to PHP.
If anyone can help PLS.. take a look at the site, mind you it is in
portuguese http://novo.cdmvirtual.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Re: database question
and what about this ... $sql = SELECT a_column FROM table WHERE text_column LIKE '% $word %'; matching the word in the text ... $sql = SELECT a_column FROM table WHERE text_column LIKE '$word %'; matching the word at the begining of the text ... $sql = SELECT a_column FROM table WHERE text_column LIKE '% $word'; matching the word at the end of the text ... $word = 'bingo'; $sql = SELECT a_column FROM table WHERE text_column LIKE '%$word%'; $res = mysql_query($sql); // etc... for displaying results -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] Re: database question
On Thu, 2001-11-29 at 14:59, Zozulak Peter wrote:
and what about this ...
$sql = SELECT a_column FROM table WHERE text_column LIKE '% $word %';
matching the word in the text ...
$sql = SELECT a_column FROM table WHERE text_column LIKE '$word %';
matching the word at the begining of the text ...
$sql = SELECT a_column FROM table WHERE text_column LIKE '% $word';
matching the word at the end of the text ...
3 selects against one, well depends what the user wants and how he
values code optimizing (versus executing optimizing) this might be
somewhat better approach:
SELECT stringvar FROM tablename WHERE FIND_IN_SET('BINGO',REPLACE(UCASE(stringvar),'
',',')) 0;
So like, first string is converted to uppercase, then all spaces are
made to colons, and then function find_in_set uses word BINGO to
locate if the textfield actually containts that word. Should be quite
exact match allthou i dont guarantee it to work (didnt test it, should
work thou)
Example:
I want to BinGo, would you want to come ? - would translate to
I WANT TO BINGO, WOULD YOU WANT TO COME ? - would translate to
I,WANT,TO,,,BINGO,,WOULD,YOU,WANT,TO,COME,?
And result for find_in_set for this string would be 6 so it would
match..
--
| ,_, Jani Mikkonen, bofh and proud of it, +358456700349
|(O,O) http://www.mikkonen.org/jani
|( ) Pgp public key @ http://www.mikkonen.org/jani/keyh.html
+-] And i repeat, I do not make mistakes that count.
|
msg41402/pgp0.pgp
Description: PGP signature
