How to use PHP database connection to implement data query and update

How to use PHP database connection to implement data query and update

1. MySQL database connection

Before using database connection in PHP, you need to make sure The MySQL database server has been correctly installed and configured. Next, we will learn how to use PHP to connect to the MySQL database and perform data query and update operations.

1. Installation and configuration of MySQL

First, you need to install the MySQL database server. Depending on the operating system, you can choose to use the installation package officially provided by MySQL, or install it through an integrated development environment (such as XAMPP, WAMP, etc.).

After the installation is complete, you need to create a database and data table. It can be created using MySQL's command line tools or visual tools (such as phpMyAdmin).

2. Connecting to the database

In PHP, you can use MySQLi (MySQL Improved Extension) or PDO (PHP Data Objects) extension to connect to the MySQL database. Here we take MySQLi as an example:

<?php
$host = "localhost";
$username = "root";
$password = "";
$dbname = "test";

// 创建数据库连接
$conn = new mysqli($host, $username, $password, $dbname);

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败：" . $conn->connect_error);
}
echo "连接成功";
?>

In the above code, we use the new mysqli() method to create a database connection and pass in the connection information. If the connection fails, an error message will be output; if the connection is successful, "Connection successful" will be output.

3. Query data

After successfully connecting to the database, we can use SQL statements to perform data query operations.

<?php
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "没有结果";
}
?>

In the above code, we use the SELECT statement to query all the data in the users table, and obtain the associative array of each result through the fetch_assoc() method, and then for processing.

4. Update data

In addition to querying data, we can also use SQL statements to perform data update operations.

<?php
$sql = "UPDATE users SET age = 20 WHERE id = 1";

if ($conn->query($sql) === TRUE) {
    echo "更新成功";
} else {
    echo "更新失败：" . $conn->error;
}
?>

In the above code, we use the UPDATE statement to update the age field of the record with id 1 in the users table to 20.

5. Close the database connection

After using the database, remember to close the database connection and release resources:

<?php
$conn->close();
?>

2. PDO database connection

Similar to MySQLi, the steps to connect to the database using PDO extension are not much different.

1. Connect to database

<?php
$host = "localhost";
$username = "root";
$password = "";
$dbname = "test";

// 创建数据库连接
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "连接成功";
} catch(PDOException $e) {
    die("连接失败：" . $e->getMessage());
}
?>

2. Query data

<?php
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->rowCount() > 0) {
    while($row = $result->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "没有结果";
}
?>

3. Update data

<?php
$sql = "UPDATE users SET age = 20 WHERE id = 1";

if ($conn->exec($sql) === TRUE) {
    echo "更新成功";
} else {
    echo "更新失败";
}
?>

4. Close the database connection

The operation is the same as MySQLi. After using the database, remember to close the database connection:

<?php
$conn = null;
?>

Summary:

Through the above code example , we learned how to use PHP database connections to implement data query and update operations. MySQLi and PDO are commonly used PHP database extensions. You can choose the appropriate extension based on personal preferences and project needs. In order to ensure the security and reliability of data, attention must also be paid to preventing security issues such as SQL injection. I hope this article can help you use database connections in PHP.

The above is the detailed content of How to use PHP database connection to implement data query and update. For more information, please follow other related articles on the PHP Chinese website!