Working with PDO

Welcome to the next lesson! In this lesson, you’ll learn how to use PDO (PHP Data Objects) for database interaction. PDO is a flexible and secure way to connect and work with databases. Let’s dive in!

What Is PDO?

PDO (PHP Data Objects) is a database access layer that provides a uniform interface for working with different database systems. It supports a variety of databases like MySQL, PostgreSQL, SQLite, and more.

Key Features:

• Database independence (works with multiple database types).
• Built-in support for prepared statements.
• Advanced error handling options.

Tip: PDO is a great choice for building secure and scalable web applications.

Connecting to a Database with PDO

To connect to a database using PDO, you need:

• The Data Source Name (DSN), including the database type, host, and name.
• A valid username and password.

Example:

Tip: Use environment variables or configuration files for sensitive credentials.

<?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();
  }
?>

Executing Queries

PDO provides two main methods for executing queries:

• exec(): For statements that don’t return results (e.g., INSERT, UPDATE).
• query(): For statements that return results (e.g., SELECT).

Example:

<?php
  // Insert a record
  $pdo->exec("INSERT INTO users (name, email) VALUES ('Alice', 'alice@mail.com')");

  // Retrieve records
  $stmt = $pdo->query("SELECT * FROM users");
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "Name: " . $row["name"] . ", Email: " . $row["email"] . "<br>";
  }
?>

Using Prepared Statements

Prepared statements are essential for secure and efficient database interaction. Example:

Tip: Use placeholders (:email) to prevent SQL injection.

<?php
  $sql = "SELECT * FROM users WHERE email = :email";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(["email" => "alice@mail.com"]);
  $user = $stmt->fetch(PDO::FETCH_ASSOC);

  echo "Name: " . $user["name"];
?>

Error Handling in PDO

PDO supports three error handling modes:

• ERRMODE_SILENT: Errors must be manually checked.
• ERRMODE_WARNING: Errors trigger PHP warnings.
• ERRMODE_EXCEPTION: Errors throw exceptions (recommended).

Example:

<?php
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  try {
    $pdo->exec("INVALID SQL STATEMENT");
  } catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
  }
?>

Fetching Data

PDO provides several fetching methods:

• fetch(): Retrieves a single row.
• fetchAll(): Retrieves all rows.
• fetchColumn(): Retrieves a single column value.

Example:

<?php
  $stmt = $pdo->query("SELECT name FROM users");
  while ($name = $stmt->fetchColumn()) {
    echo "User: " . $name . "<br>";
  }
?>

Transaction Management

Use transactions for executing multiple queries as a single unit. Example:

Tip: Use transactions to ensure data integrity.

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

    $pdo->exec("INSERT INTO users (name, email) VALUES ('Bob', 'bob@mail.com')");
    $pdo->exec("INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@mail.com')");

    $pdo->commit();
    echo "Transaction successful!";
  } catch (PDOException $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
  }
?>

Great Job!

Congratulations! You’ve learned how to use PDO for secure and efficient database interaction, including connecting, executing queries, and managing transactions. In the next lesson, we’ll explore advanced SQL querying techniques with PDO. Let’s keep coding!