Implementation of PHP+MySQL paging principle

Keywords: PHP Database SQL MySQL

;

Function introduction: including previous page, next page, first page, last page, jump page and other functions.

Code message: turn complexity into simplicity, and you will be enlightened.

Running screenshot:

  

Key steps:

  1. After the database is created, dozens of data are inserted to facilitate testing.
    CREATE TABLE `page` (
      `ID` int(10) NOT NULL AUTO_INCREMENT,
      `NAME` varchar(10) DEFAULT NULL,
       PRIMARY KEY (`ID`) 
    ) 
  2. Incoming page number: using the get method
    $page=$_GET['p'];
  3. Display data according to page number: set the display quantity of each page as pagesize and page number as page, then the data ID displayed on page is: (page-1) * pagesize to pagesize. The SQL statement is as follows
    $sql="SELECT * FROM page order by id ASC LIMIT ".($page-1)*$pageSize .",{$pageSize}";
  4. Display data
    $result=mysqli_query($conn,$sql);
        //Show data to table And table Format
        echo "<div class='content'>";
        echo "<table border=1 cellspacing=0 width=30% align=center>";
        echo "<tr><td>ID</td><td>NAME</td></tr>";
        while ($row = mysqli_fetch_assoc($result)) {
             echo "<tr>";
             echo "<td>{$row['ID']}</td>";
             echo "<td>{$row['NAME']}</td>";
             echo "<tr>";
        }
        echo "</table>";
        echo "</div>";
        //Release results
        mysqli_free_result($result);
  5. Realize the function of previous page and next page
    $page_banner = "<a href='".$_SERVER['PHP_SELF']."?p=" .($page-1) . "'><previous page</a>";
    $page_banner .= "<a href='".$_SERVER['PHP_SELF']."?p=" .($page+1) . "'><next page</a>";

    Knowledge supplement: $page_banner. = the function is cohesion; $_SERVER['PHP_SELF '] indicates the filename of the currently executing script.

  6. The above four steps can achieve simple paging effect. However, the page value can only be changed manually in the address bar to achieve paging effect. For example, if you enter http://localhost/beifen/mypage.php?p=5 in the address bar, it means that the fifth page is currently displayed.
  7. Perfection stage: the page value is obtained through the page column; the realization of the functions of the first page, the last page and the jump; the beautification of the interface.
  8. The complete code is as follows: mapage.php
    <!DOCTYPE html>
    <html>
    <head>
        <meta http-equiv="Content-type" content="text/html;charset=utf-8">
    </head>
    <style type="text/css">
        body{
            font-size: 12px;font-family: verdana;width: 100%;
        }
        div.page{
            text-align: center;
        }
        div.content{
            height: 300px;
        }
        div.page a{
            border:#aaaadd 1px solid;text-decoration: none;padding: 2px 5px 2px 5px;margin: 2px;
        }
        div.page span.current{
            border:#000099 1px solid;background-color: #000099;padding: 2px 5px 2px 5px;margin: 2px;color: #fff;font-weight: bold;
        } 
        div.page span.disable{
            border:#eee 1px solid;padding:2px 5px 2px 5px; margin: 2px;color:#ddd;
        }
        div.page form{
            display: inline;
        }
    </style>
    <body>
    <?php
        //Error setting level, except for notification class
        error_reporting('E_ALL&~E_NOTICE');  
        /**1---Incoming page number, GET with GET**/
        $page=$_GET['p'];
        /**2---Get the data according to the page number: PHP - > MySQL processing**/
        $host="localhost";
        $username="root";
        $password="";
        $db="test";
        $pageSize=10;
        $showPage=5; 
        //Connect to database,Process oriented
        $conn=mysqli_connect($host,$username,$password);
        if(!$conn){
            echo "Database connection failed";
            exit;
        }
        //Select the database to operate on
        mysqli_select_db($conn,$db);
        //Format database encoding
        mysqli_query($conn,"SET NAMES UTF8");
        //To write sql Get paging data SELECT * FROM Table name LIMIT Start position, number of pieces displayed
        $sql="SELECT * FROM page order by id ASC LIMIT ".($page-1)*$pageSize .",{$pageSize}";
        //hold sql Statement to database
        $result=mysqli_query($conn,$sql);
        //Show data to table No table Format
        echo "<div class='content'>";
        echo "<table border=1 cellspacing=0 width=30% align=center>";
        echo "<tr><td>ID</td><td>NAME</td></tr>";
        while ($row = mysqli_fetch_assoc($result)) {
             echo "<tr>";
             echo "<td>{$row['ID']}</td>";
             echo "<td>{$row['NAME']}</td>";
             echo "<tr>";
        }
        echo "</table>";
        echo "</div>";
        //Release results
        mysqli_free_result($result);
        //Total number of acquired data
        $total_sql="SELECT COUNT(*)FROM page";
        $total_result=mysqli_fetch_array(mysqli_query($conn,$total_sql));
        $total=$total_result[0];
        $total_pages=ceil($total/$pageSize);
        //close database
        mysqli_close($conn);
        /**3---Display data + display page bar**/
        $page_banner="<div class='page'>";
        //Calculate offset
        $pageoffset=($showPage-1)/2;
        //Display effect of front page and previous page in two cases
        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>";
        }else{
            $page_banner .="<span class='disable'>home page</span>";
            $page_banner .="<span class='disable'><previous page</span>";
        }
        //display
        $start=1;
        $end=$total_pages;
        //When the total number of entries is greater than the number of pages
        if($total_pages>$showPage){
            if($page>$pageoffset+1){
                $page_banner .="...";
            }
            if($page>$pageoffset){
                $start=$page-$pageoffset;
                $end=$total_pages>$page+$pageoffset?$page+$pageoffset:$total_pages;//Three stage type
            }
            //Display of the first few special page numbers. Currently refers to page number 1 or 2
            else{
                $start=1;
                $end=$showPage;
            }
            //The display of the last several special page numbers, currently page numbers 7 and 8 are displayed
            if($page+$pageoffset>$total_pages){
                $start=$start-($page+$pageoffset-$end);//Pay attention to understand this sentence
            }
        }
        //show page numbers
        for($i=$start;$i<=$end;$i++){
            //Display background color on current page number
            if($page==$i){
                $page_banner .="<span class='current'>{$i}</span>";
            }
            //Non current page number display
            else{
                $page_banner .= "<a href='".$_SERVER['PHP_SELF']."?p=" .$i . "'>{$i}</a>";    
            }    
        }
        if($total_pages>$showPage&&$total_pages>$page+$pageoffset){
            $page_banner .="...";    
        }
        //Display effect of last page and next page in two cases
        if($page<$total_pages){
            $page_banner .= "<a href='".$_SERVER['PHP_SELF']."?p=" .($page+1) . "'>next page></a>";
            $page_banner .= "<a href='".$_SERVER['PHP_SELF']."?p=$total_pages'>Tail page</a>";
        }else{
            $page_banner .="<span class='disable'>Tail page</span>";
            $page_banner .="<span class='disable'>next page></span>";
        }
        $page_banner .= "common{$total_pages}page,";
        $page_banner .= "<form action='mypage.php' method='get'>";
        $page_banner .= " To the first<input type='text' size=2 value='1' name='p'>page";
        $page_banner .= "<input type='submit' value='Determine'>";
        $page_banner .= "</form>";
        $page_banner .= "</div>";
        echo $page_banner;
    ?>
    </body>
    </html>   

Posted by the_damo2004 on Tue, 05 May 2020 09:30:37 -0700