How to connect to MySQL database in php and query it

PHP is a popular open source programming language used for developing web applications and websites. MySQL is a widely used relational database management system that can be used to store, manage and query data. When developing web applications using PHP, you often need to connect to a MySQL database to perform various query operations.

This article will introduce how to use PHP to connect to the MySQL database and perform common query operations.

1. Connect to MySQL database

In PHP, we can use MySQLi functions or PDO (PHP Data Object) to connect to the database. The following is a sample code for using MySQLi functions to connect to a MySQL database:

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

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

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

In the above code, we first define variables such as database server name, MySQL user name, MySQL password, and database name. Then, use the mysqli() function to create a connection object $conn. If the connection fails, an error message is printed and the script is terminated.

2. Perform query operations

After connecting to the MySQL database, we can perform various query operations, such as querying tables, inserting data, updating data, deleting data, etc. The following are some common query operation examples:

1. Query data table

$sql = "SELECT * FROM myTable";

$result = $conn->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 结果";
}

In the above code, we use the SELECT statement to query the data table## All columns of #myTable. $result->fetch_assoc() Returns one row of data at a time, so you can use a while loop to output each row of data.

Insert data

2. $sql = "INSERT INTO myTable (name, email) VALUES ('John Doe', 'john@example.com')";
   
   if ($conn->query($sql) === TRUE) {
       echo "新记录插入成功";
   } else {
       echo "Error: " . $sql . "<br>" . $conn->error;
   }

The above code will insert a new record in the

myTable table. If the insertion is successful, the message "New record inserted successfully" will be output, otherwise an error message will be output.

Update data

3. $sql = "UPDATE myTable SET email='john.doe@example.com' WHERE id=1";
   
   if ($conn->query($sql) === TRUE) {
       echo "记录更新成功";
   } else {
       echo "Error: " . $sql . "<br>" . $conn->error;
   }

The above code will update the

email of the record with id being 1 in the myTable table List. If the update is successful, the message "Record update successful" will be output, otherwise an error message will be output.

Delete data

4. $sql = "DELETE FROM myTable WHERE id=1";
   
   if ($conn->query($sql) === TRUE) {
       echo "记录删除成功";
   } else {
       echo "Error: " . $sql . "<br>" . $conn->error;
   }

The above code will delete the record with

id being 1 in the myTable table. If the deletion is successful, the message "Record deleted successfully" will be output, otherwise an error message will be output.

3. Close the database connection

After executing the query operation, the connection to the MySQL database must be closed. The following is a sample code to close the connection:

$conn->close();

The above code will close the connection object

$conn.

Conclusion

Connecting and operating a MySQL database in PHP is very simple. Through the above sample code, you can learn how to connect to the MySQL database in PHP and perform common query operations. If you want to learn more about PHP and MySQL, we recommend reading the related documentation and tutorials. Good luck with your web development with PHP and MySQL!

The above is the detailed content of How to connect to MySQL database in php and query it. For more information, please follow other related articles on the PHP Chinese website!