How to write query statements in php database

PHP is one of the most popular server-side scripting languages ​​and a common programming language for web applications. Most web applications need to interact with databases to achieve tasks such as data storage, retrieval, and update. This article will focus on how to write PHP database query statements to help readers better master PHP database programming technology.

1. Connect to the database

To use PHP to query the database, you first need to connect to the database. PHP provides a variety of methods for connecting to databases, the most commonly used of which is to use the mysqli or PDO extension. The following are two examples of methods to connect to the database:

Use mysqli extension to connect to the database:

<?php
$server = "localhost"; // 数据库服务器名称
$username = "myuser"; // 数据库用户名
$password = "mypassword"; // 数据库密码

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

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

echo "连接成功";
?>

Use PDO extension to connect to the database:

<?php
$server = "localhost"; // 数据库服务器名称
$username = "myuser"; // 数据库用户名
$password = "mypassword"; // 数据库密码

try {
    $conn = new PDO("mysql:host=$server;dbname=mydb", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "连接成功";
} catch(PDOException $e) {
    echo "连接失败: " . $e->getMessage();
}
?>

2. Query data

After connecting to the database, you can start querying data. PHP supports a variety of query statements, including SELECT, INSERT, UPDATE, DELETE, etc. The following is an example of how to write a SELECT query statement:

<?php
$server = "localhost"; // 数据库服务器名称
$username = "myuser"; // 数据库用户名
$password = "mypassword"; // 数据库密码
$dbname = "mydb"; // 数据库名称

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

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

// 执行查询语句
$sql = "SELECT * FROM users";
$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 结果";
}

// 关闭连接
$conn->close();
?>

In the above code, we established a connection with the database, then executed a SELECT query statement, queried all the data in the users table, and output the results onto the page.

3. Filter query results

In practical applications, we usually need to filter and sort query results. PHP provides a variety of functions and statements to easily process query results. The following is an example of sorting and outputting query results:

<?php
$server = "localhost"; // 数据库服务器名称
$username = "myuser"; // 数据库用户名
$password = "mypassword"; // 数据库密码
$dbname = "mydb"; // 数据库名称

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

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

// 执行查询语句
$sql = "SELECT * FROM users ORDER BY name";
$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 结果";
}

// 关闭连接
$conn->close();
?>

In the above code, we use the ORDER BY clause to sort the query results and output the results to the page.

4. Update data

In addition to querying data, PHP also supports updating data in the database. The following is an example of updating data:

<?php
$server = "localhost"; // 数据库服务器名称
$username = "myuser"; // 数据库用户名
$password = "mypassword"; // 数据库密码
$dbname = "mydb"; // 数据库名称

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

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

// 执行更新语句
$sql = "UPDATE users SET email='newemail@example.com' WHERE id=1";

if ($conn->query($sql) === TRUE) {
    echo "记录更新成功";
} else {
    echo "更新失败: " . $conn->error;
}

// 关闭连接
$conn->close();
?>

In the above code, we executed an UPDATE query statement to update the email field of the record with id 1 in the users table to "newemail@example. com".

Through the introduction of this article, readers can understand how to write PHP database query statements, and quickly master related technologies through sample codes. I hope it will be helpful to readers in their work in web development.

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