Jump to content


Photo

until eof?


  • Please log in to reply
1 reply to this topic

#1 Round

Round
  • Members
  • PipPipPip
  • Advanced Member
  • 104 posts
  • LocationEngland

Posted 25 October 2006 - 09:03 AM

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.
	#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();}
	?>

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:-
#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();}

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.

If it all gets too much just remember that, at the end of the day... It gets dark!! or that on the other hand... There are more Fingers!!
If you eventually get it right then Hip Hip ARRAY!! lol


#2 Round

Round
  • Members
  • PipPipPip
  • Advanced Member
  • 104 posts
  • LocationEngland

Posted 25 October 2006 - 11:59 AM

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.

<?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();}
	?>

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.  :)

If it all gets too much just remember that, at the end of the day... It gets dark!! or that on the other hand... There are more Fingers!!
If you eventually get it right then Hip Hip ARRAY!! lol





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users