PHP Pagination Script
I’m not going to reinvent the wheel by coming up with another pagination script instead I will suggest that you checkout and try PHP Pagination Script from PHPSense. Its a PHP class that is very easy to integrate with your existing applications by following these few simple steps:
Step One: Include the Pagination Class
include('ps_pagination.php');
Step Two: Connect to MySQL database
$conn = mysql_connect('localhost', 'username', 'password');
mysql_select_db('yourdatabase', $conn);
$sql = 'SELECT post_title FROM wp_posts WHERE post_type="post" ORDER BY ID DESC';
Step Three: Create the pagination object
The pagination object accepts four parameters:
- MySQL connection link
- SQL query
- Number of records to display per page. Defaults to 10
- Number of pagination links to display. Defaults to 5
$pager = new PS_Pagination($conn, $sql, 10, 5);
Step Four: The paginate() function returns a mysql result set
After creating the pagination object, call the paginate() function from the object. This function returns the paginated result set for the current page. You can use this result set just as you would use a standard MySQL result set.
$rs = $pager->paginate();
while($row = mysql_fetch_assoc($rs)) {
echo $row['post_title'],"\n";
}
Step Five: Display the full navigation
echo $pager->renderFullNav();
Finished Code
//Include the PS_Pagination class
include('ps_pagination.php');
//Connect to mysql db
$conn = mysql_connect('localhost','root','');
mysql_select_db('yourdatabase',$conn);
$sql = 'SELECT post_title FROM wp_posts WHERE post_type="post" ORDER BY ID DESC';
//Create a PS_Pagination object
$pager = new PS_Pagination($conn,$sql,10,10);
//The paginate() function returns a mysql result set
$rs = $pager->paginate();
while($row = mysql_fetch_assoc($rs)) {
echo $row['post_title'],"\n";
}
//Display the full navigation in one go
echo $pager->renderFullNav();
As you can see its very simple to use in your applications. Paginating pages is one of the most commonly requested functions in websites and this is one of the many scripts that can really help you with your projects. I have been looking for a pagination script for a while now and by far this is the best and most efficient pagination script I’ve used. As always feel free to suggest if you’re using any pagination scripts. Hoping to learn from you guys. Thanks for reading.
The script worked fine except you cannot write search query with a where clause or with an ordered by. I have tried modifying the query string in a few obvious places in the usage.php and ps_pagination.php but none have worked so far just getting errors.
Yes, i agree with the above person. We cannot write a query using where clause and ordered by. Its only useful for a simple select clause.
Thank you for your comments and sorry for the late reply. I updated the code, but before I did that I tried fetching records from a sample wordpress database in my localhost. I used a WHERE and ORDER BY statements and the query worked for me and returned the filtered results. Please try the updated code (I only edited some variables and quotes) and let me know what your results are. Thank you.
@John Nguyen: Thank you for your comment. You don’t need to modify the ps_pagination.php. You only need to modify usage.php. What errors are you getting?
@Shashi: Thank you for your comment. Are you getting any errors?
order by and where clauses working OK – fantastic script –
I do have a slight issue in regard to calling it via a form :
$sql = “SELECT * FROM submitteditems
WHERE MATCH (keywords) AGAINST (‘”.$_POST["search_query"].”‘ IN BOOLEAN MODE)”;
first page not a issue, displays the data as expected but the post value is erased when you click the next page – however this is one of the best that I have seen, I will try and program around it if I can.
Dam fine work..
Hi Raymond,
thanks for the script is there an example we can see of the outcome of the script? IE: the script in action?
Angelo
Hi Raymond.
I still cannot get my search query work. Well when i place the query at the $sql+”SELECT… WHERE post_title LIKE ‘%$d%’;
When i view it, i see that there are 2 pages but when i clicked on the 2nd page, instead of showing me the 2nd page, it showed me the whole contents….
Hey Micheal, did you manage to solve the page 2 problem?
Hi,
I have fixed the problem faced by Michael, I have passed on my search word i.e. keyword used in MySQL query to the second page through URL as a query string to the second page. This can be seen in the variable –
$pager = new PS_Pagination($conn, $sql, 4, 5, “param1=$keyword”);
I am extracting this keyword again for second page using if else
Thanks,
Himansshu H Singh
I hope the Code is being cut out….
You can see the code at this link – http://www.getontheweb.in/code.txt
Thanks,
Himansshu H Singh
hi! i folow wat you say to avoid the error on the second page but i still get the same error?pls reply..
10x a lot, with your script you helped me so much.
I have this set up to read a database with just under 6000 records. I pull the correct number of pages back but when I click on page 2 thru 300 and next they send me back to my home page. Acts as if it is not passing the query from page to page and send me back to defult. Any suggestions?
How do I setup article database and show results as 30 clickable links per page under 2 categories?
Ex. water website
————–
fresh | salt
————–
Clicking “fresh” should display 30 clickable links with pagination to show next 30 links.
Clicking “salt” should display 30 clickable links with pagination to show next 30 links.
What I have so far?
//Connect to mysql db
$conn = mysql_connect(‘hosthere’,'usernamehere’,'passwordhere’);
if(!$conn) die(“Failed to connect to database!”);
$status = mysql_select_db(‘databasehere’, $conn);
if(!$status) die(“Failed to select database!”);
$sql = ‘SELECT article_title FROM fresh_water_articles’;
$sql = ‘SELECT article_title FROM salt_water_articles’;
/*
* Create a PS_Pagination object
*
* $conn = MySQL connection object
* $sql = SQl Query to paginate
* 30 = Number of rows per page
* 5 = Number of links
* “param1=valu1¶m2=value2″ = You can append your own parameters to paginations links
*/
$pager = new PS_Pagination($conn, $sql, 10, 5, “param1=valu1¶m2=value2″);
ALSO: What code should I include in each .php article page and where?
Thanks.
Thank you for your pagination object article.
Hi,
Thank you PHPSense and you for the instructions.
But there is a problem that I am facing.
Only the First page works fine, even the page distribution is perfect.
My Second page and rest don’t show any result …This is my code. Need help soon. Thank you
<?php
if(isset($_POST['submit'])){
$firstName=$_POST['txtFirstname'];
$batchSelect=$_POST['batchSelect'];
$degreeSelect=$_POST['degreeSelect'];
$branchSelect=$_POST['branchSelect'];
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
mysql_select_db("alumni") or die(mysql_error());
// Selection Zone
echo '
Student Name
Father Name
Batch
Degree
Branch
Phone No.
Mobile No.
Email ID
Address
‘
;
//Include the PS_Pagination class
include(‘pagination\ps_pagination.php’);
$sql = “SELECT sname, fname, batch, branch, degree, phoneno, mobileno, email, address, currcomp, currlocation FROM AlumniDB WHERE
sname=’$firstName’ OR batch=’$batchSelect’ OR degree=’$degreeSelect’ OR branch=’$branchSelect’ “;
$rs_result = mysql_query ($sql);
$pager = new PS_Pagination($conn, $sql, 9, 0);
$rs = $pager->paginate();
while($row = mysql_fetch_assoc($rs)) {
echo ‘
‘ . $row['sname'] . ‘
‘ . $row['fname'] . ‘
‘ . $row['batch'] . ‘
‘ . $row['branch'] . ‘
‘ . $row['degree'] . ‘
‘ . $row['phoneno'] . ‘
‘ . $row['mobileno'] . ‘
‘ . $row['email'] . ‘
‘ . $row['address'] . ‘
‘;
}
echo ”;
/*
* Display page links: 1 2 3
* $prefix = Will be prepended to the page link (optional)
* $suffix = Will be appended to the page link (optional)
*
*/
echo $pager->renderNav(”, ”);
//Display the link to next page: >>
echo $pager->renderNext();
//Display the link to last page: Last
echo $pager->renderLast();
mysql_close($conn);
}
?>
we can’t use ps_pagination.php for search results
(
thanks for this nice article. i will use this script! thanks!
this ps_pagination.php cannot work with search results. First page is display but there’s problem on the second page.
You did Nice job ..Thanks…
..With previous code (ps_pagination.php)
i couldn’t Fetch second page’s results with ($_post Or $_get) where condition
It goes with empty where condition
If its possible assit me
$insid = $_SESSION['institution_id'];
$classname =$_GET["classname "];
$sql = “SELECT * FROM eventslist WHERE institution_id = ‘”.$insid.”‘ AND student_class =’”.$classname.”‘ ORDER BY id desc”;
with this code first page’s result only displaying .. from second page it shows empty result..
But session value insid works in all pages
Thx in advance
Regards,
Arun.k
where can i find
include(‘ps_pagination.php’);
??
where is the ps_pagination.php
Hi Zadji ,
Thx ,For your response,
Here am attaching Ps_pagination.php..
conn = $connection;
$this->sql = $sql;
$this->rows_per_page = (int)$rows_per_page;
if (intval($links_per_page ) > 0) {
$this->links_per_page = (int)$links_per_page;
} else {
$this->links_per_page = 5;
}
$this->append = $append;
$this->php_self = htmlspecialchars($_SERVER['PHP_SELF'] );
if (isset($_GET['page'] )) {
$this->page = intval($_GET['page'] );
}
}
/**
* Executes the SQL query and initializes internal variables
*
* @access public
* @return resource
*/
function paginate() {
//Check for valid mysql connection
if (! $this->conn || ! is_resource($this->conn )) {
if ($this->debug)
echo “MySQL connection missing”;
return false;
}
//Find total number of rows
$all_rs = @mysql_query($this->sql );
if (! $all_rs) {
if ($this->debug)
echo “SQL query failed. Check your query.Error Returned: ” . mysql_error();
return false;
}
$this->total_rows = mysql_num_rows($all_rs );
@mysql_close($all_rs );
//Return FALSE if no rows found
if ($this->total_rows == 0) {
if ($this->debug)
echo “Query returned zero rows.”;
return FALSE;
}
//Max number of pages
$this->max_pages = ceil($this->total_rows / $this->rows_per_page );
if ($this->links_per_page > $this->max_pages) {
$this->links_per_page = $this->max_pages;
}
//Check the page value just in case someone is trying to input an aribitrary value
if ($this->page > $this->max_pages || $this->page page = 1;
}
//Calculate Offset
$this->offset = $this->rows_per_page * ($this->page – 1);
//Fetch the required result set
$rs = @mysql_query($this->sql . ” LIMIT {$this->offset}, {$this->rows_per_page}” );
if (! $rs) {
if ($this->debug)
echo “Pagination query failed. Check your query.Error Returned: ” . mysql_error();
return false;
}
return $rs;
}
/**
* Display the link to the first page
*
* @access public
* @param string $tag Text string to be displayed as the link. Defaults to ‘First’
* @return string
*/
function renderFirst($tag = ‘First’) {
if ($this->total_rows == 0)
return FALSE;
if ($this->page == 1) {
return “$tag “;
} else {
return ‘php_self . ‘?page=1&’ . $this->append . ‘”>’ . $tag . ‘ ‘;
}
}
/**
* Display the link to the last page
*
* @access public
* @param string $tag Text string to be displayed as the link. Defaults to ‘Last’
* @return string
*/
function renderLast($tag = ‘Last’) {
if ($this->total_rows == 0)
return FALSE;
if ($this->page == $this->max_pages) {
return $tag;
} else {
return ‘ php_self . ‘?page=’ . $this->max_pages . ‘&’ . $this->append . ‘”>’ . $tag . ‘‘;
}
}
/**
* Display the next link
*
* @access public
* @param string $tag Text string to be displayed as the link. Defaults to ‘>>’
* @return string
*/
function renderNext($tag = ‘>>’) {
if ($this->total_rows == 0)
return FALSE;
if ($this->page max_pages) {
return ‘php_self . ‘?page=’ . ($this->page + 1) . ‘&’ . $this->append . ‘”>’ . $tag . ‘‘;
} else {
return $tag;
}
}
/**
* Display the previous link
*
* @access public
* @param string $tag Text string to be displayed as the link. Defaults to ‘<total_rows == 0)
return FALSE;
if ($this->page > 1) {
return ‘ php_self . ‘?page=’ . ($this->page – 1) . ‘&’ . $this->append . ‘”>’ . $tag . ‘‘;
} else {
return ” $tag”;
}
}
/**
* Display the page links
*
* @access public
* @return string
*/
function renderNav($prefix = ”, $suffix = ”) {
if ($this->total_rows == 0)
return FALSE;
$batch = ceil($this->page / $this->links_per_page );
$end = $batch * $this->links_per_page;
if ($end == $this->page) {
//$end = $end + $this->links_per_page – 1;
//$end = $end + ceil($this->links_per_page/2);
}
if ($end > $this->max_pages) {
$end = $this->max_pages;
}
$start = $end – $this->links_per_page + 1;
$links = ”;
for($i = $start; $i page) {
$links .= $prefix . ” $i ” . $suffix;
} else {
$links .= ‘ ‘ . $prefix . ‘php_self . ‘?page=’ . $i . ‘&’ . $this->append . ‘”>’ . $i . ‘‘ . $suffix . ‘ ‘;
}
}
return $links;
}
/**
* Display full pagination navigation
*
* @access public
* @return string
*/
function renderFullNav() {
return $this->renderFirst() . ‘ ’ . $this->renderPrev() . ‘ ’ . $this->renderNav() . ‘ ’ . $this->renderNext() . ‘ ’ . $this->renderLast();
}
/**
* Set debug mode
*
* @access public
* @param bool $debug Set to TRUE to enable debug messages
* @return void
*/
function setDebug($debug) {
$this->debug = $debug;
}
}
?>
//mine file
setDebug(true);
/*
* The paginate() function returns a mysql result set
* or false if no rows are returned by the query
*/
$rs = $pager->paginate();
?>
renderFullNav();
/*
* Or you can display the individual links for more
* control over HTML rendering.
*
*/
?>
Now i have mentioned like
$pager = new PS_Pagination($conn, $sql,3, 5, “param1=valu1¶m1=value1&conditionone=’$q’&conditiontwo=’$aa’&conditionthree=’$bb’”);
Now i could get values untill our pagination’s last page..
hello guys,
i m having the same issue as you, after i type a keyword and hit go button, i get results displayed fine on the first page, but when clicking the next link from the pagination, i get redirected to the home page. any idea on this please ????
check my code below :
<FORM method="post" action="”
>
Type a Keyword below please
Artist Album Song Title
connect(‘localhost’, ‘root’, ‘dA6wep7UUzq5tVfu’, $dbname))
die( $Bdd -> return_error() );
$query = “SELECT * FROM $tablename WHERE “.$type.” LIKE ‘%”.$keyword.”%’”;
// Envoi de la requete :
if( !$Result = $Bdd -> Send_Query($query, $link) )
die( $Bdd -> return_error());
}
if($type == ‘SongName’)
{
if(!$link2 = $Bdd -> connect(‘localhost’, ‘root’, ‘dA6wep7UUzq5tVfu’, $dbname))
die( $Bdd -> return_error() );
$query2 = “SELECT * FROM $tableN WHERE “.$type.” LIKE ‘%”.$keyword.”%’”;
// Envoi de la requete :
if( !$Result2 = $Bdd -> Send_Query($query2, $link2) )
die( $Bdd -> return_error());
}
//============================================================================
Hey guys, where can I find updated code, because I can’t get ORDER BY or WHERE clause to work…BTW the code is amazing…thanx
Nice work! Actually First time I am using pagination and I done it using ur script.. really gud job.. keep posting simple useful codes…
Hi!
I’m using jpaginate — I’m trying to configure the top one in this demo: http://tympanus.net/jPaginate/ at the bottom of my website http://www.bestadventuremovielist.com –
How can I implement this script with that pagination?
Thanks!