In the previous chapter, we followed the steps and successfully completed the user registration.
Let’s do a background user list display. In the actual management process, we can modify the user's password and user-related information individually through the backend.
In the background, all users need to be displayed in a table form, which is the user list.
1. Connecting to the database, judging errors and setting character sets
Connection, error judgment and character set selection are all mentioned above Spoken. The old rule, the first step is to use mysqli_connect to connect to the database. As we said in the first section, you can add library selection in the fourth parameter. You no longer need to use the mysqli_select_db function to select a database again later.
The returned type is a connection resource. We must pass in resources in mysqli_errno, mysqli_error and mysqli_set_charset to determine which connection we are operating.
$conn = mysqli_connect('localhost', 'root', 'secret', 'book'); if (mysqli_errno($conn)) { mysqli_error($conn); exit; } mysqli_set_charset($conn, 'utf8');
2. Prepare and send SQL statements
What we need to query will be to find out the user ID, user name, time and IP. And use order by id to sort in descending order.
According to human thinking, people generally like to see the latest batch of registered users. The ID increases automatically, that is, the larger the ID is, the more new users are registered. Therefore, when we write the SQL statement, we write order by id desc.
$sql = "select id,username,createtime,createip from user order by id desc"; $result = mysqli_query($conn, $sql);
3. Judgment result
As long as the SQL statement is correct, the result variable $result will be true. . Therefore, we need to add one more step of judgment during implementation, not only $result. Moreover, determine the number of rows queried.
To query the number of rows, you can use mysqli_num_rows. This function requires that the result variable of the $result query be passed in.
If there are results, the list will be displayed. If there are no results, we can generate a prompt.
The code snippet is as follows:
if($result && mysqli_num_rows($result)){ //显示列表代码段 }else{ //提示没有结果的代码段 }
4. Loop display data
We need all the results Display it in list form. The rows and columns of a table are the same as those of a data table. Shown is easy to display.
First declare a table and output one row each time it loops. Display the results into each column.
The function used is mysqli_fetch_assoc, which returns an associative array.
This function reads a result set and moves backward once. When there is no result after reading, a bool value of false will be returned. Therefore, we choose while to cooperate with mysqli_fetch_assoc.
The result of each loop is assigned to $row, which is an associative array. So in this loop, I can display both rows and columns.
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 '</tr>'; } echo '</table>';
5. Add editing and deletion control
When deleting, we divide For single selection deletion and multi-selection deletion.
When editing, we will select a user
We add a few small things to the code in the previous step and they will be on the page Implemented deletion and editing.
Let’s take a look at the actual renderings to reason about the specific implementation process. The effects are as follows:
There are several aspects in the implementation process Key points:
When you select delete or edit, you need to use the get method to pass in the ID, so that we know which user is to be edited or deleted.
When you select multiple deletes, you need to pass in multiple users. Therefore, we can use the form form and the post method to submit this batch of user IDs.
Single selection deletion we can follow delete.php? Add the id and value and make a delete request when clicked.
echo '<td><a href="delete.php?id=' . $row['id'] . '">删除用户</a></td>';
The same goes for editing users. We add ? to edit.php and write the id and value. When clicked, we will know which user needs to be edited.
echo '<td><a href="edit.php?id=' . $row['id'] . '">编辑用户</a></td>';
For multi-select deletion, we need to use the checkbox in HTML. When passing in multiple user IDs, we need to add id[] after the name. Use a form to wrap the table, and add a submit tag outside the table to achieve multi-select deletion.
<form action="delete.php" method="post"> echo '<td><input type="checkbox" name="id[]" value="' . $row['id'] . '" /></td>'; echo '<input type="submit" value="删除" />'; echo '</form>';
The code is as follows:
echo '<form action="delete.php" method="post">'; echo '<table width="800" border="1">'; while ($row = mysqli_fetch_assoc($result)) { echo '<tr>'; echo '<td><input type="checkbox" name="id[]" value="' . $row['id'] . '" /></td>'; 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 '</table>'; echo '<input type="submit" value="删除" />'; echo '</form>';
6. Close the database connection
We have finished operating the database , close the database connection.
mysqli_close($conn);
The user list list.php code we implemented is as follows:
<?php $conn = mysqli_connect('localhost', 'root', 'secret', 'book'); if (mysqli_errno($conn)) { mysqli_error($conn); exit; } mysqli_set_charset($conn, 'utf8'); $sql = "select id,username,createtime,createip from user order by id desc"; $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 '</table>'; } else { echo '没有数据'; } mysqli_close($conn);