PHP database operation: paginate users

In a real project, we write the host, user name, password, and library in the configuration file.

If it is written hard in the code, if the relevant information of the database server changes, it is obviously not in line with the programmer's thinking to modify all the code.

In addition, in every page that needs to connect to the database. We all need to write connections, judge errors, and set character sets, which is too troublesome. And it is not conducive to reusing these codes.

We can use the include series of functions mentioned before to achieve our goal. The example picture is as follows:

# Therefore, we can make a configuration file config.php. Set all the configurations that need to be used as constants. The code is as follows:

<?php
//数据库服务器
define('DB_HOST', 'localhost');
//数据库用户名
define('DB_USER', 'root');
//数据库密码
define('DB_PWD', 'secret');
//库名
define('DB_NAME', 'book');
//字符集
define('DB_CHARSET', 'utf8');
?>

We will extract the connection.php page. When we need to connect to the database in the future, we only need to include the connection.php file. The code is as follows:

<?phpinclude 'config.php';$conn = mysqli_connect(DB_HOST, DB_USER, DB_PWD, DB_NAME);if (mysqli_errno($conn)) {    mysqli_error($conn);    exit;}mysqli_set_charset($conn, DB_CHARSET);
?>

We can realize database connection by directly including the connection.php file in each file in the future:

include 'connection.php';

Complete the above preparations, and then complete the paging. The paging effect is as follows:

The page must contain the following basic elements:

When we control the page number, we implement page number control by passing in the page number value in the URL address bar. By appending the page number-related information to page.php, we can calculate more effective information. The effect of url controlling paging is as follows:

In the code implementation, these two values ​​​​are actually realized through the offset (offset) and quantity (num) after limit. of paging.

limit offset, num

Assume that each page displays 5 items. The final control limit formula for paging is as follows:

offset的值为 (n-1)*5
num 为规定的5

We implement the business through code:

1. Calculate the parameters required for paging

Total number

通过查询user表的count(id),得到总数$count。
$count_sql = 'select count(id) as c from user';

$result = mysqli_query($conn, $count_sql);

$data = mysqli_fetch_assoc($result);

//得到总的用户数
$count = $data['c'];

Current page

When you first enter the page.php page, the url is http://www.php.com/page.php, followed by no There is ?page=1 page identification number.

So we need to manually create a page identification number and pass it to the current page number variable $page.

We are afraid that there are decimals in the page passed by the user, so we do a forced type conversion: (int) $_GET['page'].

The first way of writing:

$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;

The second way of writing

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

Last page

Each page must be an integer. Just like math in elementary school. On average, 5.6 people should prepare how many apples. The answer must be 6.

If the page comes out with 20.3 pages, the rounding function ceil must be used. Let the number of pagination become 21.

We divide the total number by the number of data displayed on each page to get the total number of pages.

//每页显示数
$num = 5;
$total = ceil($count / $num);

Previous page and next page abnormal situation control

What if the user clicks the previous page on the first page and clicks the next page on the last page?

In this case, the data will exceed the range, causing no data to be displayed when we paginate.

Obviously this unusual situation needs to be taken into account. Therefore, if the first page is subtracted by one during paging, we make it the first page.
When adding one to the last page, we make it the last page, that is, the exception control is completed.

if ($page <= 1) {
    $page = 1;
}
if ($page >= $total) {
    $page = $total;
}

2. SQL statement

We said before that the core of paging is to control the display of each page through the offset and num in the SQL statement. number.

We also listed the specific formula above. We convert the company into code as follows:

$num = 5;
$offset = ($page - 1) * $num;

We apply $num and $offset to the SQL statement:

$sql = "select id,username,createtime,createip from user order by id desc limit $offset , $num";

Control the paging value in the URI

echo '<tr>
    <td colspan="5">
    <a href="page.php?page=1">首页</a>
    <a href="page.php?page=' . ($page - 1) . '">上一页</a>
    <a href="page.php?page=' . ($page + 1) . '">下一页</a>
    <a href="page.php?page=' . $total . '">尾页</a>
    当前是第 ' . $page . '页  共' . $total . '页
    </td>
    </tr>';

We finally connect the entire business together to achieve the final effect. The code is as follows:

include 'connection.php';


$count_sql = 'select count(id) as c from user';

$result = mysqli_query($conn, $count_sql);

$data = mysqli_fetch_assoc($result);

//得到总的用户数
$count = $data['c'];

$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;

/*
if (isset($_GET['page'])) {
    $page = (int) $_GET['page'];
} else {
    $page = 1;
}
 */

//每页显示数

$num = 5;

//得到总页数
$total = ceil($count / $num);

if ($page <= 1) {
    $page = 1;
}

if ($page >= $total) {
    $page = $total;
}


$offset = ($page - 1) * $num;

$sql = "select id,username,createtime,createip from user order by id desc limit $offset , $num";

$result = mysqli_query($conn, $sql);

if ($result && mysqli_num_rows($result)) {

    //存在数据则循环将数据显示出来

    echo '<table width="800" border="1">';

    while ($row = mysqli_fetch_assoc($result)) {

        echo '<tr>';

        echo '<td>' . $row['username'] . '</td>';
        echo '<td>' . date('Y-m-d H:i:s', $row['createtime']) . '</td>';
        echo '<td>' . long2ip($row['createip']) . '</td>';
        echo '<td><a href="edit.php?id=' . $row['id'] . '">编辑用户</a></td>';
        echo '<td><a href="delete.php?id=' . $row['id'] . '">删除用户</a></td>';

        echo '</tr>';
    }

    echo '<tr><td colspan="5"><a href="page.php?page=1">首页</a>  <a href="page.php?page=' . ($page - 1) . '">上一页</a>   <a href="page.php?page=' . ($page + 1) . '">下一页</a>  <a href="page.php?page=' . $total . '">尾页</a>  当前是第 ' . $page . '页  共' . $total . '页 </td></tr>';

    echo '</table>';

} else {
    echo '没有数据';
}

mysqli_close($conn);