Discuss the query statements of mysql database in php

PHP is a popular programming language that is widely used in web development. In web development, the use of databases is crucial. MySQL is one of the most popular relational databases currently, and PHP is one of the best choices for connecting to the MySQL database. In this article, we will take an in-depth look at how to write queries for MySQL databases using PHP.

MySQL query statements are often called "SQL statements", which allow developers to operate on data in the MySQL database. Using a MySQL database in PHP requires connecting to the MySQL server and executing SQL statements. Below, we will introduce how to connect to the MySQL database through PHP and use different query statements.

Step 1: Connect to the MySQL database

In PHP, we can use mysqli or PDO to connect to the MySQL database. Both of these are extension libraries provided in PHP that can easily interact with MySQL. The following is a sample code for using mysqli to connect to a MySQL database:

//连接MySQL服务器
$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');

//检查是否连接成功
if ($mysqli->connect_error) {
    die('连接失败: ' . $mysqli->connect_error);
}

In the above code, we create a mysqli object and use localhost as the MySQL server address, username and password are the username and password required to connect to the MySQL server. database_name is the name of the database we will connect to. You can check whether the connection is successful by calling the connect_error method.

Step 2: Execute the SQL query statement

After connecting to the MySQL server, we can execute the SQL query statement. SQL statements can be divided into four types: SELECT query, INSERT insertion, UPDATE update and DELETE deletion. The following are some of the most commonly used SQL query statements and their usage:

1. SELECT query

SELECT query is used to retrieve data from the database. Here is a sample SELECT query:

$sql = "SELECT * FROM users";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}

This query will retrieve all rows in the users table. If the result set is not empty, the fetch_assoc method will be used to output the data for each row.

2. INSERT insert

The INSERT statement is used to insert new data into the database. Here is a sample INSERT statement:

$sql = "INSERT INTO users (name, email, password) VALUES ('John Doe', 'johndoe@email.com', 'mypassword')";
if ($mysqli->query($sql) === TRUE) {
    echo "数据插入成功！";
} else {
    echo "数据插入失败： " . $mysqli->error;
}

The above INSERT statement inserts the new user's name, email, and password into the users table.

3. UPDATE update

The UPDATE statement is used to update existing data in the database. The following is an example UPDATE statement:

$sql = "UPDATE users SET email='newemail@email.com' WHERE id=1";
if ($mysqli->query($sql) === TRUE) {
    echo "数据更新成功！";
} else {
    echo "数据更新失败： " . $mysqli->error;
}

This UPDATE statement updates the email of the user with id in the users table to newemail@email. com.

4. DELETE Delete

The DELETE statement is used to delete data from the database. The following is a sample DELETE statement:

$sql = "DELETE FROM users WHERE id=1";
if ($mysqli->query($sql) === TRUE) {
    echo "数据删除成功！";
} else {
    echo "数据删除失败： " . $mysqli->error;
}

The above DELETE statement will delete the user data with id being 1 from the users table.

Step 3: Close the database connection

In PHP, after connecting to the MySQL database, you must use the close method to close the database connection. Here is an example:

$mysqli->close();

Summary

In this article, we learned how to connect to a MySQL database using PHP and perform queries, insert, update or delete data using different SQL statements. These SQL statements can be combined as needed to achieve more complex data operations. Through these examples, you can better understand and use the query statements of the MySQL database in PHP.

The above is the detailed content of Discuss the query statements of mysql database in php. For more information, please follow other related articles on the PHP Chinese website!