Posted by maceo on 05/16/05 22:38

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_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 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:4d5f0e55d2]

$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:4d5f0e55d2]

but this is not working.....getting a parsing error and the data is
not stored in the new table.

Any ideas how to do this better?

Thanks

[Back to original message]