Strange Pagination Question

[scriderpsd]

I have a database containing twenty schools. Each school has a group of students and student data associated with it. I have been tasked to create a web based report broken down by school. Example: page one contains student data associated with one school, page two contains student data associated with another school.

I have created a report where I can pull all student data for all schools and set the rows per page to 20 and cycle through that just fine. Here's what I have so far. More info provided upon request. Thanks in advance.

		$schoolName_array = array();
		$sqlSchool="SELECT DISTINCT school FROM active ORDER BY school";
		$conn = db_trans_connect();
		$resultSchool = mysql_query($sqlSchool, $conn);
		$total_schools = mysql_num_rows($resultSchool);

		for($i=0; $i < $total_schools; $i++) {
			$schoolName_array[] = mysql_field_name($resultSchool, $i);
		}

			foreach($schoolName_array as $schoolName) {
				$sql="SELECT school, last_name, first_name, six_digit, home_address, disability, one_bus, one_time, four_bus, four_time FROM active where school = '" .$schoolName. "' ORDER BY last_name LIMIT $offset, $rowsPerPage";
				$result = mysql_query($sql,$conn);

				for ($i = 0; $i < mysql_num_rows($result); $i++) {
					echo("<TR> \nl");
					$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>");
				}
			}

[bpat1434]

You could just run one query getting all the information you need into sub-arrays sorted by school:

$sql = "SELECT school, last_name, first_name, six_digit, home_address, disability, one_bus, one_time, four_bus, four_time FROM active ORDER BY last_name LIMIT $offset, $rowsPerPage";
$result = mysql_query($sql, $conn);

$schoolArray = array();  // Define empty array for this next step
while($row = mysql_fetch_array($result))
{
  $school = array_shift($row);
  $schoolArray[$school][] = $row;
}

// Now display a list of information per school:
foreach($schoolArray as $school=>$value)
{
  echo '<tr>
    <th>'.$school.'</th>
</tr>
<tr>
    <td>
      <table>
        <tr>
          <th>Last Name</th><th>First Name</th><th>Six Digit</th><th>Home Address</th><th>Disability</th><th>One Bus</th><th>One Time</th><th>Four Bust</th><th>Four Time</th>
        </tr>';
  foreach($value AS $ary)
  {
    echo '<tr>';
    foreach($ary AS $key=>$val)
    {
      echo '<td>'.$val.'</td>';
    }
    echo '</tr>';
  }
  echo '</table>
  </td>
</tr>';
}

Could be easier. Not sure what you're looking for, but that might do it.

[scriderpsd]

Thanks for your help bpat1434. This really gets me closer. I have not used array_shift before. Pretty cool.

Here's the problem with this output. It prints the first element in the <td> (school) but then each subsequent element, it prints twice. So essentially the output looks like this.

[INDENT]Ochoa Sanford Sanford Kimberly Kimberly 123523 123523 1607 W Shoshone 1607 W Shoshone HI HI 92 92 8:37:00 AM 8:37:00 AM 92 92 3:30:00 AM 3:30:00 AM[/INDENT]

Any ideas on why I'm getting double output?

[bpat1434]

Not sure. Do me a favor and add this above the foreach loops:

echo '<pre>';
var_dump($schoolArray);
echo '</pre>';

Let's look at the array structure and make sure we're itterating through it proplery 😉

[scriderpsd]

Okay, here's what we get...

[9]=>
array(19) {
  ["school"]=>
  string(5) "Ochoa"
  [0]=>
  string(7) "Sanford"
  ["last_name"]=>
  string(7) "Sanford"
  [1]=>
  string(8) "Kimberly"
  ["first_name"]=>
  string(8) "Kimberly"
  [2]=>
  string(6) "123523"
  ["six_digit"]=>
  string(6) "123523"
  [3]=>
  string(15) "1607 W Shoshone"
  ["home_address"]=>
  string(15) "1607 W Shoshone"
  [4]=>
  string(2) "HI"
  ["disability"]=>
  string(2) "HI"
  [5]=>
  string(2) "92"
  ["one_bus"]=>
  string(2) "92"
  [6]=>
  string(10) "8:37:00 AM"
  ["one_time"]=>
  string(10) "8:37:00 AM"
  [7]=>
  string(2) "92"
  ["four_bus"]=>
  string(2) "92"
  [8]=>
  string(10) "3:30:00 AM"
  ["four_time"]=>
  string(10) "3:30:00 AM"
}

[bpat1434]

Ah hah!!! Okay. Change the mysql_fetch_array() to mysql_fetch_assoc() and that should do it.

[scriderpsd]

Nice, that got me fixed up. Thanks so much for your help.

Now I want to break up my results into pages. All info for school one on the first page, info for school two on the second page, etc.

Would I need to hit the database and do something like...

SELECT DISTINCT COUNT(school) FROM active

???

I'm a little confused as to where to go next.

[bpat1434]

Well you're not talking about paginating it. You're talking about sectionalization. You're breaking the schools up into sections: school names. If you truly wanted to paginate it, you'd return the results regardless of what the school name was, and once the range specifications were met (as stated in the LIMIT clause).

Now, if you wanted to paginate per school (so it lists students in order grouped by school) then you'd want to add a WHERE clause stating that you want those students who are only in X school. Something simple like:

SELECT school, last_name, first_name, six_digit, home_address, disability, one_bus, one_time, four_bus, four_time 
FROM active 
WHERE school='$school'
ORDER BY last_name 
LIMIT $offset, $rowsPerPage

Or if you wanted a looser version so that if you have school1 and school2 (respectively) you could do:

SELECT school, last_name, first_name, six_digit, home_address, disability, one_bus, one_time, four_bus, four_time 
FROM active 
WHERE school LIKE '%$school%'
ORDER BY last_name 
LIMIT $offset, $rowsPerPage

The % is a wild-card marker. So basically that would match all schools that might or might not have something prepending and appending whatever the value of $school was. So if $school was set to "ool", both school1 and school2 would be returned. If it was set to "1", only school1 would be returned since "1" isn't found in the string "school2". You can read more about it at the mySQL Development site (dev.mysql.com).

[scriderpsd]

Thanks again for taking the time to help me out on this issue bpat. I've got a pretty good grasp on the SQL side of things but am having a little trouble wrapping my head around how to break this up into multiple pages. Let me try to better explain what I'm trying to do.

I'm not sure if you would term it "pagination" or "sectionalization." I do want to break my results up into individual pages. Page one contains info for school one, page 2 for school two, etc. I have the current output nicely formatted with each schools info broken up.

With normal pagination, I would look at the total number of rows in the database (let's say for example that it was 300) and then I would set the variable $rowsPerPage (let's say $rowsPerPage=20). I would then have my results broken into 15 pages. Right?

For this task, I don't need to know rows per page. I can get the total number of pages like this...

		$schoolCount="SELECT COUNT(DISTINCT school) FROM active";
		$result = mysql_query($schoolCount, $conn);

	$totalPages = $result

I think my biggest problem is figuring out what $offset is so I can use it in my LIMIT clause.

Again, in standard pagination, I would be doing something like this...

		$rowsPerPage = 18;

	// 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;

Then I could write my SQL like...

$sql="SELECT last_name, first_name, six_digit, home_address, school, one_bus, one_time, three_bus, three_time FROM active ORDER BY last_name LIMIT $offset, $rowsPerPage";

Would I know just totally get rid of my LIMIT clause???

I'm sorry if this is a bit wordy. I just think this should be a fairly simple problem and I'm probably making it out to be more complicated than it is. Again, I just can't get my head around it.

Any help is very much appreciated.

[scriderpsd]

Anyone else got any ideas on this? I'm still pretty stuck here and would really appreciate any help you could give. Thanks in advance.

Scott

[bpat1434]

Sorry for the long delay in response. Worked something like 36 hours in the past 3 days.... so I was computer deprived 🙁

Okay, I think I understand. If you want to "pagitnate" by school, use a WHERE $school = 'XSchoolName' instead of a LIMIT clause. If you want to paginate by number of rows, use a LIMIT clause. Understand?

Paginate by School

SELECT last_name, first_name, six_digit, home_address, school, one_bus, one_time, three_bus, three_time 
FROM active 
WHERE school='$school'
ORDER BY last_name

Pagination by Rows

SELECT last_name, first_name, six_digit, home_address, school, one_bus, one_time, three_bus, three_time 
FROM active 
ORDER BY last_name 
LIMIT $start, $end

Pagination by School & rows in school 😉

SELECT last_name, first_name, six_digit, home_address, school, one_bus, one_time, three_bus, three_time 
FROM active 
WHERE school='$school'
ORDER BY last_name 
LIMIT $start, $end

Hope that helps 🙂

[scriderpsd]

Okay, I understand what you are saying above except, where does the $school come from? I'm pulling it from the SELECT clause. How do I populate $school so that it can be used in the WHERE clause?

I hope this makes sense.

[bpat1434]

Okay, well you have a couple options:
1.) Query for the school name first
2.) Use a sub-query to get the school name.

My question is, if you want schoolA to be page 1, schoolB to be page 2, how will you know which page they're on? Once you answer that question, I think the question you just asked will be answered.