How to query in php

With the development of Internet technology, databases have become an important means of data storage. PHP is a widely used server-side scripting language that also plays an important role in website development. In PHP, querying the database is one of the common operations in development. So, this article will introduce how to perform query operations in PHP.

1. Connect to the database

Before performing the query operation, we need to connect to the database first. PHP provides a built-in function mysqli_connect(), which is used to open a new connection to the MySQL server. This function needs to pass in parameters such as server address, user name, password, and database name. The sample code is as follows:

<?php
$servername = "localhost";
$username = "yourusername";
$password = "yourpassword";
$dbname = "yourdbname";

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

// 检测连接是否成功
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

In the above code, we pass four parameters: server address, user name, password and database name. If the connection fails, error information is output through the mysqli_connect_error() function. If the connection is successful, "Connected successfully" is output.

2. Execute the query statement

After the connection is successful, we can use the mysqli_query() function to execute the query statement. It requires passing in two parameters: the connection object and the SQL statement to be executed. The sample code is as follows:

<?php
$sql = "SELECT id, name, age FROM users";
$result = mysqli_query($conn, $sql);

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

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

In the above code, we executed a SELECT statement to query the data of the id, name and age columns from the users table. We use the mysqli_query() function to execute the statement and store the result in $result. If the data is queried, the data in the result set is read line by line through the mysqli_fetch_assoc() function and output to the page. If no data is found, "0 results" is output. Finally, we close the connection using the mysqli_close() function.

3. Prevent SQL Injection

Although the basic query operation has been completed in the above example, due to the existence of SQL injection, we must be more cautious in actual development. PHP provides some functions to prevent SQL injection attacks.

1. mysqli_real_escape_string() function

This function is used to escape special characters in SQL queries, such as single quotes, double quotes, etc. We can use this function to escape the input data before executing the query operation to avoid malicious users from passing in SQL injection code. The sample code is as follows:

<?php
$name = mysqli_real_escape_string($conn, $_POST['name']);
$sql = "SELECT * FROM users WHERE name='$name'";
$result = mysqli_query($conn, $sql);

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

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

In the above code, we use the mysqli_real_escape_string() function to escape the $name entered by the user. When constructing the query statement, the escaped $name is inserted into the SQL statement to avoid SQL injection attacks.

2. Prepared Statements

In PHP, we can also use prepared statements to avoid SQL injection attacks. A prepared statement is to send a preprocessing command to the database before executing the SQL statement to tell the database the structure and data type of the SQL statement to be executed. Then, the query parameters are sent to the database together with the prepared statement to avoid SQL injection attacks. The sample code is as follows:

<?php
$stmt = $conn->prepare("SELECT * FROM users WHERE name=?");
$stmt->bind_param("s", $name);

$name = mysqli_real_escape_string($conn, $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    // 输出数据
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "0 results";
}

// 关闭连接
$stmt->close();
$conn->close();
?>

In the above code, we first created the prepared statement using the $conn->prepare() function, and bound the parameters using the $stmt->bind_param() function. Before executing the query operation, we escape the $name entered by the user and assign it to the $s variable in the binding parameter. Finally, we use the $stmt->execute() function to execute the prepared statement and the $stmt->get_result() function to obtain the query results, thus completing the query operation.

4. Conclusion

Querying the database in PHP is a very common operation, but due to the existence of SQL injection attacks, we must be more cautious. In actual development, we usually perform query operations in conjunction with the anti-injection measures introduced above. I hope this article can be helpful to query operations in PHP development.

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