How to use MySQL database in PHP programming?

With the continuous development of Internet technology, databases have become an important tool for storing data on the backend of websites. MySQL, as one of the best, is often used by PHP programmers to store website data. This article will introduce how to use MySQL database in PHP programming.

1. Establishing databases and data tables

Before using MySQL, you need to create a database first. It can be created using the MySQL console or phpMyAdmin. For example, to create a database named "testdb", you can use the following SQL statement:

CREATE DATABASE testdb;

After completing the creation of the database, you need to create a data table under the database. The data table structure needs to be designed according to actual needs. For example, you can create a data table named "users" to store the registration information of website users. The following is an example:

CREATE TABLE users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    password VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

This data table contains 5 fields: id, username, password, email and reg_date. Among them, id is the primary key and grows automatically; username and password are required fields, and email and reg_date have default values.

2. Connecting to the database

After writing the data table structure, you need to establish a connection between the PHP program and the MySQL database. The connection can be done using extensions such as mysqli or PDO. The following is an example of using the mysqli extension to establish a connection:

$servername = "localhost";  // 数据库服务器名称
$username = "username";     // 数据库用户名
$password = "password";     // 数据库密码
$dbname = "testdb";         // 数据库名称

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

// 检测连接是否成功
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

In this example, $servername, $username, $password and $dbname are the database server name, username, password and database name that need to be connected respectively. After the connection is successful, "Connected successfully" will be returned.

3. Execute SQL statements

After completing the database connection, you can execute SQL statements in the PHP program to add, delete, modify, and query the data table. The following is a simple example to insert a piece of user information into the data table:

$sql = "INSERT INTO users (username, password, email) VALUES ('john', '123456', 'john@example.com')"

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

In this example, $sql contains the SQL statement to be executed, which is executed using the mysqli_query function. If the SQL statement is executed successfully, "New record created successfully" will be output, otherwise an error message will be output.

4. Querying data

Querying data is one of the most commonly used operations when using a MySQL database. The following is an example to query the information of all users from the data table:

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // 输出数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - username: " . $row["username"]. " - email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 results";
}

In this example, $sql is the query statement, and the results are saved in $result. If the query result is greater than 0, all records will be output through loop traversal. If the query result is 0, "0 results" is output.

5. Update data

Updating data in the database is also one of the commonly used operations when using MySQL. The following is an example, changing the email of the record with username "john" in the data table to "john@example.org":

$sql = "UPDATE users SET email='john@example.org' WHERE username='john'";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

In this example, $sql contains the update statement, and the result is saved in $result. If the update is successful, "Record updated successfully" is output, otherwise an error message is output.

6. Delete data

Deleting data in the database is also one of the commonly used operations when using MySQL. The following is an example to delete the record with ID 10 in the data table:

$sql = "DELETE FROM users WHERE id=10";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

In this example, $sql contains the delete statement and the result is saved in $result. If the deletion is successful, "Record deleted successfully" is output, otherwise an error message is output.

Summary

Using the MySQL database in PHP programming is relatively simple. The key lies in the understanding of the database structure and SQL statements. Through the introduction of this article, readers should be able to master the basic operations of using MySQL database and how to operate it in combination with PHP programs.

The above is the detailed content of How to use MySQL database in PHP programming?. For more information, please follow other related articles on the PHP Chinese website!