How to Implement PHP PDO CRUD to Manipulate MySQL Database Table Records

In the field of web development, managing database records efficiently is very important. PHP is a versatile server-side scripting language that offers various methods to interact with databases. One powerful tool in the PHP developer's arsenal is PDO (PHP Data Objects), which provides a consistent interface for accessing databases, including MySQL. In this article, I will walk you through into implementing CRUD operations (Create, Read, Update, Delete) using PDO to manipulate MySQL database table records effectively like a pro.

Introduction to PDO

PDO is a database access layer providing a uniform method of access to multiple databases. It supports various database drivers, including MySQL, PostgreSQL, SQLite, and more. PDO offers several advantages over traditional database access methods like MySQLi, such as prepared statements, which help prevent SQL injection attacks and enhance security.

Setting Up the Environment

Before you get started with PDO CRUD operations, ensure you have the following prerequisites:

1. A web server (like Apache) installed on your local machine (I recommend XAMPP) or a hosting server (I recommend Direct Admin or cPanel).
2. PHP installed on your server or local environment.
3. MySQL database server installed and running.

Creating a MySQL Database and Table

First, let us create a MySQL database and a table to perform CRUD operations. Execute the following SQL commands in your MySQL client or phpMyAdmin:

CREATE DATABASE IF NOT EXISTS `pdo_crud`;
USE `pdo_crud`;

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) NOT NULL
);

This SQL script creates a database named 'pdo_crud' and a table named 'users' with columns for id, name, and email.

Connecting to the Database Using PDO

Next, let us establish a connection to the MySQL database using PDO in PHP. Create a new PHP file (e.g., db_connection.php) and add the following code:

$host = 'localhost';
$dbname = 'pdo_crud';
$username = 'your_username';
$password = 'your_password';

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

Replace 'your_username' and 'your_password' with your MySQL username and password, respectively. This script establishes a connection to the 'pdo_crud' database.

Implementing CRUD Operations

Now, let us implement CRUD operations using PDO:

1. Create Operation

include 'db_connection.php';

$name = 'John Doe';
$email = 'john@example.com';

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);

$stmt->execute();

echo "Record inserted successfully";

2. Read Operation

include 'db_connection.php';

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

3. Update Operation

include 'db_connection.php';

$id = 1;
$newName = 'Jane Doe';

$stmt = $pdo->prepare("UPDATE users SET name = :name WHERE id = :id");
$stmt->bindParam(':name', $newName);
$stmt->bindParam(':id', $id);

$stmt->execute();

echo "Record updated successfully";

4. Delete Operation

include 'db_connection.php';

$id = 1;

$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->bindParam(':id', $id);

$stmt->execute();

echo "Record deleted successfully";

In this article, we explored how to implement CRUD operations using PHP PDO to manipulate MySQL database table records. PDO offers a secure, efficient, and consistent way to interact with databases, making it a preferred choice for database operations in PHP applications. By following these steps I have provided, you can enhance your web development skills and build robust database-driven applications with PHP and MySQL.

You may like:

Introduction to PHP and MySQL

Beyond the Browser: An Architectural Deep-Dive into NativePHP

How to create a functional contact form in PHP