Connect to MySQL database using PHP

With the increasing needs of web development and data storage, connecting to the database has become a common task in PHP development. Among them, the MySQL database is a widely used relational database, and PHP also provides a complete set of APIs, making it very simple to connect to the MySQL database. In this article, we will introduce how to use PHP to connect to a MySQL database and perform common database operations.

1. Preparation work

Before starting to connect to the MySQL database, we need to ensure that the following conditions are met:

• A computer with PHP and MySQL server.
• A MySQL database, and the tables to be used have been created.
• The correct database username and password, as well as the database host address, are ready.

If you have completed the above preparations on the local or remote server, then we can start connecting to the MySQL database.

2. Connecting to the database

PHP uses the mysqli module to provide a set of APIs for connecting to the MySQL database, which can easily establish a connection with the MySQL database. In addition, the mysqli module provides advanced features such as prepared statements and transactions to operate the database more safely and efficiently. Next, we will show how to use mysqli to connect to a MySQL database:

$servername = "localhost";           // 数据库主机地址
$username = "yourusername";          // 数据库用户名
$password = "yourpassword";          // 数据库密码
$dbname = "yourdatabase";            // 数据库名称

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

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

In the above example, we first declared the database host address, database username, database password, and database name to connect to. Next, a connection instance is created using the mysqli_connect() function. If the connection is successful, we can print a confirmation message on the screen, otherwise we will print an error message and stop the program.

3. Execute the query

Once we have successfully connected to the MySQL database, we can execute the query and read the data from the database. Through the mysqli API, we can easily build SQL query statements and execute these queries. The following is a simple example:

$sql = "SELECT id, name, email FROM users";
$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 results";
}

In the above example, we use the SQL query statement to select all the data in the users table and use mysqli_query() The function executes this query. We can use the mysqli_num_rows() function to get the number of rows in the query result, and use the while loop statement to iteratively read each row of data. In the while loop, we call the mysqli_fetch_assoc() function to get the field values ​​of each row and output these values ​​on the screen.

4. Insert data

One of the most common operations when connecting to a MySQL database is to insert new data into the database. This is accomplished by constructing a SQL INSERT statement and sending it to the database. Here is an example:

$name = "John Doe";
$email = "john@example.com";
$phone = "123-456-7890";
$sql = "INSERT INTO users (name, email, phone) VALUES ('$name', '$email', '$phone')";

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

In the above example, we first declared the data to be inserted and built the SQL INSERT statement. Then, we use the mysqli_query() function to send the SQL statement to the MySQL database and determine whether the query result is successful. If the record is successfully inserted, we print a confirmation message on the screen; otherwise we print an error message.

5. Update data

If we need to update the data of a certain record in the database, we can use the SQL UPDATE statement to achieve it. Here is an example:

$sql = "UPDATE users SET phone='555-555-5555' WHERE name='John Doe'";

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

In the above example, we use the SQL UPDATE statement to update the phone number of the record named John Doe in the users table to 555-555-5555. We again use the mysqli_query() function to execute the SQL statement and output a success or failure message on the screen.

6. Delete data

If you need to delete data from the MySQL database, you need to use the SQL DELETE statement. Here is an example:

$sql = "DELETE FROM users WHERE name='John Doe'";

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

In the above example, we used the SQL DELETE statement to delete the record named John Doe in the users table from the database. We can again use the mysqli_query() function to execute the SQL statement and output a success or failure message on the screen.

7. Conclusion

Through the above introduction, we can see that it is very simple to use PHP to connect to the MySQL database. We can use the mysqli API to easily build SQL queries and operation statements, and execute these statements to access data in the database. Although the above examples are only demonstrations of some common database operations, this article has covered connecting to the database, executing queries, inserting data, updating data, and deleting data, which is enough to get you started using PHP for database operations.

The above is the detailed content of Connect to MySQL database using PHP. For more information, please follow other related articles on the PHP Chinese website!