Can anyone explain to me how to do Pagination in MySQLi?
Or just give me some examples
I don’t know why anybody would use MySQLi as opposed to PDO. MySQLi defeats the object of where PHP has moved on in the last 9 years which is object orientated. PDO is OO but MySQLi is still procedural so for better practice, go for PDO.
As for pagination you would select the top amount of rows and offset the results.
The first result in Google for “PHP PDO Pagination” brings up: http://www.phpro.org/tutorials/Pagination-with-PHP-and-PDO.html
Which is a really good tutorial.
I’ve never heard such nonsense in my life!! :o :o
What is PHP’s mysqli Extension?
The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later.
The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:
Object-oriented interface
Support for Prepared Statements
Support for Multiple Statements
Support for Transactions
Enhanced debugging capabilities
Embedded server support
What is PDO?
PHP Data Objects, or PDO, is a database abstraction layer specifically for PHP applications. PDO provides a consistent API for your PHP application regardless of the type of database server your application will connect to. In theory, if you are using the PDO API, you could switch the database server you used, from say Firebird to MySQL, and only need to make minor changes to your PHP code.
Other examples of database abstraction layers include JDBC for Java applications and DBI for Perl.
While PDO has its advantages, such as a clean, simple, portable API, its main disadvantage is that it doesn’t allow you to use all of the advanced features that are available in the latest versions of MySQL server. For example, PDO does not allow you to use MySQL’s support for Multiple Statements.
and here are examples of Mysqli using both procedural and object-oriented
[php]<?php
$mysqli = mysqli_connect(“example.com”, “user”, “password”, “database”);
if (mysqli_connect_errno($mysqli)) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$res = mysqli_query($mysqli, “SELECT 'A world full of ’ AS _msg FROM DUAL”);
$row = mysqli_fetch_assoc($res);
echo $row[’_msg’];
$mysqli = new mysqli(“example.com”, “user”, “password”, “database”);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli->connect_error;
}
$res = $mysqli->query(“SELECT ‘choices to please everybody.’ AS _msg FROM DUAL”);
$row = $res->fetch_assoc();
echo $row[’_msg’];
?>[/php]
Source
I use mysqli only as it is the best of both worlds, FACT!
Red 
To answer the original question:
Here is a fully working pagination script
You just need to replace table_name_here with your actual table name and change $dbc to whatever your database connection is…
[php]<?php
// Number of Records to Display per Page
$fp_display = 8;
// limit number of page numbers (links) to show.
$fp_limitPageNumber = 3;
// Determine how many pages there are
if(isset($_GET[‘np’])) {
$fp_num_pages = $_GET[‘np’];
}
else {
// Count the number of records
$fp_query = “SELECT COUNT(*) FROM table_name_here”;
$fp_result = mysqli_query($dbc, $fp_query);
$fp_row = mysqli_fetch_array($fp_result, MYSQLI_NUM);
$fp_num_records = $fp_row[0];
// Calculate the number of pages
if($fp_num_records > $fp_display) {
// More than 1 page
$fp_num_pages = ceil($fp_num_records/$fp_display);
}
else {
$fp_num_pages = 1;
}
} // End of np IF
// Determine where in the database to start returning results
if(isset($_GET[‘s’])) { $fp_start = $_GET[‘s’]; } else { $fp_start = 0; }
// Run the Query
$fp_query = “SELECT * FROM table_name_here LIMIT $fp_start, $fp_display”;
$fp_result = mysqli_query($dbc, $fp_query);
$fp_num = mysqli_num_rows($fp_result);
// Check it ran ok
if ($fp_num > 0) {
// Make the liks to other pages (if necessary)
if ($fp_num_pages > 1) {
// Determine what page the script is on
$fp_current_page = ($fp_start/$fp_display) + 1;
// If it's not the first page, make a Previous button
if ($fp_current_page == 0) {
echo '<a>First</a>';
}
else {
echo '<a href="' . $_SERVER['PHP_SELF'] . '?s=0&np=' . $num_pages . '">First</a>';
}
// If it's not the first page, make a Previous button
if ($fp_current_page != 1) {
echo '<a href="' . $_SERVER['PHP_SELF'] . '?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a>';
}
else {
echo '<a>Previous</a>';
}
for($fp_i = 1; $fp_i <= $fp_num_pages; $fp_i++){
if($fp_i >= ($fp_current_page + $fp_limitPageNumber) || $fp_i <= ($fp_current_page - $fp_limitPageNumber)){
}
else {
echo ' <a href="' . $_SERVER['PHP_SELF'] . '?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';
}
}
// If its not the last page, make a Next button
if ($fp_current_page != $fp_num_pages) {
echo '<a href="' . $_SERVER['PHP_SELF'] . '?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a>';
}
else {
echo '<a>Next</a>';
}
// If its not the last page, make a last button
if ($fp_current_page != $fp_num_pages) {
echo '<a href="' . $_SERVER['PHP_SELF'] . '?s=' . ($num_pages-1) . '&np=' . $num_pages . '">Last</a>';
}
else {
echo '<a>Last</a>';
}
} // End of LINKS section
}
?>[/php]
Hope that helps,
Red
Ha! I stand corrected from 1 scouser to another, oh the irony. I don’t know why I had it in my head that mysqli was procedural but hey, can’t know everything!
Hi Scott, Hope you didn’t take too much offence to my post, was simply clarifying an important oversight on your behalf.
You was partly correct, mysqli IS procedural, however it can be used in object oriented format also, I prefer it myself, coming from the old mysql, the changeover was pretty simple.
You red or blue?
Oh no, didn’t take offence always good to get feedback! Red all the way!
Happy Days!! 