Simple PHP Pagination With Mysql

I have a wordpress database. I will pull posts from this database and display with pagination. Lets get started.

Make database connection. Obviously values below are for my own database. Change them to fit yours.

// Create connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "wptpl"; //

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

// Check connection

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

If connection is good proceed.

Typically paginated pages will have URLs like below

https://mydomain/?page=1
https://mydomain/?page=2
https://mydomain/?page=3
https://mydomain/?page=3

etc..

The parameter page is arbitrary and can be changed to anything you want. Say you want to use short p instead of full page. Paginated URLs will take the below form

https://mydomain/?p=1
https://mydomain/?p=2
https://mydomain/?p=3
https://mydomain/?p=3

etc..

Let us roll with page for this tutorial.

Capture the page parameter from URL with $_GET[‘page’]

if ( isset( $_GET['page'] ) )
    $page = $_GET['page'];
else 
    $page = 1;

How many records you want to display per page? Let us say 10.

$limit = 10;
$offset = ( $page-1 ) * $limit;

A paginated display will show records from the last offset up to limit.

Get total number of records.

$result = mysqli_query( $conn, "SELECT count(*) FROM wp_posts WHERE post_status = 'publish'" );
$rows = mysqli_fetch_array( $result, MYSQLI_NUM )[0];
$total = ceil( $rows / $limit );

Get records to display from database

$records = mysqli_query( $conn, "SELECT * FROM wp_posts WHERE post_status = 'publish' LIMIT $offset, $limit" );

Notice the use of $offset and $limit above. These must be set to get correct paginated results. Now you can display the records any way you want. 

// this will change as per your needs.
$recordChunk = mysqli_fetch_all( $records, MYSQLI_ASSOC );
foreach( $recordChunk as $index => $record ) {
    // display your records here
}

Close the connection.

mysqli_close( $conn );

Show the pagination links – 1,2,3,4 style. Notice that i have added a class page-link to a tag. This is for styling the navigation links. 

$counter = 1;
$pageLink = '';

// pagination links for style 1,2,3,4

while ( $counter <= $total ) { 

    $pageLink .= "<a href='?page=".$counter."'>".$counter."</a> ";        

    $counter++;
}

echo $pageLink;

Show the pagination links – Previous, Next style

// Pagination links for style - previous, next

$pre = '';
$next = '';

if ( $page <= 1 )
    $pre = '#';

else
    $pre = '?page=' . ( $page - 1 );

if ( $page >= $total )
    $next = '#';

else 
    $next = '?page=' . ( $page + 1 );

echo "<a href='?page=1'>First</a> ";

echo "<a href='$pre'>Previous</a> ";
echo "<a href='$next'>Next</a> ";

echo "<a href='?page=$total'>Last</a>";

Now let us add some styling. In the loop for pagination – style 1,2,3,4 – add the following code for adding an active class. This code will highlight the active page link. I have also added another class page-link to manage the general look of pagination elements.

while ( $counter <= $total ) { 

    if ( $counter == $page ) 
    $active = 'active';

    else
    $active = '';

    $pageLink .= "<a class='$active page-link' href='?page=".$counter."'>".$counter."</a> ";        
   $counter++;

}

Define these CSS classes in your style sheet.

 .page-link {
      padding: .5em;
  }

 .active {
     background: red;
     color: white;
 }

Styling for Previous-Next can also be done same way.

Let us wrap it up by putting all this code in once place

<!DOCTYPE html>
<html lang="en">

<head>

    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>

    <style>

        .page-link {
            padding: .5em;
        }

        .active {
            background: red;
            color: white;
        }

        .page-nav {
            padding: .5em;
            border: 1px solid #ccc;
        }

    </style>

</head>

<body>

<?php

// Create connection

$servername = "localhost";
$username = "root";
$password = "";
$database = "wptpl";

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

// Check connection

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


// Connection is good. Proceed!

if ( isset( $_GET['page'] ) )
    $page = $_GET['page'];

else 
    $page = 1;


$limit = 10;
$offset = ( $page-1 ) * $limit; 

$result = mysqli_query( $conn, "SELECT count(*) FROM wp_posts WHERE post_status = 'publish'" );
$rows = mysqli_fetch_array( $result, MYSQLI_NUM )[0];
$total = ceil( $rows / $limit );

$records = mysqli_query( $conn, "SELECT * FROM wp_posts WHERE post_status = 'publish' LIMIT $offset, $limit" );
$recordChunk = mysqli_fetch_all( $records, MYSQLI_ASSOC );

foreach( $recordChunk as $index => $record ) {
    echo $record['post_title'] . "<br>";
}

mysqli_close( $conn );

$counter = 1;
$pageLink = '';

while ( $counter <= $total ) { 

    if ( $counter == $page ) 
    $active = 'active';

    else
    $active = '';

    $pageLink .= "<a class='$active page-link' href='?page=".$counter."'>".$counter."</a> ";        

    $counter++;

}

echo $pageLink;   


// Previous - Next

$pre = '';
$next = '';

if ( $page <= 1 )
    $pre = '#';

else
    $pre = '?page=' . ( $page - 1 );


if ( $page >= $total )
    $next = '#';

else 
    $next = '?page=' . ( $page + 1 );


echo "<a class='page-nav' href='?page=1'>First</a> ";

echo "<a class='page-nav' href='$pre'>Previous</a> ";
echo "<a class='page-nav' href='$next'>Next</a> ";

echo "<a class='page-nav' href='?page=$total'>Last</a>";

?>
    
</body>
</html>
Php

Copyright Web/ Design/ Vista 2022 ©