How to filter and sort data using database query and result filter functions in PHP?

How to use database query and result filtering functions to filter and sort data in PHP?

In PHP, the most common way to interact with the database is to obtain the required data through query statements. However, sometimes we need to filter and sort the query results to get data that meets specific conditions and order. To achieve this purpose, PHP provides a series of database result filtering functions.

In this article, we will introduce how to use database query and result filtering functions in PHP to filter and sort data, and will also give some example codes to help readers understand better.

First, we assume that we have connected to the database and have a table named "users" with the following fields: id, name, age, gender, email. We need to obtain all users older than 18 years old through the query statement and sort them in descending order by age. The following is a code example to achieve this goal:

<?php
// 建立数据库连接
$conn = new mysqli("localhost", "username", "password", "database");

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 构建查询语句
$sql = "SELECT * FROM users WHERE age > 18 ORDER BY age DESC";

// 执行查询
$result = $conn->query($sql);

// 检查查询结果是否为空
if ($result->num_rows > 0) {
    // 循环遍历查询结果
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - 姓名: " . $row["name"] . " - 年龄: " . $row["age"] . " - 性别: " . $row["gender"] . " - 邮箱: " . $row["email"] . "<br>";
    }
} else {
    echo "没有符合条件的数据";
}

// 关闭连接
$conn->close();
?>

The above code first establishes a database connection and constructs a query statement. SELECT * FROM users WHERE age > 18 ORDER BY age DESC means to select all users older than 18 years old from the "users" table and sort them in descending order by age. Note that we use the ">" symbol to filter.

Next, we execute the query and check if the result is empty. If the result is not empty, we iterate through the result set and output the data for each user. Finally, we close the database connection.

In addition to basic query and sorting, PHP also provides some result filtering functions, such as mysqli_num_rows() function to get the number of rows in the result set, mysqli_fetch_assoc() function to get an associative array for each row, etc.

If we only want to get a part of the results, we can use the LIMIT clause to limit the number of rows returned. For example, if we only want to get the first 10 pieces of data, we can modify the query statement to: SELECT * FROM users WHERE age > 18 ORDER BY age DESC LIMIT 10.

In addition, we can also use other conditions to filter, such as equal, unequal, fuzzy search, etc. The following is a sample code using equality conditions:

<?php
// 建立数据库连接
$conn = new mysqli("localhost", "username", "password", "database");

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 构建查询语句
$sql = "SELECT * FROM users WHERE gender = 'male'";

// 执行查询
$result = $conn->query($sql);

// ... 省略其他代码 ...

// 关闭连接
$conn->close();
?>

The above code queries all users whose gender is male. Note that we use the "='" symbol to filter for equal conditions.

To summarize, by using database queries and result filtering functions, we can easily filter and sort data in the database. The above example code is just an entry-level introduction, readers can perform more complex query and filtering operations according to actual needs. At the same time, in order to ensure the efficiency and security of database queries, it is recommended to use methods such as prepared statements and parameter binding to prevent SQL injection attacks.

The above is the detailed content of How to filter and sort data using database query and result filter functions in PHP?. For more information, please follow other related articles on the PHP Chinese website!