PDO in PHP: A Better Way to Create, Read, and Update Data

When working with databases in PHP, two primary choices for database access are PDO (PHP Data Objects) and MySQLi (MySQL Improved). In this guide, we’ll focus on PDO, explaining how to use it to perform basic database operations—creating, reading, and updating data in PHP. We’ll also explore why PDO is often considered superior to MySQLi.

What is PDO?

PHP Data Objects (PDO) is a PHP extension that provides a consistent interface for connecting to and working with databases. It supports various database management systems like MySQL, PostgreSQL, SQLite, and more, making your code database-agnostic. This flexibility is one of the reasons why PDO is favored among PHP developers.

Advantages of Using PDO

Before we dive into using PDO for database operations, let’s understand why it’s preferred over MySQLi by many developers:

1. Database Agnosticism: As mentioned earlier, PDO supports multiple database systems. This means you can write code that works with different databases without having to rewrite your queries.
2. Object-Oriented Interface: PDO uses an object-oriented approach, making your code cleaner and more organized. Each database connection is represented as a PDO instance.
3. Security: PDO provides built-in security features like prepared statements and parameter binding, which help prevent SQL injection attacks.
4. Error Handling: PDO has better error handling capabilities. You can set PDO to throw exceptions on errors, making it easier to catch and handle issues in your code.
5. Transaction Support: PDO supports database transactions, allowing you to group multiple SQL statements into a single, atomic unit of work. This is essential for maintaining data integrity in complex applications.
6. Community and Documentation: Being widely used and recognized, PDO has extensive community support and documentation, making it easier to find solutions to problems.

Now, let’s see how to use PDO for basic database operations.

Creating a Database Connection

To use PDO, you need to establish a connection to your database. Here’s how to do it:

<?php
  $host = 'localhost';
  $dbname = 'mydatabase';
  $username = 'myusername';
  $password = 'mypassword';

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

In this example, we create a PDO object and set error handling to throw exceptions on database errors.

Creating, Reading, and Updating Data

Let’s see how to perform common CRUD (Create, Read, Update, Delete) operations using PDO.

Creating Data

To insert data into a database using PDO, you can prepare an SQL statement and then execute it:

<?php

  $sql = "INSERT INTO users (username, email) VALUES (?, ?)";
  $stmt = $pdo->prepare($sql);
  $username = "newuser";
  $email = "[email protected]";
  $stmt->execute([$username, $email]);

Reading Data

To fetch data from the database, you can use a prepared statement as well:

<?php
  
  $sql = "SELECT * FROM users";
  $stmt = $pdo->query($sql);
  while ($row = $stmt->fetch()) {
    echo "ID: " . $row['id'] . " - Username: " . $row['username'] . "<br>";
  }

Updating Data

Updating data is straightforward with PDO:

<?php
  
  $sql = "UPDATE users SET email = ? WHERE id = ?";
  $stmt = $pdo->prepare($sql);
  $newEmail = "[email protected]";
  $userId = 1;
  $stmt->execute([$newEmail, $userId]);

Conclusion

PDO in PHP is a powerful tool for working with databases, offering a high level of security, portability, and flexibility. It allows you to create, read, update, and delete data with ease, making it a top choice for developers.

Whether you’re building a small web application or a large-scale project, PDO’s versatility and robust features will serve you well in interacting with your database. It’s a valuable skill that every PHP developer should have in their toolkit.