Php pagination on website

Question

As the data has grown the need for pagination is of great importance. Everything works fine. I have a limit in my SELECT statement and count the rows. The goal is to have pagination at the bottom of the page with the limit of 10. The page contains the latest rows in the database. Do i need to have

<?php

$servername     = "localhost";
$username       = "";
$password       = "";
$dbname         = "";


$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$nr     = 0;
$count = mysql_query("SELECT count(*) AS TOTAL FROM quotes");
$row = mysql_fetch_array($sum);

$sql = "SELECT * FROM quotes ORDER BY date DESC limit 10";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    $output     = "";
    while($row  = $result->fetch_assoc() ) {
        $topic   = trim($row["topic"]);
        $quote   = trim($row["quote"]);
        $author  = trim($row["author"]);
        $id      = trim($row["id"]);

        $output .= injectNColumnWrapper(3, $nr, "container row", $nr);
        $output .="<div class='col s12 m6 l4 z-depth-1'>";
        $output .="<div class='card-panel grey darken-4 white-text center'>";
        $output .=" <h5>Citat: {$id}</h5>";
        $output .="</div>";
        $output .="<pre class='flow-text black-text' wrap='soft'>";        
        $output .="<p class='flow-text-p citat'>&#34{$quote}&#34</p>";
        $output .="<p style='font-weight:bold; class='flow-text-p author'>{$author}</p>";
        $output .="<p class='flow-text-p topic'>{$topic}</p>";
        $output .="</pre>";
        $output .="<div class='content_wrapper'>";
        $output .="<h4></h4>";
        $output .="<div class='voting_wrapper' id='vote-{$id}'>";
        $output .="<div class='voting_btn'>";
        $output .="<div class='up_button'>&nbsp;</div>";
        $output .="<span class='up_votes'>0</span>";
        $output .="</div>";
        $output .="<div class='voting_btn'>";
        $output .="<div class='down_button'>&nbsp;</div>";
        $output .="<span class='down_votes'>0</span>";
        $output .="</div>";
        $output .="<br>";
        $output .="</div>";
        $output .="</div>";
        $output .="</div>";
        $nr++;

    }
    $output    .= "</div>";
    echo $output;
}else {
    echo "0 results";
}

$conn->close();


function injectNColumnWrapper($cols_per_row, $closePoint, $cssClass="container row", $nthElem=""){
    $blockDisplay       = "";
    if( ($closePoint == 0) ){
        $blockDisplay   = "<div class='" . $cssClass . " container_nr_" . $nthElem . "'>"  . PHP_EOL;
    }else if( ($closePoint % $cols_per_row) == 0 && ($closePoint != 0) ){
        $blockDisplay   = "</div><div class='" . $cssClass . " container_nr_" . $nthElem . "'>"  . PHP_EOL;
    }
    return $blockDisplay;
}
?>

Answer 1

$limit = 10;
$start = $nr * $limit
$sql = "SELECT * FROM quotes ORDER BY date DESC limit $start $limit";

Where $nr is number of current page -1, so if you have page 1, it should be 0, if 2 then 1, etc.

To get number of pages, you ned to take all your results count and make division by limit so for example 100/10 = 10 pages. Simple for loop should render pages.

Answer 2

Use offset in your query.

You may display a set of links in your pagination, then catch the param from the URL and finally construct the query with the proper offset.

Pagination links:

// Note: $row['TOTAL'] comes from your counting sql (count(*))
for ($i = 0; $i< $row['TOTAL']; $i++) {
echo " <a href='?p=$i'>$i</a> |";
}

Get the requested page in your URL:

$requestedPage = !empty($_GET['p']) ? intval($_GET['p']) : 0;

Construct the query:

$pagination = 10 * $requestedPage;
$sql = "SELECT * FROM quotes ORDER BY date DESC limit 10 OFFSET $pagination"; 

Answer 3

You should give start and length (offset) values to limit data in MySQL for pagination. EG :

SELECT * FROM table_name WHERE your_condition LIMIT 0,9 - to fetch first 10 records

SELECT * FROM table_name WHERE your_condition LIMIT 10,19 - to fetch second set of  10 records

Refer for example. And it is a duplicate question