Good Morning;
I'm at a loss with this issue.
I'm using a type of Pagination to control the display of the results per page. The issue is this :-->
When there are no more results to display. I.E. your query has ended, and you click the 'next' button, or you click the '#' of the next page, it re-queries the database, and starts off where the query ended. For example, if the queries last page was to display only 10 rows, but offset 50, then if I click on the 'next' button, it sends a query to the database of all the information in it, at that point it will send it back to me, and it will be 50 items in.
So...let's review...
Database sends query --> I get back 30 results.
I'm displaying 10 results per page.
Pagination works great for the first 3 pages.
If I click on 'next'. it shows me the rest of the database, starting 30 results offset.
Anyway to stop it from querying the database after my strings results have ended?
My code is below (arbitrary connection / variable information has been excluded)
Declare some varaibles...
$tableName="CompanySearch";
$targetpage = "company-search.php";
$limit = 10;
$query = "SELECT COUNT(*) as num FROM $tableName";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];
$stages = 3;
$page = mysql_escape_string($_GET['page']);
if($page){
$start = ($page - 1) * $limit;
}else{
$start = 0;
}
The actual query and page data are below.
// Get page data
$term = $_POST['location'];
$query1 = "select * from $tableName where BINARY CompanyLocation like '%$term%' order by CompanyLocation asc limit $start, $limit" ;
$result = mysql_query($query1) or die('Unable to populate.');
while($row = mysql_fetch_array($result))
{
echo '<div style="height:110px;margin-top:10px;border-bottom:1px solid #c0c0c0;border-right:1px solid #c0c0c0;width:535px;">';
echo '<img style="float:right;width:110px;margin-right:10px;" src="'.$row[5].'">';
echo "<br /> <span style=\"font-size:18px;font-weight:bold;color:rgb(14,101,175)\">".$row['CompanyName']."</span>";
echo '<br />Location : '.$row['CompanyLocation'];
echo '<br /> Company Industry : '.$row['CompanyIndustry'];
echo "<br /> Website: <a style=\"color:rgb(0,0,0);text-decoration:none;\" href=\"".$row[4]."\">".$row[4]."</a>";
echo '<br style=\"clear:both\" /><br /></div>';
}
Setup the pagination to calculate the necessary values...
// Initial page num setup
if ($page == 0){$page = 1;}
$prev = $page - 1;
$next = $page + 1;
$lastpage = ceil($total_pages/$limit);
$LastPagem1 = $lastpage - 1;
$paginate = '';
if($lastpage > 1)
{
Now we're going to get into the thick of things with the rest of the code, I'm sorry for the inline-styles, but it's just the way it has to be for right now.
$paginate .= "<div class='paginate' style='font-family:Arial, Helvetica, sans-serif;padding: 3px;margin: 3px;margin-top:50px;margin-bottom:20px;'>";
// Previous
if ($page > 1){
$paginate.= "<a style='padding:2px 5px 2px 5px;margin:2px;border:1px solid #c0c0c0;text-decoration:none;color:rgb(14,101,175);' href='$targetpage?page=$prev'>previous</a>";
}else{
$paginate.= "<span class='disabled' style='padding:2px 5px 2px 5px;margin:2px;border:1px solid #eee; color: rgb(14,101,175);'> previous </span>"; }
// Pages
if ($lastpage < 7 + ($stages * 2)) // Not enough pages to breaking it up
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page){
$paginate.= "<span class='current' style='margin: 2px;padding: 2px 5px 2px 5px;border: 1px solid #c0c0c0;font-weight: bold;background-color:rgb(14,101,175);color:rgb(255,255,255);'>$counter</span>";
}else{
$paginate.= "<a style='padding:2px 5px 2px 5px;margin:2px;border:1px solid #c0c0c0;text-decoration:none;color:rgb(14,101,175);' onmouseover=\"this.style.css='border:1px solid #c0c0c0;'\" href='$targetpage?page=$counter'>$counter</a>";}
}
}
elseif($lastpage > 5 + ($stages * 2)) // Enough pages to hide a few?
{
// Beginning only hide later pages
if($page < 1 + ($stages * 2))
{
for ($counter = 1; $counter < 4 + ($stages * 2); $counter++)
{
if ($counter == $page){
$paginate.= "<span class='current' style='margin: 2px;padding: 2px 5px 2px 5px;border: 1px solid #c0c0c0;font-weight: bold;background-color:rgb(14,101,175);color:rgb(255,255,255);'>$counter</span>";
}else{
$paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}
}
$paginate.= "...";
$paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
$paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";
}
// Middle hide some front and some back
elseif($lastpage - ($stages * 2) > $page && $page > ($stages * 2))
{
$paginate.= "<a href='$targetpage?page=1'>1</a>";
$paginate.= "<a href='$targetpage?page=2'>2</a>";
$paginate.= "...";
for ($counter = $page - $stages; $counter <= $page + $stages; $counter++)
{
if ($counter == $page){
$paginate.= "<span class='current' style='margin: 2px;padding: 2px 5px 2px 5px;border: 1px solid #c0c0c0;font-weight: bold;background-color:rgb(14,101,175);color:rgb(255,255,255);'>$counter</span>";
}else{
$paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}
}
$paginate.= "...";
$paginate.= "<a href='$targetpage?page=$LastPagem1'>$LastPagem1</a>";
$paginate.= "<a href='$targetpage?page=$lastpage'>$lastpage</a>";
}
// End only hide early pages
else
{
$paginate.= "<a href='$targetpage?page=1'>1</a>";
$paginate.= "<a href='$targetpage?page=2'>2</a>";
$paginate.= "...";
for ($counter = $lastpage - (2 + ($stages * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page){
$paginate.= "<span class='current' style='margin: 2px;padding: 2px 5px 2px 5px;border: 1px solid #c0c0c0;font-weight: bold;background-color:rgb(14,101,175);color:rgb(255,255,255);'>$counter</span>";
}else{
$paginate.= "<a href='$targetpage?page=$counter'>$counter</a>";}
}
}
}
// Next
if ($page < $counter - 1){
$paginate.= "<a style='padding:2px 5px 2px 5px;margin:2px;border:1px solid #c0c0c0;text-decoration:none;color:rgb(14,101,175);' href='$targetpage?page=$next'>next</a>";
}else{
$paginate.= "<span class='disabled' style='padding:2px 5px 2px 5px;margin:2px;border:1px solid #eee; color:rgb(14,101,175);'>next</span>";
}
$paginate.= "</div>";
}
// pagination
echo '<center>'.$paginate;'</center>'
?>
So there it is, the code in it's entirety....
The only thing I want, is for pagination to return no more results, past the scope of the query.
I hope I've made some sense of this. Appreciate any help I could get, thank you.
