Good Evening/morning, I have a table which is paginated. The first page shows up in a primarycontent div, which is where i want it..however when clicking on page 2, 3..etch it opens up in a different link, I want it to open up in the primary content div like the first page, but can't figure out how to do it. Also when clicking the previous button it goes to page 1, instead of the page before the current page..ex on page 50, goes to page 1. Here is my code.
<?php
require_once ('mysqli_connect.php');
$display = 30;
if (isset($_GET['p']) && is_numeric ($_GET['p']))
{
$pages = $_GET['p'];
} else {
$q = "SELECT COUNT(NewCustomerID) FROM customer";
$r = @mysqli_query($dbc, $q);
$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
$records = $row[0];
if ($records > $display){//more than 1 page.
$pages = ceil($records/$display);
} else {
$pages = 1;
}
} // end of p IF
if (isset($_GET['s']) && is_numeric ($_GET['s']))
{
$start = $_GET['s'];
} else {
$start = 0;
}
$q = "SELECT(NewCustomerID) AS customerid,
(OldCustomerID) AS oldcustomerid,
(FirstName) AS FirstName,
(MiddleName) AS MiddleName,
(LastName) AS LastName,
(UserName) AS UserName,
(CarID) AS CarID,
(CarColorID) AS CarColorID,
(ComputerID) AS ComputerID,
(IsLaptop) AS LaptopID,
(RaceID) AS RaceID,
(ResidenceID) AS ResidenceID,
(BirthMonthID) AS BirthMonthID
FROM customer ORDER BY LastName ASC LIMIT $start, $display";
$r = @mysqli_query($dbc, $q); if(!$r){die(mysqli_error($dbc));}
Echo '<table>
<tr>
<td><b>NewCustomerID </b></td>
<td><b>OldCustomerID </b></td>
<td><b>FirstName </b></td>
<td><b>MiddleName </b></td>
<td><b>LastName </b></td>
<td><b>UserName </b></td>
<td><b>CarID </b></td>
<td><b>CarColorID </b></td>
<td><b>ComputerID </b></td>
<td><b>IsLaptop </b></td>
<td><b>RaceID </b></td>
<td><b>ResidenceID </b></td>
<td><b>BirthMonthID </b></td>
</tr>';
$bg = '#eeeeee'; // set initial back ground color
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
$bg = ($bg =='#eeeeee' ? '#ffffff' : '#eeeeee'); // switch the background color.
echo '<tr bgcolor="' . $bg . '">
<td>' . $row['customerid'] . '</td>
<td>' . $row['oldcustomerid']. '</td>
<td>' . $row['FirstName']. '</td>
<td>' . $row['MiddleName']. '</td>
<td>' . $row['LastName']. '</td>
<td>' . $row['UserName'].'</td>
<td>' . $row['CarID'].'</td>
<td>' . $row['CarColorID'].'</td>
<td>' . $row['BirthMonthID'].'</td>
<td>' . $row['ComputerID'].'</td>
<td>' . $row['LaptopID'].'</td>
<td>' . $row['RaceID'].'</td>
<td>' . $row['ResidenceID'].'</td>
</tr>';
} // end of while loop
echo '</table>';
mysqli_free_result($r);
mysqli_close($dbc);
// make the links to the other pages if necessary
if ($pages >1) {
// add some spaces and start a paragraph
echo '<br /> <p>';
// determine what page the script is on:
$current_page = ($start/$display)+1;
// if it's not the first page, make a previous link;
if ($current_page !=1) {
echo '<a href="#"' . ($start - $display) . ' &p=' . $pages . '">Previous </a> ';
}
for ($i = 1; $i <= $pages; $i++) {
if ($i != $current_page) {
echo '<a href="#" ' . (($display * ($i - 1))) . '&p=' . $pages . '">'
. $i . '</a> ';
}else{
echo $i . ' ';
}
}// end of FOR loop
// if it is not the last page, make a next button
if ($current_page != $pages) {
echo '<a href="#"' . ($start + $display) . '&p=' . $pages . '">Next</a>';
}
echo '</p>'; // close the paragraph
} // endo of links section
?>
First of all, you should pass in GET/POST parameter which will carry the page to show. After you grab it from there, fro example in variable $page, you can use it SQL query with keyword OFFSET and LIMIT, like LIMIT 10 OFFSET 2, that gives you exactly rows from 11 to 20 items numbers.
Hope that's exactly what you need.
EDITED
<?php
$route = '/index.php?p='; // where to go on click page
$total = ($records % $display) != 0 ? floor($records / $display) + 1 : $records / $display; // total pages count
$current = isset($_GET['p']) ? $_GET['p'] : 1; // current page number
$last = 1; // init value of the last page
?>
<p class="pager">
<?php if ($total <= 9): ?>
<?php for ($i = 1; $i <= $total; $i++): ?>
<a href="<?php echo $route, $i; ?>" <?php if ($current == $i) echo 'class="acitve"'; ?>><?php echo $i; ?></a>
<?php endfor; ?>
<?php else: ?>
<?php
$rbound = $current + 1;
$lbound = $current - 1;
if ($lbound < 1)
$lbound = 1;
if ($rbound > $total)
$rbound = $total;
for ($i = 1; $i <= 3; $i++):
?>
<a href="<?php echo $route, $i; ?>" <?php if ($current == $i) echo 'class="acitve"'; ?>><?php echo $i; ?></a>
<?php $last = $i; ?>
<?php endfor; ?>
<?php if ($lbound <= $last): ?>
<?php for ($i; $i <= $rbound; $i++): ?>
<a href="<?php echo $route, $i; ?>" <?php if ($current == $i) echo 'class="acitve"'; ?>><?php echo $i; ?></a>
<?php $last = $i; ?>
<?php endfor; ?>
<?php else: ?>
<?php echo $lbound == $last + 1 ? '' : '...'; ?>
<?php endif; ?>
<?php if ($lbound > 3 && $rbound < $total - 2): ?>
<?php for ($i = $lbound; $i <= $rbound; $i++): ?>
<a href="<?php echo $route, $i; ?>" <?php if ($current == $i) echo 'class="acitve"'; ?>><?php echo $i; ?></a>
<?php $last = $i; ?>
<?php endfor; ?>
<?php endif; ?>
<?php if ($rbound >= $total - 2): ?>
<?php for ($i = $lbound; $i < $total - 2; $i++): ?>
<a href="<?php echo $route, $i; ?>" <?php if ($current == $i) echo 'class="acitve"'; ?>><?php echo $i; ?></a>
<?php $last = $i; ?>
<?php endfor; ?>
<?php $last = $last > $total - 4 ? $last : $total - 2; ?>
<?php else: ?>
<?php echo $rbound == $total - 3 ? '' : '...'; ?>
<?php $last = $total - 2; ?>
<?php endif; ?>
<?php for ($i = $total - 2; $i <= $total; $i++): ?>
<a href="<?php echo $route, $i; ?>" <?php if ($current == $i) echo 'class="acitve"'; ?>><?php echo $i; ?></a>
<?php endfor; ?>
<?php endif; ?>
</p>
That's all in above gives you pager rendered as: 1 2 3 ... 12 13 14 ... 18 19 20, here current page is 13, so it shows first 3 pages number, current page +/- 1 page and last 3 page numbers. So for the first page it would be: 1 2 3 ... 18 19 20, same for the last. Also adds CSS class active to the current page link.
Save yourself a ton of time and use a pre-engineered grid. My grid of choice is DataTables It's got all the bugs and browser inconsistencies taken care of already, and paging is as simple as one line of code. If you want to get really simple, you could output your entire table and let datatables sort out the paging, as well as sorting, filtering, etc. If you're like me and have to deal with a lot of results, you can also load data via ajax and have datatables actually assemble the dom. Either way, it's quicker than writing your own.