PHP PDO (PHP Data Objects) is a database access layer that provides a uniform method of access to multiple databases. It allows developers to use a consistent interface for different database systems (e.g., MySQL, PostgreSQL, SQLite). PDO supports prepared statements, which help prevent SQL injection attacks and enhance the security of database interactions.

Key Features of PDO:

• Database Agnostic: PDO provides a consistent API for different database systems.
• Prepared Statements: Secure way to execute SQL queries.
• Transaction Management: Support for transactions.
• Error Handling: Flexible error handling with exceptions.

Basic Example of Using PDO

Let’s go through a simple example using PDO to connect to a MySQL database, insert a record, fetch data, and use prepared statements.

1. Connecting to a Database:

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

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

$dsn: Data Source Name, contains the information required to connect to the database.

$username and $password: The database credentials.

PDO::ATTR_ERRMODE: Sets the error handling mode to exceptions.

2. Inserting Data Using Prepared Statements

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

    // Bind parameters
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':email', $email);

    // Insert a row
    $name = "John Doe";
    $email = "john.doe@example.com";
    $stmt->execute();

    echo "Record inserted successfully!";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

prepare(): Prepares the SQL query for execution.bindParam():

Binds a parameter to a specific variable.execute():

Executes the prepared statement.

3. Fetching Data

<?php
try {
    $sql = "SELECT * FROM users WHERE email = :email";
    $stmt = $pdo->prepare($sql);

    // Bind the email parameter
    $stmt->bindParam(':email', $email);

    // Set the email to search for
    $email = "john.doe@example.com";
    $stmt->execute();

    // Fetch the result
    $user = $stmt->fetch(PDO::FETCH_ASSOC);

    echo "Name: " . $user['name'] . ", Email: " . $user['email'];
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

fetch(PDO::FETCH_ASSOC): Fetches the result as an associative array.

4. Using Transactions

Transactions are useful when you need to execute a series of queries that should either all succeed or all fail.

<?php
try {
    // Begin a transaction
    $pdo->beginTransaction();

    // First query
    $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");

    // Second query
    $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

    // Commit the transaction
    $pdo->commit();

    echo "Transaction successful!";
} catch (PDOException $e) {
    // Rollback the transaction if something failed
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}
?>

• beginTransaction(): Starts a transaction.
• commit(): Commits the transaction.
• rollBack(): Rolls back the transaction in case of an error.

Summary

PDO is a powerful and flexible way to interact with databases in PHP. It offers a consistent interface for different databases and includes features like prepared statements and transactions, which improve security and reliability. By using PDO, you can write more secure and maintainable code, regardless of the database system you are using.

4o