Does anyone have a script that will show mysql query results as paged results? This is what I have at the moment and it does not pass the results to the second page... Ughh..
<?
$page = $GET['page'];
$pagenr= $GET['pagenr'];
$db_name="xxxx";
$table_name="xxxx";
$connection = @mysql_connect("localhost", "xxx", "xxxx") or die ("Couldn't connect.");
$db= @mysql_select_db($db_name, $connection) or die("Couldn't select database.");
$query = "SELECT id,city,state,description,type,cost, DATE_FORMAT(date, '%m/%d/%Y') AS date FROM rentals WHERE type = '$type' and zone = '$zone'";
$result = mysql_query($query) or die("ERROR");
$num_record = mysql_num_rows($result);
if($num_record > $display) { // Only show 1,2,3,etc. when there are more records found that fit on 1 page
// when the page is loaded first...
if(empty($pagenr)) {
$pagenr = 1;
}
// some variables
$display = 50; // number of records to display per page
$max_pages_to_print = 5; // number of pages to show, if you change this you also have to change the variable 'middlenumber', for example:increase this one with two, increase middlenumber with one
$startrecord = $pagenr * $display; // first record to show from the queryresult
$num_pages = intval($num_record / $display) + 1; // total number of pages
$loopcounter = 0; // counter for whileloop
$currentpage = $pagenr; // Page where we are at the moment
$middlenumber = 3; // Number will be decreased from variable currentpage in order to get the currentpage always in the middle
$colourcounter = 0; // Variable to change the background-color of the <td>
$i = 1; // variable that will print 1,2,3,etc..
$x = 0; // variable i use to put always the current, marked page in the middle
// actual stuff starts here
print("<table border=0 align=center><tr>");
if($currentpage >= $max_pages_to_print) {
print("<td><a href=\"{$_SERVER['PHP_SELF']}?pagenr=1\">First</a>...</td>");
}
//BEGIN LOOP
while($loopcounter < $max_pages_to_print) {
if($currentpage >= $max_pages_to_print) { // If user clicks om page higher than $max_pages_to_print
// Mark current page
if($currentpage == $i) {
print("<td>$i </td>"); // print pagenumbers
$i += 1; //increase pagenr
$loopcounter += 1; // increase loopcounter
}
// End marking
if($i > $num_pages) { // if last page has been printed, exit loop
break;
}
if($x == 0) {
$i = $currentpage - $middlenumber; // current page will always be printed in the middle
}
print("<td><a href=\"{$_SERVER['PHP_SELF']}?pagenr=$i\">$i</a> </td>"); // print pagenumbers
$x = $x + 1;
$i += 1; //increase pagenr
$loopcounter += 1; // increase loopcounter
}
else { // Else user clicks on a pagenumber lower $max_pages_to_print
// Mark current page
if($currentpage == $i) {
print("<td>$i </td>"); // print pagenumbers
$i += 1; //increase pagenr
$loopcounter += 1; // increase loopcounter
}
// End marking
if($i > $num_pages) { // if less than $max_mages_to_print, exit loop
break;
}
print("<td><a href=\"{$_SERVER['PHP_SELF']}?pagenr=$i\">$i</a> </td>"); // print pagenumbers
$i += 1; //increase pagenr
$loopcounter += 1; // increase loopcounter
} // End if
}
// END LOOP
if(($num_pages > $max_pages_to_print AND // notice the user that there are more pages
$i <= $num_pages)) {
print("<td>...<a href=\"{$_SERVER['PHP_SELF']}?pagenr=$num_pages\">Last</a></td>");
}
print("</tr></table>");
$startrecord = $startrecord - $display; // Set startrecord to the right position
// Some calculation for the lastrecord
if($currentpage == $num_pages) { // Last page...
$lastrecord = $num_record; // so $lastrecord = $num_record
}
else {
$lastrecord = ($currentpage * $display);
}
} // End of the first if-statement
// Some info
print("<table align=center>
<tr><td>You are now on page $currentpage</td></tr>
<tr><td>There are $num_pages pages in total</td></tr>
<tr><td>$num_record records are spread over $num_pages pages</td></tr>
<tr><td>Current display : $startrecord - $lastrecord</td></tr>
</table>");
// End info
// actual query, watch the end($startrec
// ord, $display)
$query2 = "SELECT id,city,state,description,type,cost, DATE_FORMAT(date, '%m/%d/%Y') AS date FROM rentals WHERE type = '$type' and zone = '$zone' order by date asc LIMIT $startrecord, $display";
$result2 = mysql_query($query2) or die("ERROR");
// print results on screen
print("<table border=0 align=center width=400 bgcolor=#0E711D cellpadding=\"2\" cellspacing=\"1\"><tr><td><font color=#FFFFFF></td><td><font color=#FFFFFF><b>City</b></font></td><td><font color=#FFFFFF><b>State</b></font></td><td><font color=#FFFFFF><b>Agency Name</b></font></td><td><font color=#FFFFFF><b>Type</b></font></td><td><font color=#FFFFFF><b>Date</b></font></td></tr>");
while(list($id,$city, $state,$description,$type,$cost,$date) = mysql_fetch_row($result2)) {
if ($colorcounter == 0) {
$colorbg = "#ECE28B";
}
else {
$colorbg = "#F4EFC1";
$colorcounter = $colorcounter - 2;
}
print("<tr><td bgcolor=$colorbg></td><td bgcolor=$colorbg>$city</td><td bgcolor=$colorbg>$state</td><td bgcolor=$colorbg>$contact</td><td bgcolor=$colorbg>$type</td><td bgcolor=$colorbg>$date</td></tr>");
$colorcounter = $colorcounter + 1;
}
print("</table>");
?>
