By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,718 Members | 1,383 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,718 IT Pros & Developers. It's quick & easy.

Saving a result to another table

P: n/a
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

Jul 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
maceo wrote:
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:

[ big snip ]
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

Based on the code you listed you are not executing your INSERT
statement.

HTH
Jerry
Jul 17 '05 #2

P: n/a
va*****@alltel-dot-net.no-spam.invalid (maceo) wrote:

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>";


Why do you hurt yourself with all of that repetitive coding? It is
certainly wastely, and is sure to lead to typographical errors and
cut-and-paste problem.

<style> <!--
table.one tr td {
background-color: #000080;
color: #ffffff;
font: bold 11pt arial,helvetica,sans serif;
}
--> </style>

print "<td width=85>NUMBER</td>";
print "<td width=120>NAME / EMAIL</td>";
print "<td width=130>CITY</td>";

Isn't that easier to read? And it's SO much easier to maintain, such as
when you want to tweak the colors.
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Jul 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.