Using PDO not only simplifies database interactions but also enhances security by preventing SQL injection attacks. In this guide, we will explore how to set up a PDO connection, execute queries, and handle errors effectively.

Setting Up PDO Connection

To begin using PDO, you first need to establish a connection to your database. Below is an example of how to connect to a MySQL database using PDO.

<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8';
$username = 'root';
$password = 'password';

try {
    $pdo = new PDO($dsn, $username, $password);
    // Set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Connection Parameters

ParameterDescription
dsnData Source Name, specifies the database type and connection details.
usernameThe username for the database.
passwordThe password for the database user.

Executing Queries with Prepared Statements

Prepared statements are a key feature of PDO, allowing you to execute SQL queries safely. They help prevent SQL injection by separating SQL logic from data. Here’s how to use prepared statements to insert data into a database.

Inserting Data

<?php
$sql = "INSERT INTO users (username, email) VALUES (:username, :email)";
$stmt = $pdo->prepare($sql);

$username = 'john_doe';
$email = '[email protected]';

$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);

if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Error: Could not execute the query.";
}
?>

Binding Parameters

You can bind parameters using:

  • bindParam(): Binds a variable to a parameter.
  • bindValue(): Binds a value to a parameter.

Selecting Data

Retrieving data is equally straightforward. Here’s an example of selecting users from the database.

<?php
$sql = "SELECT * FROM users WHERE email = :email";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':email', '[email protected]');
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
    echo $row['username'] . " - " . $row['email'] . "<br>";
}
?>

Error Handling

PDO provides a robust error handling mechanism. By setting the error mode to PDO::ERRMODE_EXCEPTION, you can catch exceptions and handle errors gracefully.

Example of Error Handling

<?php
try {
    $stmt = $pdo->prepare("SELECT * FROM non_existing_table");
    $stmt->execute();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Transaction Management

Transactions are essential for maintaining data integrity, especially when performing multiple related operations. PDO allows you to manage transactions easily.

Using Transactions

<?php
try {
    $pdo->beginTransaction();

    $stmt1 = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
    $stmt1->bindParam(':username', $username);
    $stmt1->bindParam(':email', $email);
    $username = 'jane_doe';
    $email = '[email protected]';
    $stmt1->execute();

    $stmt2 = $pdo->prepare("INSERT INTO profiles (user_id, bio) VALUES (:user_id, :bio)");
    $stmt2->bindParam(':user_id', $pdo->lastInsertId());
    $bio = 'Hello, I am Jane!';
    $stmt2->bindParam(':bio', $bio);
    $stmt2->execute();

    $pdo->commit();
    echo "Transaction completed successfully";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}
?>

Commit and Rollback

  • Commit: Finalizes the transaction.
  • Rollback: Reverts all changes made during the transaction if an error occurs.

Conclusion

Using PDO for database interactions in PHP not only enhances security through prepared statements but also simplifies error handling and transaction management. By following the best practices outlined in this guide, you can ensure that your PHP applications are both secure and efficient.

Learn more with useful resources: