PHP and PDO: How to execute complex SQL queries

PHP and PDO: How to execute complex SQL query statements

When dealing with database operations, PHP provides a powerful extension library PDO (PHP Data Objects) to simplify interaction with the database. PDO supports a variety of databases, such as MySQL, SQLite, etc., and also provides a wealth of functions and methods to facilitate developers to perform various database operations. This article will introduce how to use PDO to execute complex SQL query statements, and attach corresponding code examples.

1. Connecting to the database

Before using PDO, you first need to establish a connection with the database. The following code example shows how to connect to a MySQL database:

$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'root';
$password = '';

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

In the above code, the connection string (DSN) is first defined, including the database type (mysql), host name (localhost) and database name (testdb ). Next is the database username (root) and password (empty). Then use the constructor of the PDO class to establish a connection with the database, and set the error handling mode to exception mode. Finally, exception handling is used to catch possible connection errors.

2. Execute complex SQL query statements

Next, we will introduce how to use PDO to execute complex SQL query statements, such as conditions, sorting, paging, etc. . Suppose we have a data table named "users" that contains fields such as the user's name, age, and gender.

2.1 Query all records

To query all records in the data table, you can use the query() method in PDO. The following code example shows how to query all user records in the data table:

$stmt = $conn->query('SELECT * FROM users');
while ($row = $stmt->fetch()) {
    echo $row['name'] . ' ' . $row['age'] . ' ' . $row['gender'] . '<br>';
}

In the above code, first use the query() method to execute the SQL query statement and return the result set (PDOStatement object). Then use the fetch() method to obtain the data in the result set by row, and print out the records of each row.

2.2 Query with conditions

If you want to query records that meet specific conditions, you can use conditional statements (such as WHERE clause) in SQL statements. The following code example shows how to query user records older than 18 years old:

$stmt = $conn->query('SELECT * FROM users WHERE age > 18');
while ($row = $stmt->fetch()) {
    echo $row['name'] . ' ' . $row['age'] . ' ' . $row['gender'] . '<br>';
}

In the above code, a WHERE conditional statement is added to filter records that meet the conditions.

2.3 Sorting and paging query

In practical applications, it is often necessary to sort and display query results in paging. The following code example shows how to sort in ascending order by the name field and perform a paging query to display 10 records per page:

$page = $_GET['page'] ?? 1;
$limit = 10;
$offset = ($page - 1) * $limit;

$stmt = $conn->query('SELECT * FROM users ORDER BY name ASC LIMIT ' . $limit . ' OFFSET ' . $offset);
while ($row = $stmt->fetch()) {
    echo $row['name'] . ' ' . $row['age'] . ' ' . $row['gender'] . '<br>';
}

In the above code, first get the current page number from the URL parameter (using ? ? operator sets the default value to 1). Then define the number of records displayed on each page (10) and the offset (the offset is calculated based on the current page number). Finally, add the ORDER BY clause to the SQL query statement for sorting, and use the LIMIT and OFFSET keywords for paging queries.

The above is an introduction and sample code for using PDO to execute complex SQL query statements. Using PDO can simplify database operations and provide powerful functions to facilitate developers to perform various complex query operations. By mastering the use of PDO, you can handle database-related tasks more efficiently.

The above is the detailed content of PHP and PDO: How to execute complex SQL queries. For more information, please follow other related articles on the PHP Chinese website!