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

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

When developing web applications, you often need to use a database to store and retrieve data. As a popular server-side programming language, PHP provides many built-in functions and extensions to easily interact with databases.

This article will focus on how to use database query functions and result filter functions in PHP to perform conditional query and sorting of data. We will take the MySQL database as an example for demonstration and use the PDO extension for database connection and operation.

1. Connecting to the database

In PHP, we can use the PDO (PHP Data Objects) extension to connect to the database. The following is a simple sample code to connect to a MySQL database:

$host = "localhost";
$dbname = "mydatabase";
$username = "root";
$password = "";

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("数据库连接失败：" . $e->getMessage());
}

2. Data query

Next, we can use the query()## provided by PDO #Method to execute SQL query statements. The following is a simple example to query all user records in the data table named "users":

$query = "SELECT * FROM users";
$stmt = $pdo->query($query);

After the above code is executed, the

$stmt variable will save the handle of the query result. We can use the fetch() method to obtain data row by row from the handle:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // 处理每一行数据
    echo $row['username'] . ": " . $row['email'] . "<br>";
}

Conditional query

When performing data query, we often Data needs to be filtered based on certain criteria. The following is an example to query all user records whose names begin with "A" in the data table named "users":

$query = "SELECT * FROM users WHERE username LIKE 'A%'";
$stmt = $pdo->query($query);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['username'] . ": " . $row['email'] . "<br>";
}

In the above example, the

LIKE sub-child of SQL is used Sentences are used for fuzzy matching. A% represents any string starting with the letter "A".

Data sorting

In addition to conditional queries, we often need to sort the query results according to a certain field. The following is an example to query all user records in the data table named "users" and sort them in alphabetical order by user name:

$query = "SELECT * FROM users ORDER BY username";
$stmt = $pdo->query($query);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['username'] . ": " . $row['email'] . "<br>";
}

In the above example, SQL's

ORDER BY is used clause to specify the sort field and sort order. By default, they are sorted in ascending order (smallest to largest).

To sum up, this article introduces how to use database query and result filtering functions to perform conditional query and sorting of data in PHP. By using PDO extensions and corresponding SQL statements, we can easily perform database operations to meet different business needs. I hope this article can help you with database operations in PHP development!

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