"caching" a report then resort it...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

"caching" a report then resort it...

Post by Burrito »

I have a report that I have created here which can take well over 10 minutes to run (loads of data), I then output my data into an html table with 3 different columns (school name, student name, student PIN). I want to be able to have my column headers "sort-by" links which would allow my users to click them, then repull the data sorted by whatever they've specified. Problem should be obvious, I dont' want them to have to wait another 10 minutes just for the resort.

I'm thinking there's got to be a way to just put all of my results from the previous run into some sort of array (cached) so that if they click a sort link, it just grabs the stored info and repopulates my html table (reordered).

any suggestions?

thanks,

Burrito
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

How are you storing the data?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

All of the original "raw" data is stored in a mysql db.

the reason it takes so long to run the report is, I first have to grab all of the student names, then loop over all of their courses, then loop over all of the assignments in that course, then loop over all of the grades within those courses.

some students can be enrolled in 25 courses which have between 30 and 50 assignments / course. I then have to figure out their grades for those assignements to determine whether they've "passed" the course or not....long story short, it takes forfriggin' ever to run it and I'd like to just be able to save the data once it's been run (don't want to use a temp table if I can avoid it), and then use the "cached" data for a re-sort if they so desire.

I've already got a pagination set up to only show 25 students at a time (as well as a student list by name (with pagination))...still very very long.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Sounds like a JOIN would help it run faster then a bunch of nested loops

Not too sure about methods of "caching" large amounts of data..

perhaps try sessions.. ? not too sure
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

trust me, I've joined the hell out of it, there's no other way given the table structure I was handed...

any others??
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

Depending on how you have the results stored (i'd assume an array), I would just serialize the array, and either store it in a db, or in the users session, restore it then sort...
User avatar
SystemWisdom
Forum Commoner
Posts: 69
Joined: Sat Mar 26, 2005 5:54 pm
Location: A Canadian South of the 49th Parallel

Post by SystemWisdom »

You could let Javascript handle it.. Put the data into JS arrays and write a JS sort function.. That way the client side handles the sorting, and it takes load off of the server..
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

and if the user has JS disabled?
User avatar
SystemWisdom
Forum Commoner
Posts: 69
Joined: Sat Mar 26, 2005 5:54 pm
Location: A Canadian South of the 49th Parallel

Post by SystemWisdom »

Instruct them to enable it, not hard.. if they dont, then they dont get to re-sort the data.

Especially since his target audience is school of students (or teachers?), then they can be Instructed to have JS enabled for the system to work. Students can learn, teachers can teach!

Besides, almost all browsers have support for JS, and many popular websites use JS, so people who disable JS are only limiting themselves, their choice!

For the developer it is merely a trade-off:
Less Server Load for Enforced Javascript usage.

When a single request can take up to 10 minutes, the trade-off seems more than reasonable..
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

in your sql statement, how are you getting your records? are you querying and retrieving every single record? cuz that is what it sounds like to me, thus why it takes so long.

in other words, why not query it to only grab the first 25 records, or 50, or however many they have specified to display per page?

if you are doing it this way, it should not take that long to query.. and if it is, post the code ;)
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

here you go...

Code: Select all

<?
error_reporting(E_ALL);
if(!isset($_GET['st'])){
	$start = 0;
} else {
	$start = $_GET['st'];
}
if(isset($_GET['let'])){
	$nlet = $_GET['let'];
}else{
	$nlet = "";
}
set_time_limit(0);

mysql_connect(DB_HOST,DB_USER,DB_PASSWORD)
	or die(mysql_error());;
//SELECT g.id, CONCAT(c.DistrictID, '|',c.RegionID, '|',c.SchoolID) AS DRS, CONCAT(g.PIN, ' - ', c.Name) AS PINClName FROM NewRegSys.Groups g, NewRegSys.Clients c WHERE c.id = g.ClientID ORDER By c.Name
$getdists = mysql_query("SELECT g.id, c.DistrictID, c.Name AS PINClName FROM NewRegSys.Groups g, NewRegSys.Clients c WHERE c.id = g.ClientID ORDER By c.Name")
	or die(mysql_error());
if(isset($_GET['gott'])){
	$s_sid = ($_GET['sid'] !== "%" ? " and u.SchoolID = ".$_GET['sid'] : "");
	$f_Did = $_GET['did'];
	$getdisid = mysql_query("SELECT id, Name FROM NewRegSys.Clients WHERE DistrictID = $f_Did")
		or die(mysql_error());
	$gtdisid = mysql_fetch_assoc($getdisid);
	$f_Cid = $gtdisid['id'];
	$getstustot = mysql_query("select concat(u.fname,' ',u.lname) as name, u.BbID, sc.Name from NewRegSys.Users u, NewRegSys.Schools sc where u.SchoolID = sc.id and u.ClientId = $f_Cid".($nlet !== "" ? " and u.fname like '".$nlet."%'" : "") ."$s_sid order by name")
		or die("query one".mysql_error());
	$getstusnum = mysql_num_rows($getstustot);
	$getstus = mysql_query("select concat(u.fname,' ',u.lname) as name, u.BbID, sc.Name from NewRegSys.Users u, NewRegSys.Schools sc where u.SchoolID = sc.id and u.ClientId = $f_Cid".($nlet !== "" ? " and u.fname like '".$nlet."%'" : "") ."$s_sid order by name limit $start, 25")
		or die("query two".mysql_error());
} // end if for gott set...burrito


?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
	<title>Student Report By School District</title>
<style>
td
{
font-family:tahoma; font-size:9.0pt; color:00000f
}

</style>
</head>

<body>

<form name="MyForm" action="student-report.php">
<table width="400">
<tr>
<td>Select District</td>
</tr>
<tr>
<td><select name="did" onChange="location='student-report.php?did='+this.value">
<?while($gtdists = mysql_fetch_assoc($getdists)){?>
<option value="<?=$gtdists['DistrictID'];?>" <?if(isset($_GET['did']) && $_GET['did'] == $gtdists['DistrictID']) echo "selected";?>><?=$gtdists['PINClName'];?></option>
<?} // end while for result set...burrito ?>
</select></td>
</tr>
<?if(isset($_GET['did'])){ ?>
<tr>
<td>
Select School
</td>
</tr>

<?
$getschools = mysql_query("SELECT s.id, s.Name FROM NewRegSys.Regions r INNER JOIN NewRegSys.Schools s ON s.RegionID = r.id WHERE r.DistrictID = ".$_GET['did']." and s.Name <> 'All' order by s.Name")
	or die(mysql_error());
?>
<tr>
<td><select name="sid">
<option value="%">All</option>
<?while($gtschools = mysql_fetch_assoc($getschools)){?>
<option value="<?=$gtschools['id'];?>" <?if(isset($_GET['sid']) && $_GET['sid'] == $gtschools['id']) echo "selected";?>><?=$gtschools['Name'];?></option>
<?} // end while for result set...burrito ?>
</select></td>
</tr>
<tr>
<td><input type="submit" value="Run Report"></td>
</tr>
<?} // end if for did set...burrito?>
</table>
<input type="hidden" name="gott" value="1">
</form>
<?if(isset($_GET['gott'])){ ?>
<table border="1" cellspacing="0" bordercolor="#00000f">
<tr>
<?$getcourses = mysql_query("select c.title,a.course_id from NewRegSys.Users u, atutor.AT_course_enrollment a, atutor.AT_courses c where u.ClientID = $f_Cid and a.member_id = u.BbID and a.course_id = c.course_id group by c.title")
	or die("query three".mysql_error());
$numcrs = mysql_num_rows($getcourses);
echo "<td colspan=\"".($numcrs + 2)."\">";


if(isset($_GET['st']) && $_GET['st'] != 0) echo "<a href=\"student-report.php?".(isset($_GET['sid']) ? "sid=".$_GET['sid']."&" : "")."gott=1&did=".$_GET['did']."&st=".($_GET['st'] - 25).(isset($_GET['let']) ? "&let=".$_GET['let'] : "")."\">&laquo;Previous</a> | ";?>
<?if($getstusnum > 25){ ?>
<a href="student-report.php?<?=(isset($_GET['sid']) ? "sid=".$_GET['sid']."&" : "");?>gott=1&did=<?=$_GET['did'];?>&st=<?=(isset($_GET['st']) ? $_GET['st'] + 25 : "25").(isset($_GET['let']) ? "&let=".$_GET['let'] : "");?>">Next &raquo;</a>
<?} // end if for less than 25 records...burrito
$end = (($start+24) >= $getstusnum ? $getstusnum : ($start+24));?>
 Records: <?=($start + 1)."-".$end." of ".$getstusnum;?></td>
</tr>
<? 
?>
<tr>
<td>School</td>
<td>Student Name</td>
<?while($gtcourses = mysql_fetch_assoc($getcourses)){
	$tit = explode(" ",$gtcourses['title']);
?>
<td><?=$tit[0];?></td>
<?} // end while for result set...burrito ?>
</tr>
<?
while($gtstus = mysql_fetch_assoc($getstus)){
echo "<tr><td>".$gtstus['Name']."</td><td>".$gtstus['name']."</td>";
	mysql_data_seek($getcourses,0);
	while($gtcourses = mysql_fetch_assoc($getcourses)){
		$getenrollment = mysql_query("select course_id from atutor.AT_course_enrollment where member_id = ".$gtstus['BbID']." and course_id = ".$gtcourses['course_id'])
			or die(mysql_error());
		if($gtenrollment = mysql_fetch_assoc($getenrollment)){
		$getassign = mysql_query("SELECT * FROM atutor.AT_assignments WHERE course_id = ".$gtcourses['course_id'])
			or die("query four".mysql_error());
		while($gtassign = mysql_fetch_assoc($getassign)){
			$getgrades = mysql_query("SELECT grade FROM atutor.AT_grades WHERE assignment_id = ".$gtassign['id']." AND member_id = ".$gtstus['BbID'])
				or die("query five".mysql_error());			
				$thisgrade = "-";
				$flag = FALSE;
				while($gtgrades = mysql_fetch_assoc($getgrades)){
					if($gtgrades['grade'] < 4 || $gtgrades['grade'] == ""){
						$flag = TRUE;
					} // end if for first lesson failure or lesson not done...burrito
					if($flag && ($gtgrades['grade'] < 4 || $gtgrades['grade'] == "")){
						$thisgrade = "E";
						break;
					} // end if for haven't finished the course...burrito
					if(strpos($gtassign['assignment_title'],"Post Test") !== FALSE){
						if($gtgrades['grade'] < 85){
							$thisgrade = "E";
							break;
						}
					} // end if for post test assignment...burrito
					$thisgrade = "C";
				}
		} // end while for result set...burrito
		} else {// end if for students enrolled in class...burrito
			$thisgrade = "-";
		}
	echo "<td align=\"center\">".$thisgrade."</td>";
	} // end while for result set...burrito

echo "</tr>";


} // end while loop for result set...burrito ?>
</table>
<? $lets = array("a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"); 
echo "<div align=\"center\">";
foreach($lets as $let){
$ulet = strtoupper($let);
	echo "<a href=\"student-report.php?".(isset($_GET['sid']) ? "sid=".$_GET['sid']."&" : "")."did=".$_GET['did']."&let=".$let."&gott=1\">".$ulet."</a> ".($let == "l" ? "<br>" : ($let !== "z" ? " | " : "<br><a href=\"student-report.php?".(isset($_GET['sid']) ? "sid=".$_GET['sid']."&" : "")."did=".$_GET['did']."&gott=1\">All</a>"));
}
echo "</div>";
} // end if for gott set...burrito
?>



</body>
</html>
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

Your sql statements are what's causing the lag then cuz you are retrieving every single record that matches any query you are throwing at it, dumping them into an array, and then posting the ones you need.

This, most definately, will cause lag when dealing with large tables...

You may want to look into using LIMIT. This will reduce the amount of php code you are using, and also speed up your overall query drastically. Typical syntax is :

Code: Select all

mysql_query("SELECT * FROM table LIMIT 5,10;");
You can find more on LIMIT in the mysql doc included with your mysql distro, or the [url=ttp://dev.mysql.com/doc/mysql/en/select.html]Online MySQL Manual[/url]. If you use the online manual, you'll have to scroll down to LIMIT.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

I'm already using limit to limit the number of students per load (at present 25, see the top of the code I posted).

the problem comes in when I have to loop over the courses for which the are signed up, then loop over the assignments in those courses, and finally loop over the grades to determine whether or not they've passed the course...I can't limit any of this stuff. As I said in a previous post, I think all of the queries are necessary evils but someone asked for my code, so I posted. thought maybe they could help speed things up with a "join" or something that I may have missed (as again, someone above suggested).

at this point, I'm thinking I"ll go with SW's idea and just throw it into a JS array, thansk for helping everyone.

Burr
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

Sorry about that, I missed it. Was kinda speeding through them anyways (@work right now..). Anyways, there still should be no reason for it to take 10 minutes each time you do this. No reason at all. but SW's idea will work, though I'm not exactly sure that's the best method... Will have to look at it when I get home.
Post Reply