Jump to content

Archived

This topic is now archived and is closed to further replies.

Round

until eof?

Recommended Posts

hello all,
Hopefully you can help me on this next issue as you have all been really helpful with all other q's.
Right I ill do my best to explain.

I have a page that displays a students attendance records, which is fine.
[code]
#connect to db
$conn = @mysql_connect(  "localhost", "username", "password" )
      or die( "Err:conn");
#select db
$rs = @mysql_select_db( "dbname", $conn)
or die( "ERR:Db");

#create query
$sql = "select * from table where stu_id=\"$stu_id\" ";

#exe query
$rs = mysql_query( $sql, $conn )
or die( "Could not execute query.");

#search for matches
$num = mysql_numrows ($rs);
if ($num !=0)
{
$list = "<table align= \"center\" border=\"0\" cellpadding=\"8\" >";
$list .= "<tr>";
$list .= "<th class=table>Reg ID</th>";
$list .= "<th class=table>Group</th>";
$list .= "<th class=table>Reg Description</th>";
$list .= "<th class=table>Day</th>";
$list .= "<th class=table>Att</th>";
$list .= "<th class=table>Abs</th>";
$list .= "<th class=table>%Att</th>";
$list .= "<th class=table>Tutor</th>";
$list .= "</tr>";

#retrieve data
while ( $row = mysql_fetch_array( $rs ) )
{
$list .= "<tr>";
$list .= "<td class=table>".$row["register_id"]."</td>";
$list .= "<td class=table>".$row["register_group"]."</td>";
$list .= "<td class=table>".$row["full_desc"]."</td>";
$list .= "<td class=table>".$row["day"]."</td>";
$list .= "<td class=table>".$row["total_present"]."</td>";
$list .= "<td class=table>".$row["total_absent"]."</td>";
$list .= "<td class=table>".$row["perc"]."%"."</td>";
$list .= "<td class=table>".$row["forename"]." ".$row["surname"]."</td>";
$list .= "</tr>";
$name = $row["name"];
$student_id = $row["stu_id"];
}
$list .= "</table>";

#list details if matches found or display no matches found msg
echo($name. "  ". $stu_id. "<br><br>");
echo(  $list . "<br>" );
}
else
{ echo ("<br>There is no attendance information for this student");
exit();}
?>[/code]

I want to add a row at the end of this table that add's all the att's, abs's and works out a percentage once it has retrieved all of the matches.

So I did this:-
[code]#connect to db
$conn = @mysql_connect(  "localhost", "username", "password" )
      or die( "Err:conn");
#select db
$rs = @mysql_select_db( "dbname", $conn)
or die( "ERR:Db");

#create query
$sql = "select *, sum(total_absent) as totabs, sum(total_present) as totatt from table where stu_id=\"$stu_id\" group by stu_id";

#exe query
$rs = mysql_query( $sql, $conn )
or die( "Could not execute query.");

#search for matches
$num = mysql_numrows ($rs);
if ($num !=0)
{
$list = "<table align= \"center\" border=\"0\" cellpadding=\"8\" >";
$list .= "<tr>";
$list .= "<th class=table>Reg ID</th>";
$list .= "<th class=table>Group</th>";
$list .= "<th class=table>Reg Description</th>";
$list .= "<th class=table>Day</th>";
$list .= "<th class=table>Att</th>";
$list .= "<th class=table>Abs</th>";
$list .= "<th class=table>%Att</th>";
$list .= "<th class=table>Tutor</th>";
$list .= "</tr>";

#retrieve data
while ( $row = mysql_fetch_array( $rs ) )
{
$list .= "<tr>";
$list .= "<td class=table>".$row["register_id"]."</td>";
$list .= "<td class=table>".$row["register_group"]."</td>";
$list .= "<td class=table>".$row["full_desc"]."</td>";
$list .= "<td class=table>".$row["day"]."</td>";
$list .= "<td class=table>".$row["total_present"]."</td>";
$list .= "<td class=table>".$row["total_absent"]."</td>";
$list .= "<td class=table>".$row["perc"]."%"."</td>";
$list .= "<td class=table>".$row["forename"]." ".$row["surname"]."</td>";
$list .= "</tr>";
$list .= "<tr>";
$list .= "<td class=table></td>";
$list .= "<td class=table></td>";
$list .= "<td class=table colspan=\"2\" align=\"right\"><b>My Total Attendance</b></td>";              $list .= "<td class=table>".$row["totatt"]."</td>";
$list .= "<td class=table>".$row["totabs"]."</td>";
                          $list .= "<td class=table>".$row["totperc"]."%"."</td>";
$list .= "<td class=table></td>";
$list .= "</tr>";
$name = $row["name"];
$student_id = $row["stu_id"];
}
$list .= "</table>";

#list details if matches found or display no matches found msg
echo($name. "  ". $stu_id. "<br><br>");
echo(  $list . "<br>" );
}
else
{ echo ("<br>There is no attendance information for this student");
exit();}[/code]

It now only displays the first record from the table that matches but carries out the calculation correctly(includes all the matching rows) ???
I want it to display all matching rows then the calculation row at the end.
Im guessing there is an; if eof, do until loop situation but I've searched for a answer, but no luck.
Any Ideas?

Hope I made sense.

Many Thanks.

Share this post


Link to post
Share on other sites
Right I have been going over this all day and I think I may have cracked it.

I thought I would put my code here for use by someone else or if any one still has any suggestions on how to make it more efficient please let me know.

[code]<?php
 
  #connect to db
$conn = @mysql_connect(  "localhost", "username", "password" )
      or die( "Err:conn");
#select db
$rs = @mysql_select_db( "dbname", $conn)
or die( "ERR:Db");

#create queries
$sql = "select * from table where stu_id=\"$stu_id\" ";

$totsql = "select sum(total_absent) as totabs, sum(total_present) as totatt from table
where stu_id=\"$stu_id\" group by stu_id";

#exe queries
$rs = mysql_query( $sql, $conn )
or die( "Could not execute query.");

$rs2  = mysql_query( $totsql, $conn )
or die( "Could not execute query.");

#search for matches to query $sql
$num = mysql_numrows ($rs);
if ($num !=0)
{
$list = "<table align= \"center\" border=\"0\" cellpadding=\"8\" >";
$list .= "<tr>";
$list .= "<th class=table>Reg ID</th>";
$list .= "<th class=table>Group</th>";
$list .= "<th class=table>Reg Description</th>";
$list .= "<th class=table>Day</th>";
$list .= "<th class=table>Att</th>";
$list .= "<th class=table>Abs</th>";
$list .= "<th class=table>%Att</th>";
$list .= "<th class=table>Tutor</th>";
$list .= "</tr>";

#retrieve data from query $sql
while ( $row = mysql_fetch_array( $rs ) )
{
$list .= "<tr>";
$list .= "<td class=table>".$row["register_id"]."</td>";
$list .= "<td class=table>".$row["register_group"]."</td>";
$list .= "<td class=table>".$row["full_desc"]."</td>";
$list .= "<td class=table>".$row["day"]."</td>";
$list .= "<td class=table>".$row["total_present"]."</td>";
$list .= "<td class=table>".$row["total_absent"]."</td>";
$list .= "<td class=table>".$row["perc"]."%"."</td>";
$list .= "<td class=table>".$row["forename"]." ".$row["surname"]."</td>";
$list .= "</tr>";
$name = $row["name"];
$student_id = $row["stu_id"];
}
#retrieve data from query $totsql
while ( $row = mysql_fetch_array( $rs2 ) )
{
$totabs = $row["totabs"];
  $totatt = $row["totatt"];
$totperc = (round(100 * ( $totatt / ( $totatt + $totabs ) ) ,1) );
$list .= "<tr>";
$list .= "<td class=table></td>";
$list .= "<td class=table></td>";
$list .= "<td class=table colspan=\"2\" align=\"right\"><b>My Total Attendance</b></td>";
$list .= "<td class=table>".$row["totatt"]."</td>";
$list .= "<td class=table>".$row["totabs"]."</td>";
$list .= "<td class=table>".$totperc."%"."</td>";
$list .= "<td class=table></td>";
$list .= "</tr>";
}
$list .= "</table>";

#list details if matches found or display no matches found msg
echo($name. "  ". $stu_id. "<br><br>");
echo(  $list. "<br>" );
}
else
{ echo ("<br>There is no attendance information for this student");
exit();}
?>[/code]

Thought it may be useful as it shows calculations at server side and some at client side. It also shows a table with a totals part.
Hope it is of use.  :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.