469,133 Members | 1,061 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

Storing the results of a calculation in another table

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

Jul 17 '05 #1
0 1414

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Kieran Benton | last post: by
12 posts views Thread by Larry Bates | last post: by
12 posts views Thread by colin | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.