I have a script that will print out the results of a table and make a
calculation of a total of one of the columns. See example:
<?php
/* Database connection */
include(MYSQL_C ONNECT_INCLUDE) ;
/* Select all pilots */
$query = "SELECT * FROM pilots ORDER BY pilot_num ASC";
$result = mysql_query($qu ery);
/* Determine the number of pilots */
$number = mysql_numrows($ result);
if ($number > 0) {
/* Print roster header
Change this HTML to fit your webpage layout */
print "<table>";
print "<tr>";
print "<td bgcolor=#000080 width=85 height=12
align=left><fon t face=Arial color=#FFFFFF
size=2><b>NUMBE R</b></font></td>";
print "<td bgcolor=#000080 width=120 height=12
align=left><fon t face=Arial color=#FFFFFF
size=2><b>NAME /
EMAIL</b></font></td>";
print "<td bgcolor=#000080 width=130 height=12
align=left><fon t face=Arial color=#FFFFFF
size=2><b>CITY</b></font></td>";
print "<td bgcolor=#000080 width=93 height=12
align=left><fon t face=Arial color=#FFFFFF
size=2><b>COUNT RY</b></font></td>";
print "<td bgcolor=#000080 width=93 height=12
align=left><fon t face=Arial color=#FFFFFF
size=2><b>FLIGH T
TIME</b></font></td>";
print "<td bgcolor=#000080 width=75 height=12
align=left><fon t face=Arial color=#FFFFFF
size=2><b><cent er>STATUS</center></b></font></td>";
print "<td bgcolor=#000080 width=75 height=12
align=left><fon t face=Arial color=#FFFFFF
size=2><b><cent er>LOG
BOOK</b></center></font></td>";
print "</tr>";
/* Get pilots info */
for ($i=0; $i<$number; $i++) {
$num = mysql_result($r esult,$i,"pilot _num");
$name = mysql_result($r esult,$i, "name");
$city = mysql_result($r esult,$i, "city");
$country = mysql_result($r esult,$i,
"country");
$status = mysql_result($r esult,$i,
"status");
$id = mysql_result($r esult,$i, "pilot_id") ;
$email = mysql_result($r esult,$i, "email");
$log = mysql_result($r esult,$i, "log");
/* Calculate flight hours */
$query_hours = "SELECT
sec_to_time(sum (time_to_sec(t2 .duration))) AS
duration_sum FROM pilots t1, reports t2 WHERE t1.pilot_id=$id AND
t1.pilot_id=t2. pilot_id";
$result_hours = mysql_query($qu ery_hours);
if (mysql_numrows( $result_hours) > 0) {
$time =
mysql_result($r esult_hours,0," duration_sum");
}
?>
<table border="1">
<tr>
<td bgcolor=#F0F8FF width=78 height=12
align=left><fon t face=Arial size=2 color=#000080>< ? echo
$num; ?></font></td>
<td bgcolor=#F0F8FF width=120 height=12
align=left><fon t face=Arial size=2 color=#000080>< a
href="mailto:<? echo $email; ?>"><? echo
$name; ?></a></font></td>
<td bgcolor=#F0F8FF width=130 height=12
align=left><fon t face=Arial size=2 color=#000080>< ? echo
$city; ?></font></td>
<td bgcolor=#F0F8FF width=93 height=12
align=left><fon t face=Arial size=2 color=#000080>< ? echo
$country; ?></font></td>
<td bgcolor=#F0F8FF width=93 height=12
align=left><fon t face=Arial size=2 color=#000080>< ? echo
$time; ?></font></td>
<td bgcolor=#F0F8FF width=73 height=12
align=left><fon t face=Arial size=2 color=#000080>< ? echo
$status; ?></font></td>
<td bgcolor=#F0F8FF width=73 height=12 align=left><fon t
face=Arial size=2 color=#000080>< center><a
href="<? echo $log;
?>">Flightlog </a></center></font></td>
</tr>
</table>
<?
}
print "</table>";
}
/* Close the database connection */
mysql_close();
?>
What I want to do is take the result ($time) and save it to another
table, along with the name and id# of the pilot. I then want to call
from that table and print out the top 5 based upon flight times.
I have tried to store to a second table (tmp), by using code to this
script that appears after the $time calculation:
[code:1:4d5f0e55 d2]
$sql = "INSERT INTO tmp (mxpid,time,nam e) VALUES
('$num','$time' ,'$name')";
$query = "SELECT * FROM tmp ORDER BY time DESC";
$result = mysql_query($qu ery);
$number = mysql_numrows($ result);
if ($number > 0) {
for ($i=0; $i<$number; $i++) {
$mxpid = mysql_result($r esult,$i,"mxpid ");
$name = mysql_result($r esult,$i, "name");
$totaltime = mysql_result($r esult,$i,
"time");
[/code:1:4d5f0e55 d2]
but this is not working.....get ting a parsing error and the data is
not stored in the new table.
Any ideas how to do this better?
Thanks
