PHP and MySQL database: How to connect to the database for CRUD operations

PHP is a popular server-side scripting language, while MySQL is a widely used relational database management system. The combination of the two can lay a solid foundation for web development. This article explains how to connect to a MySQL database through PHP code and perform common CRUD operations.

1. Connecting to the database

In PHP, connecting to the MySQL database requires the use of PHP's built-in mysqli extension. First, you need to create a mysqli object and use the mysqli_connect() function to specify the host name, user name, password and database name of the database. Here is sample code that connects to a local MySQL server and opens a database named "testdb":

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

$conn = mysqli_connect($host, $username, $password, $dbname);

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

The above code connects to a MySQL server and selects a database named "testdb". If the connection fails, an error message will be output and execution will be terminated.

2. New data

To insert new data into the MySQL database from PHP, you need to use the mysqli_query() function to execute a SQL INSERT statement. First you need to create an associative array containing the required data, and then use the INSERT INTO statement to insert the data in the array into the database. The following is a sample code to achieve this operation:

<?php
$name = "John";
$email = "john@example.com";
$phone = "1234567890";

$sql = "INSERT INTO customers(name, email, phone) VALUES('$name', '$email', '$phone')";

if (mysqli_query($conn, $sql)) {
    echo "新纪录插入成功!";
} else {
    echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}
?>

The function of the above code is to insert a new record into the data table named "customers", which contains name, email and phone data columns. These columns correspond to Fields in an associative array. If the insertion operation is successful, the message "New record inserted successfully!" will be output, otherwise a message containing error information will be output.

3. Reading data

Reading data from the MySQL database requires the use of the SELECT statement. You can use the mysqli_query() function to send query statements to the database, and the mysqli_fetch_assoc() function to convert the result set into an associative array. The following is a sample code that queries all records in the "customers" table:

<?php
$sql = "SELECT * FROM customers";
$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 结果";
}
?>

The function of the above code is to query all records in the data table named "customers" and output them to the screen. If the result set is not empty, a while loop is used to iterate through each row and convert each row into an associative array.

4. Update data

To update data in the MySQL database, you need to use the UPDATE statement. Update statements can be sent to the database using the mysqli_query() function. The following is sample code that updates the phone number of a customer named "John" to a new value:

<?php
$name = "John";
$new_phone = "0123456789";

$sql = "UPDATE customers SET phone='$new_phone' WHERE name='$name'";

if (mysqli_query($conn, $sql)) {
    echo "记录更新成功!";
} else {
    echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}

The above code updates the phone number of a customer named "John", setting it to new value. If the update operation is successful, the "Record update successful!" message is output, otherwise a message containing error information is output.

5. Delete data

Deleting data from a MySQL database requires the use of the DELETE statement. Delete statements can be sent to the database using the mysqli_query() function. The following is sample code to delete a customer record named "John":

<?php
$name = "John";

$sql = "DELETE FROM customers WHERE name='$name'";

if (mysqli_query($conn, $sql)) {
    echo "记录已成功删除!";
} else {
    echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}
?>

The above code deletes the customer named "John" from the data table named "customers". If the deletion operation is successful, the "Record was successfully deleted!" message is output, otherwise a message containing error information is output.

Summary

In this article, we learned how to use PHP code to connect to a MySQL database and perform common CRUD operations. Although the content covered in this article is at an introductory level, these operations are the basis for writing web applications using PHP. Proficiency in these operations can provide a good foundation for further learning PHP and building dynamic websites.

The above is the detailed content of PHP and MySQL database: How to connect to the database for CRUD operations. For more information, please follow other related articles on the PHP Chinese website!