PHP database operation: make a list display through steps

In the previous chapter, we followed our "Eight Steps to Database Connection" and successfully completed 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 the character set

Connection, error judgment and character set selection have all been discussed above. 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 the SQL statement

We need to query the user ID, user name, time and IP. Find out. 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 query result 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 of data

We need to display all results in the form of a list . 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

1. When deleting, we divide it into single-select deletion and multi-select deletion.

2. When editing, we will select a user

We added a few small things to the code in the previous step to implement deletion and editing on the page.

Let’s take a look at the actual renderings to reason about the specific implementation process. The effects are as follows:

There are several key points in the implementation process:

1. When selecting 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.

2. When deleting multiple selections, 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

After we operate 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);
?>