How to: Display Data from Database in Pagination

1. Please refer to this post to display data in table

2. After displaying them in the table, follow the instructions attached on the comment section (//):

 //determine no of data to display per page

$results_per_page = 10;

//count all column from the database

$sql = "SELECT COUNT(*) FROM test_user";

$res = $dbx->query($sql);

$count = $res->fetchColumn();

// determine no of total pagination needed by dividing no of total columns ($count) with total data to display (10)

$number_of_pages = ceil($count/$results_per_page);

// determine which page number visitor is currently on

if(!isset($_GET['page'])){

    $page = 1;

}else{

    $page = $_GET['page'];

}

// determine the sql LIMIT starting number for the results on the displaying page

$this_page_first_result = ($page-1)*$results_per_page;

                        

$sql = "SELECT id, user_email, user_fullname, user_address, user_city, user_zip, user_state, user_country, user_tel, user_fax from test_user LIMIT ". $this_page_first_result . "," .$results_per_page;

$result = $dbx->query($sql);

// if result is not zero, display result

if($result-> rowCount() > 0){

while($row = $result-> fetch(PDO::FETCH_ASSOC)){

echo "<tr><td>".$row["id"]."</td><td>"

.$row["user_email"] . "</td><td>"

.$row["user_fullname"]."</td><td>"

.$row["user_address"]."</td><td>"

.$row["user_city"]."</td><td>"

.$row["user_zip"]."</td><td>"

.$row["user_state"]."</td><td>"

.$row["user_country"]."</td><td>"

.$row["user_tel"]."</td><td>"

.$row["user_fax"]."</td></tr>";

}

  echo "</table>";

}

// if result is zero, display 0 result

else{

 echo "0 result";

}

// loop: define $page = 1, as long as $page less or equal to $number_of_pages, increase $page ((in this case, it is: 1,2,3))

// in the loop, echo the pagination style you want to display. in this case, I use the one in Bootstrap 5 Pagination

for($page=1; $page<=$number_of_pages; $page++){

    echo '<ul class="pagination pagination-sm">

    <li>

    <span class="page-link"><a href="display-data.php?page='. $page. '">' . $page. '</a></span>

     </li>';

                            

}