Query Results Into Multiple Pages

[scriderpsd]

Hi, I have been tasked to create several web based reports where I pull from a database. The first report was fairly easy, I had to pull about 400 rows for my report and broke it up into pages containing 20 rows each.

The next report needs to be broken up by school. I have about 30 schools in the database and each school has about 5-20 rows associated with it. I want to group them by school and then break my pages up by school. For example, one page may contain 5 rows and the next page may contain 20 rows.

So, here is the code I used for my first report.

$rowsPerPage = 15;

                // by default we show first page
	$pageNum = 1;

	// if $_GET['page'] defined, use it as page number
	if(isset($_GET['page']))
	{
	 $pageNum = $_GET['page'];
	}

	// counting the offset
	$offset = ($pageNum - 1) * $rowsPerPage;


	$sql="SELECT school, last_name, first_name, six_digit, home_address, disability, 1_bus, 1_time, 2_bus, 2_time FROM active ORDER BY school LIMIT $offset, $rowsPerPage";
	$conn = db_trans_connect();
	$result = mysql_query($sql,$conn);



			for ($i = 0; $i < mysql_num_rows($result); $i++) {
				echo("<TR>");
				$row_array = mysql_fetch_row($result);
				for ($j = 0; $j < mysql_num_fields($result); $j++) {
					echo("<TD align=left>" . $row_array[$j] . "</TD>");
				}
				echo("</TR>");
			}

	echo '<br>';

	// how many rows we have in database
	$query   = "SELECT COUNT(*) AS numrows FROM active";
	$result  = mysql_query($query) or die('Error, query failed');
	$row     = mysql_fetch_array($result, MYSQL_ASSOC);
	$numrows = $row['numrows'];

	// how many pages we have when using paging?
	$maxPage = ceil($numrows/$rowsPerPage);

	$self = $_SERVER['PHP_SELF'];

	// creating 'previous' and 'next' link
	// plus 'first page' and 'last page' link

	// print 'previous' link only if we're not
	// on page one
	if ($pageNum > 1)
	{
		$page = $pageNum - 1;
		$prev = " <a href=\"$self?index_radio=6&submit=Submit&page=$page\">[Prev]</a> ";

		$first = " <a href=\"$self?index_radio=6&submit=Submit&page=1\">[First Page]</a> ";
	} 
	else
	{
		$prev  = ' [Prev] ';       // we're on page one, don't enable 'previous' link
		$first = ' [First Page] '; // nor 'first page' link
	}

	// print 'next' link only if we're not
	// on the last page
	if ($pageNum < $maxPage)
	{
		$page = $pageNum + 1;
		$next = " <a href=\"$self?index_radio=6&submit=Submit&page=$page\">[Next]</a> ";

	 $last = " <a href=\"$self?index_radio=6&submit=Submit&page=$maxPage\">[Last Page]</a> ";
	} 
	else
	{
		$next = ' [Next] ';      // we're on the last page, don't enable 'next' link
		$last = ' [Last Page] '; // nor 'last page' link
	}


	echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;

I essentially want to change $rowsPerPage to equal the number of rows associated with each school. I'm totally at a loss here. If anyone has any ideas or can point me toward any advanced paging tutorials, I would very much appreciate it.

Thanks in advance.

Scott

[Weedpacket]

If you want each page to have all the rows for each school, then it's not really a pagination issue any more; it's just "Select all that stuff from active where school='Whatever'".

[scriderpsd]

The list of schools is dynamic, right now there are 28 of them. I think what I need to do is select all the schools and put that result set into an array and then cycle through that array in the WHERE clause. I'm not sure if I would use a for loop here. I've never seen that done before, putting the query inside a for loop so that in this instance I would be running the query 28 times.

It seems like this would be a fairly common way to build pages but I just can't seem to get my head around it.

Just so I make myself clear. I currently have 28 schools so I would have 28 pages (this number could change on the fly). The first page would contain all rows associated with school 1 (say 8 rows), the second page would contain all rows associated with school 2 (say 5 rows), and so on.

Anymore help would be much appreciated. I know all you guys are very busy so this newbie is very grateful.

scott

[Weedpacket]

Yes..... I kind of figured as much ..... are you now asking how you can get a list of the schools? "Select distinct school from active".

scriderpsd wrote:

I would be running the query 28 times.

But only once per school page.

[scriderpsd]

Okay, I put this on hold for a while but now I'm back on it.

So here's what I'm doing...

First I select the list of schools....

[scriderpsd]

Okay, I put this on hold for a while but now I'm back on it.

So here's what I'm doing...

First I select the list of schools....

$sqlSchool="SELECT DISTINCT school FROM active";

Then for each instance in the result set I need to create a query to pull the student info associated with each school.

		while ($rowSchool = mysql_fetch_array($resultSchool)){
			$sql="SELECT school, last_name, first_name, six_digit, home_address, disability, one_bus, one_time, two_bus, two_time FROM active WHERE school='".$rowSchool[school]."'ORDER BY school LIMIT $offset, $rowsPerPage";
			$result = mysql_query($sql,$conn);

			for ($i = 0; $i < mysql_num_rows($result); $i++) {
				echo("<TR>");
				$row_array = mysql_fetch_row($result);
				for ($j = 0; $j < mysql_num_fields($result); $j++) {
					echo("<TD align=left>" . $row_array[$j] . "</TD>");
				}
				echo("</TR>");
			}
}

This does not work as expected. I receive the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Finally, I need to implement the pagination. The rows per page should differ depending on the number of rows for each school.

This whole problem seems like it should be a fairly common exercise. If anyone can help, I would really appreciate it.

Thanks,

Scott