PHP database op...LOGIN

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:

2015-10-13_561c9c5350f16.png

# 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:

2015-10-13_561c9c536b206.png

The page must contain the following basic elements:


#HomepageFirst page to enter The first pagedefaults to 1 when passing parameters via getPrevious pageCurrent page minus 1If the page number is the first page, subtract 1, it should be the first page Next pageAdd 1 to the current pageIf it is LastLast pageLast pageThe total number of items is divided by the number of displays per page to get the total number of pagesCurrent pageThe current page numberis the current page numberTotal number of pagestotal How many pages are thereThe total number of items divided by the number of displays per page

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:

QQ截图20161114161938.png

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

limit offset, num

ElementDescriptionRemark
Page numberget value in urllimit offset, quantity
Page 110, 5
Page 2 25,5
Page 3310,5
Page nn(n-1)*5,5


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);


Next Section
<?php $num = 5; $offset = ($page - 1) * $num; //我们将$num和$offset应用于SQL语句中: $sql = "select id,username,createtime,createip from user order by id desc limit $offset , $num"; //控制好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>'; //我们最后将整体业务串联起来实现最终效果,代码如下: 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); ?>
submitReset Code
ChapterCourseware