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

$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

$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>";
}

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

Data sorting

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

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

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

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!