How to use SQL statements in PHP

As a popular programming language, PHP provides many tools and methods for database operations, the most commonly used of which is SQL (Structured Query Language) statements. SQL is a language used to access and manage relational databases (RDBMS), which allows us to store and retrieve data by manipulating tables, rows, and columns. The following will introduce how to use SQL statements in PHP.

1. Connect to the database

Before using SQL statements, we need to connect to the database first. This can be achieved by using PHP's built-in mysqli (MySQL Improved Extension) or PDO (PHP Data Objects):

1.1 mysqli connection method:

$servername = "localhost"; //数据库主机名
$username = "username"; //数据库用户名
$password = "password"; //数据库密码
$dbname = "myDB"; //连接的数据库

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

// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
} 

1.2 PDO connection method:

$servername = "localhost"; //数据库主机名
$username = "username"; //数据库用户名
$password = "password"; //数据库密码
$dbname = "myDB"; //连接的数据库

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // 设置 PDO 错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "连接成功"; 
}
catch(PDOException $e) {
    echo "连接失败: " . $e->getMessage();
}

2. Querying data

There are many SQL statements used to query data in tables. The following are some common SQL statements:

• SELECT: used to select data from a table.
• WHERE: used to specify conditions and records that meet the conditions.
• ORDER BY: used to sort query results according to a specific condition.
• GROUP BY: Usually used with aggregate functions (SUM, AVG, COUNT, etc.) to calculate summary data by grouping query results.

The following is an example of using mysqli and PDO to query data:

2.1 Using mysqli to query data:

$sql = "SELECT id, name, email 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 结果";
}

2.2 Using PDO to query data:

$sql = "SELECT id, name, email FROM users";
$stmt = $conn->prepare($sql); 
$stmt->execute();

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 
foreach($stmt->fetchAll() as $row) {
    echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "<br>";
}

3. Insert data

When inserting new data into the table, we use the INSERT INTO statement. The following is the basic format for inserting new data:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

The following is an example of inserting data using mysqli and PDO:

3.1 Insert data using mysqli:

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

if ($conn->query($sql) === TRUE) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

3.2 Insert using PDO Data:

$sql = "INSERT INTO users (name, email, password)
VALUES ('John Doe', 'john@example.com', '123456')";
$conn->exec($sql);
echo "新记录插入成功";

4. Update data

When updating data, we use the UPDATE statement. The following is the basic format of the UPDATE statement:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

The following is an example of using mysqli and PDO to update data:

4.1 Using mysqli to update data:

$sql = "UPDATE users SET email='john_doe@example.com' WHERE id=1";

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

4.2 Using PDO to update data :

$sql = "UPDATE users SET email='john_doe@example.com' WHERE id=1";
$conn->exec($sql);
echo "记录更新成功";

5. Delete data

When deleting data, we use the DELETE statement. The following is the basic format of the DELETE statement:

DELETE FROM table_name WHERE condition;

The following is an example of deleting data using mysqli and PDO:

5.1 Deleting data using mysqli:

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

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

5.2 Deleting data using PDO :

$sql = "DELETE FROM users WHERE id=1";
$conn->exec($sql);
echo "记录删除成功";

Summary: When using PHP and SQL statements, you must be careful to prevent SQL injection. You can use mysqli's prepared statements or PDO's prepared statements to avoid SQL injection attacks. However, using mysqli or PDO to operate the database can ensure database security. When writing MySQL applications, PDO or mysqli should be used as much as possible to reduce the burden between the program and the database.

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