Creating a page break in a report

Anthony1312002

I hope someone can help me. I'm trying to create a page break for this report so that when the record count exceeds 20 a break is inserted and the remaining records are printed to the next page. I've gotten some really good help from this site which enabled me to get this far.

<?php
//------------------------------------------------------------------------------------------------------
//----THIS PORTION OF THE SCRIPT IS PULLING REPORTS BY ACTIVITY-----------------------------------------

if ($opt1==1) {

$Query = "Select ANumber,FirstName,LastName,Department,TeamLeader,date_format(DateOfService,'%M/ %D/ %Y') AS ServiceDate,NumberOfHours,ActivityType,
Org,Description,TimeTakenWhen,SiteID
FROM activitytracking WHERE DateOfService BETWEEN '%".$POST[txtStartDate]."%'
AND '%".$POST[txtEndDate]."%' AND Department Like '%".$POST[Departments]."%'
AND SiteID Like '%".$POST[Sites]."%'
GROUP BY ActivityType,DateOfService,Department,ANumber,TeamLeader,Description
ORDER BY ANumber,DateOfService";

$result = mysql_db_query ($DBName, $Query, $Link);

//Fetch the results frm the database
print "<table border=1 align=center>";
print "<font size=4 color=blue>$Sites Community Service Report By Activity.</font><br>";
print "<font size=4> For the Date Range running between $POST[txtStartDate] AND $POST[txtEndDate].</font><br>";
print "<font size=4> YOU MUST PRINT THIS REPORT IN LANDSCAPE TO VIEW ALL RECORDS.</font>";
print "</table>";

print "<table border=1><tr><th bgcolor=#F0F0F0>Employee Number</th>";
  print "<th bgcolor=#F0F0F0>First Name</th><th bgcolor=#F0F0F0>Last Name</th><th bgcolor=#F0F0F0>Department</th><th bgcolor=#F0F0F0>Team Leader</th>";
    print "<th bgcolor=#F0F0F0>Date Of Service</th><th bgcolor=#F0F0F0>Hours</th><th bgcolor=#F0F0F0>Activity</th><th bgcolor=#F0F0F0>ORG</th><th bgcolor=#F0F0F0>Comments</th>";
       print "<th bgcolor=#F0F0F0>Involved When</th><th bgcolor=#F0F0F0>SITE</th></tr>";

while ($Row = mysql_fetch_row ($result)) {

print " <tr>";

print "<td align=Center><b> $Row[0]</b></td>
<td align=Center>$Row[1]</td>
<td align=Center>$Row[2]</td>
<td align=Center> $Row[3]</td>
<td align=Center> $Row[4]</td>
<td align=Center> $Row[5]</td>
<td align=Center> $Row[6]</td>
<td> $Row[7]</td>
<td> $Row[8]</td>
<td> $Row[9]</td>
<td align=Center> $Row[10]</td>
<td><b> $Row[11]</b></td>";

print "</tr>";
}

$Query = "Select Sum(NumberOfHours) AS Hours
FROM activitytracking WHERE DateOfService BETWEEN '%".$POST[txtStartDate]."%'
AND '%".$POST[txtEndDate]."%' AND Department Like '%".$POST[Departments]."%'
AND SiteID Like '%".$POST[Sites]."%'";

$result = mysql_db_query ($DBName, $Query, $Link);

while ($Row = mysql_fetch_row ($result)) {

print " <tr>";

print "<td align=Center bgcolor=#F0F0F0>///</td>
<td align=Center bgcolor=#F0F0F0>///</td>
<td align=Center bgcolor=#F0F0F0>///</td>
<td align=Center bgcolor=#F0F0F0>///</td>
<td align=Center bgcolor=#F0F0F0>///</td>
<td align=Center bgcolor=#F0F0F0><b><font size=5>Total Hours</b></td>
<td align=Center bgcolor=#F0F0F0><b><font size=5>$Row[0]</b></td>
<td align=Center bgcolor=#F0F0F0>///</td>
<td align=Center bgcolor=#F0F0F0>///</td>
<td align=Center bgcolor=#F0F0F0>///</td>
<td align=Center bgcolor=#F0F0F0>///</td>
<td align=Center bgcolor=#F0F0F0>///</td>
<td></td>";

print "</tr>";
}
}
mysql_close ($Link);
print "</table>";
?>
</body>
</html>

The_Chancer

If you count the number of rows, and in the while loop, count the number going out...

Something like
for($i=0;$i<$count;$i++)
while(.......)
{

code

if ($i%20)
{
enter page break here
}
}

Something like that
For info on forced page breaks see: This M$ page

Anthony1312002

Thanks for getting back to me. I must admit that I'm still really new to PHP and I haven't learned how to use the procedure you mentioned. How would I use this in my present scipt. Sorry about the lack of knowledge.

The_Chancer

<?php 
//------------------------------------------------------------------------------------------------------ 
//----THIS PORTION OF THE SCRIPT IS PULLING REPORTS BY ACTIVITY----------------------------------------- 

if ($opt1==1) { 

$Query = "Select ANumber,FirstName,LastName,Department,TeamLeader,d
ate_format(DateOfService,'%M/ %D/ %Y') AS ServiceDate,NumberOfHours,ActivityType, 
Org,Description,TimeTakenWhen,SiteID 
FROM activitytracking WHERE DateOfService BETWEEN '%".$_POST[txtStartDate]."%' 
AND '%".$_POST[txtEndDate]."%' AND Department Like '%".$_POST[Departments]."%' 
AND SiteID Like '%".$_POST[Sites]."%' 
GROUP BY ActivityType,DateOfService,Department,ANumber,Team
Leader,Description 
ORDER BY ANumber,DateOfService"; 

$result = mysql_db_query ($DBName, $Query, $Link); 
$count=mysql_num_rows($result);
//Fetch the results frm the database 
print "<table border=1 align=center>"; 
print "<font size=4 color=blue>$Sites Community Service Report By Activity.</font><br>"; 
print "<font size=4> For the Date Range running between $_POST[txtStartDate] AND $_POST[txtEndDate].</font><br>"; 
print "<font size=4> YOU MUST PRINT THIS REPORT IN LANDSCAPE TO VIEW ALL RECORDS.</font>"; 
print "</table>"; 

print "<table border=1><tr><th bgcolor=#F0F0F0>Employee Number</th>"; 
print "<th bgcolor=#F0F0F0>First Name</th><th bgcolor=#F0F0F0>Last Name</th><th bgcolor=#F0F0F0>Department</th><th bgcolor=#F0F0F0>Team Leader</th>"; 
print "<th bgcolor=#F0F0F0>Date Of Service</th><th bgcolor=#F0F0F0>Hours</th><th bgcolor=#F0F0F0>Activity</th><th bgcolor=#F0F0F0>ORG</th><th bgcolor=#F0F0F0>Comments</th>"; 
print "<th bgcolor=#F0F0F0>Involved When</th><th bgcolor=#F0F0F0>SITE</th></tr>"; 
for ($i=0;$i<$count;$i++)
while ($Row = mysql_fetch_row ($result)) { 

print " <tr>"; 

print "<td align=Center><b> $Row[0]</b></td> 
<td align=Center>$Row[1]</td> 
<td align=Center>$Row[2]</td> 
<td align=Center> $Row[3]</td> 
<td align=Center> $Row[4]</td> 
<td align=Center> $Row[5]</td> 
<td align=Center> $Row[6]</td> 
<td> $Row[7]</td> 
<td> $Row[8]</td> 
<td> $Row[9]</td> 
<td align=Center> $Row[10]</td> 
<td><b> $Row[11]</b></td>"; 

print "</tr>"; 
if ($i%20)
{
    //insert your page break here
    //put in table headers again for next page though
}
} 

$Query = "Select Sum(NumberOfHours) AS Hours 
FROM activitytracking WHERE DateOfService BETWEEN '%".$_POST[txtStartDate]."%' 
AND '%".$_POST[txtEndDate]."%' AND Department Like '%".$_POST[Departments]."%' 
AND SiteID Like '%".$_POST[Sites]."%'"; 

$result = mysql_db_query ($DBName, $Query, $Link); 

while ($Row = mysql_fetch_row ($result)) { 
print " <tr>"; 

print "<td align=Center bgcolor=#F0F0F0>///</td> 
<td align=Center bgcolor=#F0F0F0>///</td> 
<td align=Center bgcolor=#F0F0F0>///</td> 
<td align=Center bgcolor=#F0F0F0>///</td> 
<td align=Center bgcolor=#F0F0F0>///</td> 
<td align=Center bgcolor=#F0F0F0><b><font size=5>Total Hours</b></td> 
<td align=Center bgcolor=#F0F0F0><b><font size=5>$Row[0]</b></td> 
<td align=Center bgcolor=#F0F0F0>///</td> 
<td align=Center bgcolor=#F0F0F0>///</td> 
<td align=Center bgcolor=#F0F0F0>///</td> 
<td align=Center bgcolor=#F0F0F0>///</td> 
<td align=Center bgcolor=#F0F0F0>///</td> 
<td></td>"; 

print "</tr>"; 
} 
} 
mysql_close ($Link); 
print "</table>"; 
?> 
</body> 
</html>

There is still some work to be done by you though - although the PHP stuff is done.... 🆒

Anthony1312002

I think I'm getting close but I have a problem. The only thing showing on the report now is the Grand Total hours at the bottom of the report, but now of the individual records are shown. What did I do wrong?

</table>
<?php
//------------------------------------------------------------------------------------------------------
//----THIS PORTION OF THE SCRIPT IS PULLING REPORTS BY ACTIVITY-----------------------------------------

if ($opt1==1) {

$result = mysql_db_query ($DBName, $Query, $Link);

print "<table border=1><tr><th bgcolor=#F0F0F0>Employee Number</th>";
  print "<th bgcolor=#F0F0F0>First Name</th><th bgcolor=#F0F0F0>Last Name</th><th bgcolor=#F0F0F0>Department</th><th bgcolor=#F0F0F0>Team Leader</th>";
    print "<th bgcolor=#F0F0F0>Date Of Service</th><th bgcolor=#F0F0F0>Hours</th><th bgcolor=#F0F0F0>Activity</th><th bgcolor=#F0F0F0>ORG</th><th bgcolor=#F0F0F0>Comments</th>";
       print "<th bgcolor=#F0F0F0>Involved When</th><th bgcolor=#F0F0F0>SITE</th></tr>";

for ($i=0;$i<$count;$i++)
while ($Row = mysql_fetch_row ($result)) {

print " <tr>";

print "</tr>";
if ($i%20)
{
//insert your page break here
echo "</h3>";
//put in table headers again for next page though
}

}

$result = mysql_db_query ($DBName, $Query, $Link);

while ($Row = mysql_fetch_row ($result)) {

print " <tr>";

print "</tr>";
print "</h3>";
}
}

The_Chancer

You need to include $count=mysql_num_rows($result); after the first query - else it won't work at all....