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
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
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: $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: 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
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
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
> $word = 'bingo'; > $sql = "SELECT a_column FROM table WHERE text_column LIKE '%$word%'; > $res = mysql_query($sql); > // etc... for displaying results > > Resuming: > % on the beggining - doesn't matter what comes before > % on the end - doesn't matter what comes after Yes, which means that all these following examples would lead to match: abingo bingob abingob So, this might lead to unwanted 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 +-"-"---] Life is unfair but root passwords makes it bareable | msg41399/pgp0.pgp Description: PGP signature