PHP underlying database programming and implementation skills

As a popular server-side scripting language, PHP is widely used in website development and database operations. In actual projects, we often need to interact with the database, such as getting data, inserting data, updating data or even deleting data. In this article, we will delve into the underlying database programming and some implementation techniques of PHP, and explain it with specific code examples.

Connecting to the database

Before doing database programming, you first need to connect to the database. PHP provides a variety of ways to connect to the database, the most common is to use PDO (PHP Data Objects) or MySQLi extension. The following is a basic database connection example:

// 使用PDO连接数据库
$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'username';
$password = 'password';
try {
    $dbh = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
    echo '连接数据库失败：' . $e->getMessage();
}

// 使用MySQLi连接数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "mydatabase";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接数据库失败: " . $conn->connect_error);
}

Query data

Generally speaking, we need to obtain data from the database to display to the user. The following takes PDO and MySQLi as examples to show how to query data:

Query data using PDO:

$stmt = $dbh->prepare("SELECT * FROM mytable");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
    echo "ID: " . $row['id'] . "，Name: " . $row['name'] . "
";
}

Query data using MySQLi:

$sql = "SELECT * FROM mytable";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"]. "，Name: " . $row["name"]. "
";
    }
}

Insert data

In addition to querying data, we often need to insert new data into the database. Here is an example of inserting data using PDO and MySQLi:

Inserting data using PDO:

$stmt = $dbh->prepare("INSERT INTO mytable (name, email) VALUES (:name, :email)");
$name = "John";
$email = "john@example.com";
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->execute();
echo "新记录插入成功";

Inserting data using MySQLi:

$sql = "INSERT INTO mytable (name, email) VALUES ('John', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "
" . $conn->error;
}

Updating data and deleting data

Updating data and deleting data are similar to inserting data. Here are examples of PDO and MySQLi:

Using PDO to update data:

$stmt = $dbh->prepare("UPDATE mytable SET email='newemail@example.com' WHERE id=1");
$stmt->execute();
echo "数据更新成功";

Using MySQLi to update data:

$sql = "UPDATE mytable SET email='newemail@example.com' WHERE id=1";
if ($conn->query($sql) === TRUE) {
    echo "数据更新成功";
} else {
    echo "Error: " . $sql . "
" . $conn->error;
}

Use PDO to delete data:

$stmt = $dbh->prepare("DELETE FROM mytable WHERE id=1");
$stmt->execute();
echo "数据删除成功";

Use MySQLi to delete data:

$sql = "DELETE FROM mytable WHERE id=1";
if ($conn->query($sql) === TRUE) {
    echo "数据删除成功";
} else {
    echo "Error: " . $sql . "
" . $conn->error;
}

Conclusion

Through the introduction of this article, we have learned about the underlying database programming and some implementation techniques of PHP, and Explained with specific code examples. In actual projects, reasonable database programming skills can not only improve the readability and execution efficiency of the code, but also protect the security of the database. I hope this article can help you in PHP database programming.

The above is the detailed content of PHP underlying database programming and implementation skills. For more information, please follow other related articles on the PHP Chinese website!