I have a pretty nice system in my code which puts my SQL result on different pages, 10 rows on each page. This is great and works exactly the way it should, but I would like to limit the number of pages generated. My result is in a specific order, but after 25 pages I think it's enough (that would be 250 results). What do I add to the code below in order to maximize the amount of pages created? Thanks in advance!
<?php
// First I build my SQL which counts the amount of
// rows, how this SQL query looks like depends on the
// fields left blank and actually filled.
$sql = "SELECT COUNT(bedrijfsnaam) FROM profiles";
if ($bedrijfvariabele != " zoek op bedrijfsnaam.." &&
$bedrijfvariabele != "")
{
$sql .= " WHERE bedrijfsnaam = '".$bedrijfvariabele."'";
}
if ($rubriekvariabele != " zoek op rubriek.." &&
$rubriekvariabele != "" &&
$bedrijfvariabele != " zoek op bedrijfsnaam.." &&
$bedrijfvariabele != "")
{
$sql .= " AND (hoofdrubriek = '" . $rubriekvariabele . "' "
. " OR subrubrieken LIKE '%" . $rubriekvariabele . "%')";
}
if ($rubriekvariabele != " zoek op rubriek.." &&
$rubriekvariabele != "" &&
($bedrijfvariabele == " zoek op bedrijfsnaam.." ||
$bedrijfvariabele == ""))
{
$sql .= " WHERE (hoofdrubriek = '" . $rubriekvariabele . "' "
. " OR subrubrieken LIKE '%".$rubriekvariabele."%')";
}
if ($plaatsvariabele != " zoek op plaatsnaam.." &&
$plaatsvariabele != "" &&
(
($bedrijfvariabele != " zoek op bedrijfsnaam.." &&
$bedrijfvariabele != "") ||
($rubriekvariabele != " zoek op rubriek.." &&
$rubriekvariabele != "")
))
{
$sql .= " AND plaats = '".$plaatsvariabele."'";
}
if ($plaatsvariabele != " zoek op plaatsnaam.." &&
$plaatsvariabele != "" &&
(
($bedrijfvariabele == " zoek op bedrijfsnaam.." ||
$bedrijfvariabele == "") &&
($rubriekvariabele == " zoek op rubriek.." ||
$rubriekvariabele == "")
))
{
$sql .= " WHERE plaats = '".$plaatsvariabele."'";
}
// Now comes the interesting part for you guys
$rs_result = mysql_query($sql);
$row = mysql_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / 10);
$template = '<a style="float:none;display:inline-block;color:white;'
. 'width:22px;text-align:center;text-decoration:none;'
. 'font-size:20px;background-color:#483435;margin-left:4px"'
. ' href="%s".php?page=%s">%s</a>';
for ($i=1; $i <= $total_pages; $i++)
{
echo sprintf($template, $plaatsnaam7, $i, $i);
}
It sounds like you simply need to use a LIMIT keyword on your query.
Try something like this: SELECT * FROM PEOPLE LIMIT 10
from
for ($i=1; $i<=$total_pages; $i++) {
echo "<a style=\"float:none;display:inline-block;color:white;width:22px;text-align:center;text-decoration:none;font-size:20px;background-color:#483435;margin-left:4px\" href='".$plaatsnaam7.".php?page=".$i."'>".$i."</a> ";
};
to
if $total_pages>25 $total_pages=25;
for ($i=1; $i<=$total_pages; $i++) {
echo "<a style=\"float:none;display:inline-block;color:white;width:22px;text-align:center;text-decoration:none;font-size:20px;background-color:#483435;margin-left:4px\" href='".$plaatsnaam7.".php?page=".$i."'>".$i."</a> ";
};
Just let an if statement check which page you are on.
I wouldn't call this 'pretty nice system '. I big mistake is to load a hudge amount of data which may never be shown. You should load just the slice you need to show - as you said 10 rows per page. you should use SLQ limit clause - LIMIT offest, rowcount - example select * from mytable order by id limit 20,10 - will show 10 rows, starting form the 20th in the results ordered by id. you should pass a GET or POST param setting the number of current page 'page=2' and $ofsset = 10*($page-1); $rowcount = 10; 'select .... LIMIT '.$offset.','.$rowcount.
Look at this old but nice class which perfectly shows a good pager system: split_page_results.php. Some parts should be rewriten to follow the new PHP rules, but the idea is clearly implemented.
Here yopu can find an usage hint: enter link description here. Good luck.