Bootstrap Pagination with MySQL Data

This PHP script connects to a MySQL database, retrieves a list of top movies from a top_100_movies table, and displays them one at a time with pagination controls. It calculates which movie to show based on the current page ($_GET['page']) and generates a responsive pagination bar using Bootstrap-style classes, allowing users to navigate through pages of movie data sorted by popularity.

Demo

Batman Begins (2005)

Driven by tragedy, billionaire Bruce Wayne dedicates his life to uncovering and defeating the corruption that plagues his home, Gotham City. Unable to work within the system, he instead creates a new identity, a symbol of fear for the criminal underworld - The Batman.

...

PHP

<div class="demo-container">
<?php
$conn = new mysqli ("server", "user", "password", "database");
mysqli_set_charset ($conn, "utf8");
if ($conn -> connect_error) {die ("Database Connection Error!");}

$adjacents = 2;
$query_c = "SELECT id, title, overview, popularity, date_format(release_date, '%Y') AS release_date FROM top_100_movies ORDER BY popularity DESC LIMIT 0, 140";
$result_c = mysqli_query($conn, $query_c);
$row_count = $result_c->num_rows;
$total_pages = $row_count;
$target_page = "";
$item_display_limit = 1;
$pgNr = $_GET['page']; 

if ($pgNr) 
$page_start = ($pgNr - 1) * $item_display_limit; 
else
$page_start = 0;
$query_r = "SELECT id, title, overview, popularity, date_format(release_date, '%Y') AS release_date FROM top_100_movies LIMIT $page_start, $item_display_limit";
$result_r = $conn->query($query_r); 
if ($pgNr == 0) $pgNr = 1;
$prev_page = $pgNr - 1;
$next_page = $pgNr + 1;
$last_page = ceil($total_pages/$item_display_limit);
$last_page_i = $last_page - 1;

$demoPag = ""; 

if ($last_page > 1) { 
$demoPag .= "<div class='pagination-container'>";
$demoPag .= "<ul class='pagination pagination-sm justify-content-center flex-sm-wrap'>";

if ($pgNr > 1) 
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$prev_page'>«</a></li>";

else
$demoPag.= "<li class='page-item disabled'><a class='page-link' href='#'>«</a></li>"; 

if ($last_page < 7 + ($adjacents * 2)) { 
for ($pgCount = 1; $pgCount <= $last_page; $pgCount++) { 

if ($pgCount == $pgNr)
$demoPag.= "<li class='page-item active'><a class='page-link disabled' href='#'>$pgCount</a></li>"; 

else
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$pgCount'>$pgCount</a></li>"; 
} 
}

elseif($last_page > 5 + ($adjacents * 2)) {

if ($pgNr < 1 + ($adjacents * 2)) {
for ($pgCount = 1; $pgCount < 4 + ($adjacents * 2); $pgCount++){

if ($pgCount == $pgNr)
$demoPag.= "<li class='page-item active'><a class='page-link disabled' href='#'>$pgCount</a></li>"; 

else
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$pgCount'>$pgCount</a></li>"; 
} 
$demoPag.= "<span class='pagination-dots'>...</span>";
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$last_page_i'>$last_page_i</a></li>";
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$last_page'>$last_page</a></li>"; 
} 

elseif ($last_page - ($adjacents * 2) > $pgNr && $pgNr > ($adjacents * 2)) {
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=1'>1</a></li>";
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=2'>2</a></li>";
$demoPag.= "<span class='pagination-dots'>...</span>";
for ($pgCount = $pgNr - $adjacents; $pgCount <= $pgNr + $adjacents; $pgCount++) {

if ($pgCount == $pgNr)
$demoPag.= "<li class='page-item active'><a class='page-link disabled' href='#'>$pgCount</a></li>"; 

else $demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$pgCount'>$pgCount</a></li>"; 
} 
$demoPag.= "<span class='pagination-dots'>...</span>";
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$last_page_i'>$last_page_i</a></li>";
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$last_page'>$last_page</a></li>"; 
} 

else {$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=1'>1</a></li>";
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=2'>2</a></li>";
$demoPag.= "<span class='pagination-dots'>...</span>";
for ($pgCount = $last_page - (2 + ($adjacents * 2)); $pgCount <= $last_page; $pgCount++) {

if ($pgCount == $pgNr)
$demoPag.= "<li class='page-item active'><a class='page-link disabled' href='#'>$pgCount</a></li>"; 

else $demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$pgCount'>$pgCount</a></li>"; 
} 
}
}

if ($pgNr < $pgCount - 1) 
$demoPag.= "<li class='page-item'><a class='page-link' href='$target_page?page=$next_page'>»</a></li>"; 

else $demoPag.= "<li class='page-item disabled'><a class='page-link disabled' href='#'>»</a></li>";
$demoPag.= "</div>\n";
$demoPag.= "</ul>\n"; 
}
?>

<?php
if ($result_r->num_rows > 0) {
while ($row = $result_r->fetch_assoc()) {
echo "<div class='demo-content'>"; 
echo "<div class='demo-content-header'>" . $row["title"] . " (" . $row["release_date"] . ")</div>"; 
echo "<div class='demo-content-body'>" . $row["overview"] . "</div>";
echo "</div>"; 
}
} 

else {
echo 'No Records Available';
echo "</div>"; 
} 
?>
</div>

<div class="pagination-container">
<?=$demoPag?>
</div>

Comma after each value, except the last one Display records Display records in a Bootstrap Table Display records in columns Display records with Bootstrap pagination Display records with CSS styles Escape special characters in a string Highlight the search term in results Replace unicode characters by utf-8 Search autocomplete with Typeahead Search records by an option Select rows for the current day and month Short commands

MySQL

Bootstrap pagination

Bootstrap Pagination with MySQL Data

About the author