Query database data with php

In website development, it is often necessary to obtain data from the database and display it on the page. As a commonly used server-side scripting language, PHP can easily query and operate databases.

PHP provides three main APIs to operate MySQL database: MySQLi, PDO and MySQL extension library. Among them, MySQLi is the most commonly used. Here we mainly introduce how to use MySQLi to query database data.

1. Connect to the database

First, you need to connect to the database in the PHP code. The way to use MySQLi is as follows:

$servername = "localhost";  // 数据库服务器名称
$username = "username";     // 数据库用户名
$password = "password";     // 数据库密码
$dbname = "database_name";  // 数据库名称

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

2. Query data

After the connection is successful, you can send a query request to the database. The following code demonstrates how to query all data from the database:

// 查询语句
$sql = "SELECT * FROM table_name";

// 执行查询
$result = mysqli_query($conn, $sql);

// 检测是否有数据
if (mysqli_num_rows($result) > 0) {
    // 输出数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["age"]. "<br>";
    }
} else {
    echo "0 results";
}

In this code, all data is first queried and the results are saved in the variable $result. Then, use a loop statement to traverse each row of result data and output the data for each row.

3. Query specific data

Sometimes you need to query specific data, you can add query conditions. For example, query all data whose age is greater than 20 years old:

// 查询语句
$sql = "SELECT * FROM table_name WHERE age > 20";

// 执行查询
$result = mysqli_query($conn, $sql);

// 输出数据
while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["age"]. "<br>";
}

In this code, a WHERE clause is added to the query statement to filter data that meets the conditions.

4. Query sorting

Sometimes you need to sort the query results according to the value of a certain column. You can use the ORDER BY clause in the query statement. For example, to sort the query results in ascending order of age:

// 查询语句
$sql = "SELECT * FROM table_name ORDER BY age ASC";

// 执行查询
$result = mysqli_query($conn, $sql);

// 输出数据
while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["age"]. "<br>";
}

In this code, an ORDER BY clause is added to the query statement to sort the query results in ascending order of the age column value.

5. Query Limitation

Sometimes you need to limit the number of query results, you can use the LIMIT clause. For example, query the first 5 pieces of data:

// 查询语句
$sql = "SELECT * FROM table_name LIMIT 5";

// 执行查询
$result = mysqli_query($conn, $sql);

// 输出数据
while($row = mysqli_fetch_assoc($result)) {
    echo "id: " . $row["id"]. " - Name: " . $row["name"]. " " . $row["age"]. "<br>";
}

In this code, a LIMIT clause is added to the query statement to limit the number of query results.

6. Close the connection

Finally, remember to close the database connection after the query is completed:

// 关闭连接
mysqli_close($conn);

Summary

The above is the use of PHP query Database data methods. It should be noted that in actual development, in order to avoid security issues such as SQL injection, the values ​​entered by the user need to be filtered and escaped. It is recommended to use prepared statements to execute SQL queries, which can effectively prevent SQL injection attacks.

Hope this article can help you better understand how PHP queries database data.

The above is the detailed content of Query database data with php. For more information, please follow other related articles on the PHP Chinese website!