How to use MySQL in PHP front-end to perform add, delete, modify and query operations

PHP is a popular open source server-side scripting language, and MySQL is a widely used relational database management system. In the process of web development, the combination of PHP and MySQL has become a powerful pair that can be used to build various types of web applications. This article will introduce how to use MySQL in the PHP front-end to perform add, delete, modify and query operations.

1. Connect to MySQL database

Before performing MySQL operations, you first need to establish a connection with the database. Here is an example:

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

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
echo "连接成功";
?>

The above code creates an instance connected to the MySQL database "myDB" using the mysqli class. If the connection fails, an error message will be printed.

2. Insert data

Inserting data in MySQL is a common operation. The following is sample code for inserting data into the database using the PHP front-end:

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

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

The above code will insert a new record into a table named "MyGuests" and output it to the screen.

3. Querying data

Querying data in MySQL is also a common operation. Here is a sample code that uses a PHP front-end to query data in a database:

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

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

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

$conn->close();
?>

The above code will retrieve data from a table named "MyGuests" and output it to the screen.

4. Updating and deleting data

Updating and deleting data are also common operations in MySQL. Here is a sample code that uses the PHP front-end to update and delete data in the database:

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

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 更新数据
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if ($conn->query($sql) === TRUE) {
    echo "记录更新成功";
} else {
    echo "Error updating record: " . $conn->error;
}

// 删除数据
$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) {
    echo "记录删除成功";
} else {
    echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

The above code will update the "lastname" field of the record with ID 2 in the table named "MyGuests" and delete the ID 3 records.

Summary

Through the above sample code, you can see how to use the PHP front-end to add, delete, modify and query the MySQL database. This is one of the essential skills in web development, and mastering these operations will greatly improve your ability and competitiveness in this field. But remember, when writing real applications, use more secure and efficient ways to pass and process your data.

The above is the detailed content of How to use MySQL in PHP front-end to perform add, delete, modify and query operations. For more information, please follow other related articles on the PHP Chinese website!