PHP+mysql database simple paging instance sql paging

A few days ago, Leng Yue wrote a blog post "php basic programming - php connection to mysql database - simple use of mysqli". After learning, many small partners know the connection between php and mysql database. Today, Leng Yue shares a simple paging example

First, let's look at the effect:

This case is very simple, so how can we determine what data is needed for the current page? The main core sql statements used are:

SELECT * FROM table name LIMIT start position, display the number of entries

We should use GET after the url to pass a page parameter, such as:
http:// page.php?p=1

Then we receive it with $_GET['p '], so we get the current page. Then we define a constant to hold the number of items displayed on a page. In this way, we can use the starting position to display the number of items to get the data.

The sample code is as follows:

 * Created by Cold moon white
 * WeChat official account: senior student cold month

    <meta http-equiv="Content-Type" content="text/html;charset=utf-8">
        text-align: center;
    div.content {
        height: 300px;
    } a{
        border: 1px solid #aaaadd;
        text-decoration: none;
        padding: 2px 5px 2px 5px;
        margin: 2px;
    } span.current{
        border: 1px solid #000099;
        background-color: #000099;
        padding: 4px 6px 4px 6px;
        margin: 2px;
        color: #ffffff;
        font-weight: bold;
    } span.disable{
        border: 1px solid #eeeeee;
        padding: 2px 5px 2px 5px;
        margin: 2px;
        color: #dddddd;
    } form{
        display: inline;


/*1.Incoming page numbers*/
$page = $_GET['p'];

/*2.Take out the data according to the page number, PHP - > MySQL*/
$host = "localhost";
$username = "root";
$password = "123456";
$db = "page";
$pageSize = 3;

$showPage = 5;

//Connect to database
$conn = mysqli_connect($host, $username, $password, $db);
if (!$conn) {
    var_dump("connection failed");

//Set the coding format of the database to prevent garbled code
mysqli_query($conn, "SET NAMES UTF8");

//Write sql to get the start position of SELECT * FROM table name LIMIT of paging data, and display the number of entries
$sql = "SELECT * FROM test LIMIT " . ($page - 1) * $pageSize . ",{$pageSize}";

//Transfer sql statements to data
$result = mysqli_query($conn, $sql);

//Processing data
echo "<div class='content'>";
echo "<table border='1' cellspacing='0' width='40%' align='center'>";
echo "<tr><td>ID</td><td>name</td></tr>";
while ($row = mysqli_fetch_assoc($result)) {
    echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td></tr>";
echo "</table></div>";

//Release results, close connection

//Total data obtained
$total_sql = "SELECT COUNT(*) FROM test";
$total_result = mysqli_fetch_assoc(mysqli_query($conn, $total_sql));
$total = $total_result['COUNT(*)'];
$total_page = ceil($total / $pageSize);


//3. Display data + paging bar
$page_banner = '<div class="page">';
if ($page > 1) {
    $page_banner .= "<a href='" . "{$_SERVER['PHP_SELF']}?p=1" . "'>home page</a>";
    $page_banner .= "<a href='" . "{$_SERVER['PHP_SELF']}?p=" . ($page - 1) . "'>Previous page</a>";
    $page_banner .= "<span class='disable'>home page</span>";
    $page_banner .= "<span class='disable'>Previous page</span>";

//Initialization data
$start = 1; //start page
$end = $total_page; //End page
$pageOffset = ($showPage - 1) / 2;
if ($total_page > $showPage) {  //If the total page is larger than the displayed page
    if ($page > $pageOffset + 1) { //If the current page is greater than the offset
        $page_banner .= "...";
    if ($page > $pageOffset) { //Current page is greater than offset
        $start = $page - $pageOffset;
        $end = $total_page > $page + $pageOffset ? $page + $pageOffset : $total_page;
    } else {
        $start = 1;
        $end = $total_page > $showPage ? $showPage : $total_page;
    if ($page + $pageOffset > $total_page) {
        $start = $start - ($page + $pageOffset - $end);

for ($i = $start; $i <= $end; $i++) {
    if ($page == $i){
        $page_banner .="<span class='current'>{$i}</span>";
        $page_banner .= "<a href='" . "{$_SERVER['PHP_SELF']}?p={$i}" . "'>{$i}</a>";


//Tail omission
if ($total_page > $showPage && $total_page > $page + $pageOffset){
    $page_banner .= "...";

if ($page < $total_page) {
    $page_banner .= "<a href='" . "{$_SERVER['PHP_SELF']}?p=" . ($page + 1) . "'>next page</a>";
    $page_banner .= "<a href='" . "{$_SERVER['PHP_SELF']}?p={$total_page}" . "'>Tail page</a>";
    $page_banner .= "<span class='disable'>next page</span>";
    $page_banner .= "<span class='disable'>Tail page</span>";
$page_banner .= "PageCount{$total_page},";
$page_banner .= "<form action='demo1.php' method='get'>";
$page_banner .= "To the first<input type='text' size='2' name='p'>page";
$page_banner .= "<input type='submit' value='Determine'>";
$page_banner .= "</form></div>";

echo $page_banner;


The buddy WeChat public who wants to get the source document can be concerned about the cold month's official account: the school student, cold moon. Reply: paging. Cold moon will send you the finished documents!

Welcome to my official account: my senior student, cold month, get exclusive learning resources and push daily dry cargo.
If you are interested in my topic content, you can also follow my blog:

Keywords: PHP SQL Database MySQL

Added by NeoPuma on Tue, 18 Feb 2020 05:31:48 +0200