How to use database query and result sorting function in PHP?

How to use database query and result sorting functions in PHP?

In PHP application development, it is often necessary to interact with the database and process data in the database. One of the most common tasks is to retrieve and sort data in a database using database query and result sort functions. This article will introduce how to use database query and result sorting functions in PHP.

First, we need to establish a connection to the database. In PHP, you can use mysqli or PDO extensions to connect to a variety of databases. The following is a code example that uses the mysqli extension to establish a connection with the MySQL database:

$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);
if ($conn->connect_error) {
    die("连接数据库失败: " . $conn->connect_error);
}

After establishing the database connection, we can use SQL statements to query the data in the database. The following is a code example that uses the mysqli extension to execute a query:

$sql = "SELECT * FROM table_name";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 处理查询结果
    while ($row = $result->fetch_assoc()) {
        echo "字段1: " . $row["column1"] . ", 字段2: " . $row["column2"] . "<br>";
    }
} else {
    echo "没有查询到结果";
}

In the above code example, we have used a SELECT query statement to retrieve data for all columns from the database table named "table_name". Then, we use the fetch_assoc() function to get the data row by row from the result set and print it out.

In addition to basic queries, sorting query results is also a very common requirement. In PHP, query results can be sorted using the ORDER BY clause. The following is a code example that uses the ORDER BY clause to sort the result set:

$sql = "SELECT * FROM table_name ORDER BY column1 DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 处理查询结果
    while ($row = $result->fetch_assoc()) {
        echo "字段1: " . $row["column1"] . ", 字段2: " . $row["column2"] . "<br>";
    }
} else {
    echo "没有查询到结果";
}

In the above code example, we sort the query results in descending order based on the "column1" column.

In addition to using the mysqli extension, PHP also supports the PDO extension to connect to the database and execute queries. A code example using PDO is as follows:

$dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
$conn = new PDO($dsn, $db_user, $db_pass, $options);

$sql = "SELECT * FROM table_name";
$result = $conn->query($sql);

if ($result->rowCount() > 0) {
    // 处理查询结果
    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
        echo "字段1: " . $row["column1"] . ", 字段2: " . $row["column2"] . "<br>";
    }
} else {
    echo "没有查询到结果";
}

In the above code example, we first set the PDO connection options, and then use the query() method of the PDO class to execute the query statement. Then, we use the fetch() method to obtain the query results and process them accordingly.

In summary, using database query and result sorting functions in PHP is a common task. By using mysqli or PDO extensions, we can easily connect to the database, execute queries, and sort query results. I hope the above sample code can help readers better understand and use these functions.

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