How to implement addition, modification and deletion of php mysql

As the needs of website development become more and more complex, data storage becomes more and more important. A database is a warehouse that stores and processes data, making it easy for us to store and query data in an efficient manner. PHP and MySQL are two open source software that can be combined to easily develop web-based applications. This article will introduce in detail how to use PHP to connect to the MySQL database and implement operations of adding, modifying and deleting data.

Connecting to MySQL database

In PHP, connecting to a MySQL database requires the use of the MySQLi (MySQL improved) extension. This extension provides all the functions and methods needed to work with MySQL database.

To connect to the MySQL database, you need to establish a database connection first. To establish a MySQLi connection, you need to use the mysqli_connect function as follows:

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

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

// 检测连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
echo "连接成功";
?>

In the above example, we use the mysqli_connect function to create a MySQLi connection and use $servername, $username, $password and $ The dbname variable represents the required connection parameters. After the connection is successful, we print a message to confirm that the connection has been established.

Insert data

To insert data into the MySQL database, you need to use the mysqli_query function to send SQL statements to the connected database.

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

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

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

// 插入数据
$sql = "INSERT INTO MyGuests (firstname, lastname, email) 
VALUES (&#39;John&#39;, &#39;Doe&#39;, &#39;john@example.com&#39;)";

if (mysqli_query($conn, $sql)) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

In this example, we inserted a row of data into the MyGuests table, including first name, last name, and email address. If inserting data is successful, we will output a message.

Update data

To update data in the database, you can use the mysqli_query function and the UPDATE statement. Here is an example that updates the email addresses of everyone with the last name Doe in the MyGuests table.

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

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

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

// 更新数据
$sql = "UPDATE MyGuests SET email=&#39;john.doe@example.com&#39; WHERE lastname=&#39;Doe&#39;";

if (mysqli_query($conn, $sql)) {
    echo mysqli_affected_rows($conn) . " 条记录被更新";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

After updating the email addresses of everyone with the last name Doe in the MyGuests table, we will output the number of rows affected.

Delete data

To delete data from the MySQL database, you need to use the mysqli_query function and the DELETE statement. The following is an example of removing everyone with the last name Doe from the MyGuests table.

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

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

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

// 删除数据
$sql = "DELETE FROM MyGuests WHERE lastname=&#39;Doe&#39;";

if (mysqli_query($conn, $sql)) {
    echo mysqli_affected_rows($conn) . " 条记录被删除";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

After deleting the records for everyone with the last name Doe in the MyGuests table, we will output the number of rows affected.

Summary

In this article, we introduced how to use PHP and MySQLi extensions to connect to a MySQL database and implement insert, update, and delete data operations. Of course, here are only the most basic operations and syntax, and there are more examples and methods that we need to learn and understand in depth. In actual use, we also need to pay attention to some security issues to avoid security issues such as SQL injection. I hope this article can help everyone quickly get started using PHP and MySQL to develop powerful web applications.

The above is the detailed content of How to implement addition, modification and deletion of php mysql. For more information, please follow other related articles on the PHP Chinese website!