How to perform database query traversal in PHP

In web development, PHP, as a commonly used back-end language, is often used to interact with the database and perform operations such as addition, deletion, modification and query of data. Among these operations, query (SELECT) is usually the most frequently used operation, especially in website development. This article will introduce how to use PHP to query and traverse the database to help developers better realize their business needs.

1. Basic usage

In PHP, we can use libraries such as mysqli and PDO to perform operations such as database connection and query. The following uses mysqli as an example to introduce how to query and traverse the database.

(1) Connect to the database

Before using mysqli to perform database operations, we need to connect to the database first. A simple connection example is shown below.

$mysql_host = 'localhost';//数据库所在的主机名
$mysql_user = 'root';//登录数据库的用户名
$mysql_password = '123456';//登录数据库的密码
$mysql_database = 'test';//数据库名

$con = mysqli_connect($mysql_host,$mysql_user,$mysql_password,$mysql_database);//连接数据库
if(mysqli_connect_errno($con)){//连接失败
    echo "连接数据库失败:".mysqli_connect_error();
}

(2) Execute the query statement

We can use the mysqli_query function to execute the query statement and get the query results. Below is an example of a query statement.

$sql = "SELECT * FROM user";//查询user表中的所有数据
$result = mysqli_query($con,$sql);//执行查询语句

(3) Traverse the query results

After getting the query results, we need to traverse the result set and take out each row of data in turn. The mysqli_fetch_array function can convert a row of data in the result set into an array form. The following is an example of traversing the result set.

while($row = mysqli_fetch_array($result)){
    echo $row['id'].' '.$row['name'].' '.$row['age'].'<br>';//输出每一行数据
}

2. Advanced use

For more complex query requirements, we need to use where, group by and other statements to further filter the data. Some commonly used advanced query techniques are introduced below.

(1) Find specific data

We can use the where statement to filter specific data in the database. Below is an example of finding user data with id 2.

$sql = "SELECT * FROM user WHERE id=2";//查询id为2的用户数据
$result = mysqli_query($con,$sql);

(2) Sorting

We can use the order by statement to sort the query results. Below is an example of sorting by age in ascending order.

$sql = "SELECT * FROM user ORDER BY age ASC";//按照年龄升序排序
$result = mysqli_query($con,$sql);

(3) Limit query results

We can use the limit statement to limit the number of query results. Below is an example of querying the first five pieces of user data.

$sql = "SELECT * FROM user LIMIT 5";//查询前五条用户数据
$result = mysqli_query($con,$sql);

(4) Aggregation function query

Aggregation functions can be used for statistics and analysis of data. Such as finding the average value, maximum value, minimum value, etc. Below is an example of querying the average age of users.

$sql = "SELECT AVG(age) FROM user";//查询用户平均年龄
$result = mysqli_query($con,$sql);
$row = mysqli_fetch_array($result);
echo $row[0];

3. Summary

This article introduces the basic and advanced usage methods of database query traversal using PHP, including connecting to the database, executing query statements, traversing query results, Find specific data, sort, limit query results, and aggregate function queries. These methods can help developers better implement their business needs.

The above is the detailed content of How to perform database query traversal in PHP. For more information, please follow other related articles on the PHP Chinese website!