Saving a result to another table
This is a discussion on Saving a result to another table within the PHP Language forums, part of the PHP Programming Forums category; I have a script that will print out the results of a table and make a calculation of a total ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-16-2005
|
|||
|
|||
Saving a result to another table
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 
|
|
05-17-2005
|
|||
|
|||
|
Re: Saving a result to another table
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
|
|
05-18-2005
|
|||
|
|||
|
Re: Saving a result to another table
vaavi8r@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, timr@probo.com Providenza & Boekelheide, Inc.
|
Linear Mode
