PHP pagination

Monicag · April 25, 2023

Dear Sir

Earlier i did pagination by using PHP and MYSQl....but now i want to move on to mysqli . And when i converted MYSQL into MySQLi ...i am unable to fetch my data.

The code is given below. please tell how to convert this code to mysqli so that i can fetch my data from database.

<html>
<head>
<title>
Job Applications
</title>
<link href="pagination.css" rel="stylesheet" type="text/css" />
<link href="B_blue.css" rel="stylesheet" type="text/css" />
</head>
<body>
<?php

class PS_Pagination {
 var $php_self;
 var $rows_per_page = 10; //Number of records to display per page
 var $total_rows = 0; //Total number of rows returned by the query
 var $links_per_page = 5; //Number of links to display per page
 var $append = ""; //Paremeters to append to pagination links
 var $sql = "";
 var $debug = false;
 var $conn = false;
 var $page = 1;
 var $max_pages = 0;
 var $offset = 0;


 function PS_Pagination($connection, $sql, $rows_per_page = 10, $links_per_page = 5, $append = "") {
 $this->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'] );
 }
 }

 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 <= 0) {
 $this->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;
 }

 function renderFirst($tag = 'First') {
 if ($this->total_rows == 0)
 return FALSE;

 if ($this->page == 1) {
 return "$tag ";
 } else {
 return '<a href="' . $this->php_self . '?page=1&' . $this->append . '">' . $tag . '</a> ';
 }
 }

 function renderLast($tag = 'Last') {
 if ($this->total_rows == 0)
 return FALSE;

 if ($this->page == $this->max_pages) {
 return $tag;
 } else {
 return ' <a href="' . $this->php_self . '?page=' . $this->max_pages . '&' . $this->append . '">' . $tag . '</a>';
 }
 }


 function renderNext($tag = '>>') {
 if ($this->total_rows == 0)
 return FALSE;

 if ($this->page < $this->max_pages) {
 return '<a href="' . $this->php_self . '?page=' . ($this->page + 1) . '&' . $this->append . '">' . $tag . '</a>';
 } else {
 return $tag;
 }
 }

 function renderPrev($tag = '<<') {
 if ($this->total_rows == 0)
 return FALSE;

 if ($this->page > 1) {
 return ' <a href="' . $this->php_self . '?page=' . ($this->page - 1) . '&' . $this->append . '">' . $tag . '</a>';
 } else {
 return " $tag";
 }
 }

 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 <= $end; $i ++) {
 if ($i == $this->page) {
 $links .= $prefix . " $i " . $suffix;
 } else {
 $links .= ' ' . $prefix . '<a href="' . $this->php_self . '?page=' . $i . '&' . $this->append . '">' . $i . '</a>' . $suffix . ' ';
 }
 }

 return $links;
 }

 function renderFullNav() {
 return $this->renderFirst() . ' ' . $this->renderPrev() . ' ' . $this->renderNav() . ' ' . $this->renderNext() . ' ' . $this->renderLast();
 }

 function setDebug($debug) {
 $this->debug = $debug;
 }
}
?>
</body>
</html>

mac_gyver · April 25, 2023

first of all, don't bother with the mysqli extension. it is overly complicated and inconsistent, especially when dealing with prepared queries, which you need to use to prevent any sql special characters in a value from being able to break the sql query syntax, which is how sql injection is accomplished. instead, use the much simpler and more modern PDO extension.

here's a list a do's and don'ts for the posted code -

you need to use a prepared query when supplying any external, unknown, dynamic values to a query when it gets executed. this will require that you change any place there's an $sql input parameter to also include an array of input values, such as what would be present in a WHERE ... clause. you would also supply the offset and rows_per_page in the LIMIT term via prepared query place-holders and append the values to the array of input values.
don't SELECT all the rows of data to get the total number of rows. instead, use a SELECT COUNT(*) ... query, then fetch the COUNT(*) value.
the common part of the sql query, from the FROM ... term though to any HAVING ... term needs to be used for both the SELECT COUNT(*) ... and the data retrieval query. this is the value you should build in the $sql input parameter.
just about every SELECT query that returns more than one row should have an ORDER BY ... term so that the rows of data are in a desired order.
don't use PHP_SELF and all the related code/values. you can use relative urls in the links.
when you build the pagination links, start with a copy of any existing $_GET parameters, set/modify the page parameter, then use http_build_query to build the query string part of the links. this will eliminate the need for the $append value.
use exceptions for database statement errors (this is the default error mode for both the PDO and mysqli extensions in php8+) and in most cases simply let php catch and handle any database exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) this will eliminate the need for the existing error handling and debugging logic.
don't use the @ error suppressor, ever. there's no need. when learning, developing, and debugging code/query(ies), you should display all php errors. when running code on a live/public server, you should log all php errors.
there's no need for the pagination code to test for a valid database connection. if the database connection fails, the page shouldn't ever get to the point of trying to use the connection for anything.
the code for any page should be laid out in this general order - 1) initialization, 2) post method form processing, 3) get method business logic - get/produce data needed to display the page, 4) html document. the code to get the logical page of data and build the pagination links should be located in item #3 in this layout.
OOP is NOT about wrapping a class definition around your main code and adding $this-> in front of everything to make it work. for example, the offset value is only used in one place. there's no need for it to be a class property. just use a regular/local variable for the value.

Strider64 · April 25, 2023

The following are the basic steps in doing pagination.

// Grab the current page the user is on
if (isset($_GET['page']) && !empty($_GET['page'])) {
    $current_page = urldecode($_GET['page']);
} else {
    $current_page = 1;
}

$per_page = 1; // Total number of records to be displayed:


// Grab Total Pages
$total_pages = $gallery->total_pages($total_count, $per_page);


/* Grab the offset (page) location from using the offset method */
/* $per_page * ($current_page - 1) */
$offset = $gallery->offset($per_page, $current_page);

// Figure out the Links that you want the display to look like
$links = new Links($current_page, $per_page, $total_count, $category);

// Finally grab the records that are actually going to be displayed on the page
$records = $gallery->page($per_page, $offset, 'gallery', $category);

and this the actual function/method in grabbing the record(s) to be displayed

        $sql = 'SELECT * FROM gallery WHERE page =:page AND category =:category ORDER BY id DESC, date_added DESC LIMIT :perPage OFFSET :blogOffset';
        $stmt = $this->pdo->prepare($sql); // Prepare the query:
        $stmt->execute(['page' => $page, 'perPage' => $perPage, 'category' => $category, 'blogOffset' => $offset]); // Execute the query with the supplied data:
        return $stmt->fetchAll(PDO::FETCH_ASSOC);

Edited April 25, 2023 by Strider64

Sign In

PHP pagination

Recommended Posts

Monicag

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Strider64

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information