How to use PDO for database query operations in PHP

PHP PDO is an interface that can connect to multiple different databases. In PHP applications, it can connect PHP code with database systems such as MySQL, PostgreSQL, Oracle, etc. In this article, we will introduce how to use PDO for query operations.

1. Connecting to the database

Before using PDO to query the database, you first need to establish a connection. The following is a sample code for connecting to a MySQL database:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

This code will connect to the local MySQL database and prompt information about the success or failure of the connection. Through this example, we can see that PDO handles connection errors through exception handling.

2. Querying a single row of data

Next, we will introduce how to use PDO to query a single row of data. The following is a sample code for querying a single row of data:

<?php
$stmt = $conn->prepare("SELECT * FROM customers WHERE id = :id");
$stmt->bindParam(':id', $id);

$id = 1;
$stmt->execute();

$result = $stmt->fetch(PDO::FETCH_ASSOC);

echo "id: " . $result['id'] . "<br>";
echo "name: " . $result['name'] . "<br>";
echo "email: " . $result['email'] . "<br>";
?>

This code will query a row of data in the database and output the results. First, we use the PDO::prepare() method to prepare the query statement, then use the bindParam() method to bind the query parameters to the placeholders, then use the execute() method to execute the query, and finally use the fetch() method to fetch Return query results.

3. Querying multi-row data

PDO also supports querying multi-row data. The following is a sample code for querying multiple rows of data:

<?php
$stmt = $conn->prepare("SELECT * FROM customers");
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($result as $row) {
    echo "id: " . $row['id'] . "<br>";
    echo "name: " . $row['name'] . "<br>";
    echo "email: " . $row['email'] . "<br>";
    echo "<hr>";
}
?>

This code uses the fetchAll() method to query multiple rows of data in the database and outputs each row of data in a loop. The fetchAll() method returns a two-dimensional array, and we can use foreach to traverse each row of data.

4. Query data and display it in pages

In web applications, we usually need to page the query results. The following is a sample code to query data and display it in pages:

<?php
$records_per_page = 10;
$page = isset($_GET[&#39;page&#39;]) ? $_GET[&#39;page&#39;] : 1;

$stmt = $conn->prepare("SELECT * FROM customers LIMIT :offset, :records_per_page");
$stmt->bindParam(':offset', ($page - 1) * $records_per_page, PDO::PARAM_INT);
$stmt->bindParam(':records_per_page', $records_per_page, PDO::PARAM_INT);

$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($result as $row) {
    echo "id: " . $row['id'] . "<br>";
    echo "name: " . $row['name'] . "<br>";
    echo "email: " . $row['email'] . "<br>";
    echo "<hr>";
}

$stmt = $conn->prepare("SELECT COUNT(*) FROM customers");
$stmt->execute();

$total_records = $stmt->fetchColumn();
$total_pages = ceil($total_records / $records_per_page);

echo "<div>";
for($i = 1; $i <= $total_pages; $i++) {
    echo "<a href=&#39;?page=$i&#39;>$i</a> ";
}
echo "</div>";
?>

The code first defines the record number and page number variables, and then adds them to the query statement. In the loop, we output each row of data. Next, we query the total number of records and calculate the total number of pages. Finally, we output the paginated link.

Conclusion

The above are some examples of using PDO to query the database with PHP. These codes can help you use PDO query statements correctly in your PHP applications.

The above is the detailed content of How to use PDO for database query operations in PHP. For more information, please follow other related articles on the PHP Chinese website!