I have some code that extracts the data from a table and performs a
calculation (total time) on one of the columns.
Here is the code:
<?php
/* Database connection */
include(MYSQL_CONNECT_INCLUDE);
/* Select all pilots */
$query = "SELECT * FROM pilots ORDER BY pilot_num ASC";
$result = mysql_query($query);
/* 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><font face=Arial color=#FFFFFF
size=2><b>NUMBER</b></font></td>";
print "<td bgcolor=#000080 width=120 height=12
align=left><font face=Arial color=#FFFFFF
size=2><b>NAME /
EMAIL</b></font></td>";
print "<td bgcolor=#000080 width=130 height=12
align=left><font face=Arial color=#FFFFFF
size=2><b>CITY</b></font></td>";
print "<td bgcolor=#000080 width=93 height=12
align=left><font face=Arial color=#FFFFFF
size=2><b>COUNTRY</b></font></td>";
print "<td bgcolor=#000080 width=93 height=12
align=left><font face=Arial color=#FFFFFF
size=2><b>FLIGHT
TIME</b></font></td>";
print "<td bgcolor=#000080 width=75 height=12
align=left><font face=Arial color=#FFFFFF
size=2><b><center>STATUS</center></b></font></td>";
print "<td bgcolor=#000080 width=75 height=12
align=left><font face=Arial color=#FFFFFF
size=2><b><center>LOG
BOOK</b></center></font></td>";
print "</tr>";
/* Get pilots info */
for ($i=0; $i<$number; $i++) {
$num = mysql_result($result,$i,"pilot_num");
$name = mysql_result($result,$i, "name");
$city = mysql_result($result,$i, "city");
$country = mysql_result($result,$i,
"country");
$status = mysql_result($result,$i,
"status");
$id = mysql_result($result,$i, "pilot_id");
$email = mysql_result($result,$i, "email");
$log = mysql_result($result,$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($query_hours);
if (mysql_numrows($result_hours) > 0) {
$time =
mysql_result($result_hours,0,"duration_sum");
}
?>
<table border="1">
<tr>
<td bgcolor=#F0F8FF width=78 height=12
align=left><font face=Arial size=2 color=#000080><? echo
$num; ?></font></td>
<td bgcolor=#F0F8FF width=120 height=12
align=left><font 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><font face=Arial size=2 color=#000080><? echo
$city; ?></font></td>
<td bgcolor=#F0F8FF width=93 height=12
align=left><font face=Arial size=2 color=#000080><? echo
$country; ?></font></td>
<td bgcolor=#F0F8FF width=93 height=12
align=left><font face=Arial size=2 color=#000080><? echo
$time; ?></font></td>
<td bgcolor=#F0F8FF width=73 height=12
align=left><font face=Arial size=2 color=#000080><? echo
$status; ?></font></td>
<td bgcolor=#F0F8FF width=73 height=12 align=left><font
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 to take the value of the calculation:
$time = mysql_result($result_hours,0,"duration_sum");
and store it in another table. I have written some code and inserted
into the script below this line to store the info into a seperate
table:
[code:1:d1d69b6ad4]
$sql = "INSERT INTO tmp (mxpid,time,name) VALUES
('$num','$time','$name')";
$query = "SELECT * FROM tmp ORDER BY time DESC";
$result = mysql_query($query);
$number = mysql_numrows($result);
if ($number > 0) {
for ($i=0; $i<$number; $i++) {
$mxpid = mysql_result($result,$i,"mxpid");
$name = mysql_result($result,$i, "name");
$totaltime = mysql_result($result,$i,
"time");
[/code:1:d1d69b6ad4]
I am getting a parsing error and the results are not stored.
What I want to do is save this information and call it back from the
table tmp to show the top 5 pilots based on flight hours.
Is there an easier way to do this?
Thanks
