PHP executes database query statements

PHP is a very popular server-side programming language with a rich built-in database operation API. For developers, being able to skillfully use PHP to operate databases is a very important skill. In this article, we will focus on how to execute database query statements in PHP.

1. Connect to the database

Mysqli or PDO are usually used to connect to the database in PHP. The following is a sample code for using mysqli to connect to a MySQL database:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

2. Execute the query statement

After successfully connecting to the database, we can start executing the SQL query statement. The following is a sample code for executing a query statement using mysqli:

<?php
$sql = "SELECT id, name, email 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"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}

mysqli_close($conn);
?>

In the above code, we use the SELECT statement to read data from the database and store the data in an associative array through the mysqli_fetch_assoc() function. Finally, the query results are output through while loop traversal.

3. Prevent SQL injection attacks

When executing database query statements, special attention needs to be paid to SQL injection attacks. If no protective measures are taken, malicious users can perform illegal operations by splicing malicious SQL statements, such as tampering, deleting data, etc. Therefore, in actual development, prepared statements must be used to precompile SQL statements to avoid SQL injection attacks. The following is a sample code that uses mysqli prepared statements to execute query statements:

<?php
$stmt = $conn->prepare("SELECT id, name, email FROM users WHERE email= ? ");
$email = "john@example.com";
$stmt->bind_param("s", $email);
$stmt->execute();

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

$stmt->close();
$conn->close();
?>

In the above code, we first use the prepare() function to precompile the SQL statement, and combine the query conditions with the SQL through the bind_param() method Statement binding. Finally, obtain the query results through the get_result() function and obtain the data using the fetch_assoc() method.

Summary

In this article, we introduced how to use mysqli and PDO to connect to the database in PHP and execute query statements to prevent SQL injection attacks. By learning and mastering these skills, you will be able to operate the database more skillfully in PHP development and improve development efficiency and security.

The above is the detailed content of PHP executes database query statements. For more information, please follow other related articles on the PHP Chinese website!