🐘 PHP PDO (PHP Data Objects) — Detailed
Notes
1. What is PDO?
PDO (PHP Data Objects) is a database access abstraction layer in PHP.
It provides a consistent API for accessing multiple database systems
such as MySQL, PostgreSQL, SQLite, Oracle, etc.
✅ Key advantages:
Works with multiple database systems (MySQL, SQLite, PostgreSQL,
etc.)
Uses prepared statements (protection against SQL injection)
Supports transactions
Offers error handling modes
Object-oriented and flexible
2. Enabling PDO
PDO is built into PHP 5.1+, but you must have the driver for your
database installed, e.g.:
sudo apt install php-mysql # for MySQL
sudo apt install php-pgsql # for PostgreSQL
sudo apt install php-sqlite3 # for SQLite
3. PDO Connection Syntax
✅ Basic Connection
<?php
$dsn = "mysql:host=localhost;dbname=testdb;charset=utf8mb4";
$username = "root";
$password = "";
try {
$pdo = new PDO($dsn, $username, $password);
// Set error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
echo "Connected successfully!";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
Explanation:
PDO – main class for database connection.
$dsn – Data Source Name (defines the driver and database info).
PDO::ATTR_ERRMODE – sets how PDO handles errors.
4. DSN (Data Source Name) Examples
Database DSN Format
MySQL mysql:host=localhost;dbname=testdb
SQLite sqlite:/path/to/database.db
PostgreSQL pgsql:host=localhost;port=5432;dbname=testdb;user=ro
5. Executing Queries
🔹 Using query() — For simple SELECT queries
<?php
$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['name'] . "<br>";
}
?>
Common Fetch Modes:
PDO::FETCH_ASSOC – associative array
PDO::FETCH_NUM – numeric array
PDO::FETCH_OBJ – object
PDO::FETCH_BOTH – both associative & numeric
🔹 Using exec() — For INSERT, UPDATE, DELETE
<?php
$rows = $pdo->exec("UPDATE users SET status = 'active' WHERE
id < 5");
echo "$rows rows affected.";
?>
exec() returns the number of affected rows.
6. Prepared Statements (Prevent SQL Injection)
🔸 Using prepare() and execute()
<?php
$sql = "SELECT * FROM users WHERE email = :email";
$stmt = $pdo->prepare($sql);
$stmt->execute(['email' => 'user@example.com']);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($user);
?>
Named parameters ( :email ) improve readability.
🔸 Positional Parameters Example
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([1]);
$user = $stmt->fetch();
print_r($user);
🔸 Insert Example with Prepared Statement
<?php
$sql = "INSERT INTO users (name, email) VALUES (:name,
:email)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
'name' => 'John Doe',
'email' => 'john@example.com'
]);
echo "User added successfully!";
?>
7. Fetching Data
$stmt = $pdo->query("SELECT * FROM users");
// Fetch single row
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// Fetch all rows
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
8. Transactions in PDO
Used for ensuring data integrity — either all queries succeed or none.
<?php
try {
$pdo->beginTransaction();
$pdo->exec("INSERT INTO accounts (name, balance) VALUES
('Alice', 500)");
$pdo->exec("INSERT INTO accounts (name, balance) VALUES
('Bob', 300)");
$pdo->commit();
echo "Transaction successful!";
} catch (PDOException $e) {
$pdo->rollBack();
echo "Transaction failed: " . $e->getMessage();
}
?>
9. Error Handling Modes
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
Mode Description
PDO::ERRMODE_SILENT Default. Fails silently; check $stmt-
>errorInfo() manually
PDO::ERRMODE_WARNING Emits warnings
PDO::ERRMODE_EXCEPTION Throws exceptions (recommended)
10. Binding Parameters
You can bind values or variables manually:
$stmt = $pdo->prepare("INSERT INTO users (name, age) VALUES
(:name, :age)");
$name = "Alice";
$age = 25;
$stmt->bindParam(':name', $name);
$stmt->bindParam(':age', $age);
$stmt->execute();
bindParam() binds by reference, while bindValue() binds by value.
11. Getting the Last Inserted ID
$pdo->exec("INSERT INTO users (name) VALUES ('Sam')");
$lastId = $pdo->lastInsertId();
echo "Last inserted ID is: $lastId";
12. Closing the Connection
Simply set $pdo to null :
$pdo = null;
13. Example: Full CRUD with PDO
<?php
// 1. Connect
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "root",
"");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 2. Create
$pdo->exec("INSERT INTO users (name, email) VALUES ('Alice',
'alice@example.com')");
// 3. Read
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 4. Update
$stmt = $pdo->prepare("UPDATE users SET email = ? WHERE name =
?");
$stmt->execute(['alice@newmail.com', 'Alice']);
// 5. Delete
$stmt = $pdo->prepare("DELETE FROM users WHERE name = ?");
$stmt->execute(['Alice']);
?>
14. PDO vs MySQLi
Feature PDO MySQLi
Database support Multiple MySQL only
Prepared statements Yes Yes
Object-oriented Yes Yes
Named parameters Yes No
Transactions Yes Yes
API consistency High Moderate
✅ Use PDO for flexibility and cleaner code.
15. Common PDO Methods
Method Description
query() Executes a simple SQL statement
prepare() Prepares an SQL statement for execution
Method Description
execute() Executes a prepared statement
fetch() Fetches a single row
fetchAll() Fetches all rows
exec() Executes a statement (no result set)
lastInsertId() Returns last inserted ID
beginTransaction() Begins a transaction
commit() Commits a transaction
rollBack() Rolls back a transaction
✅ Summary
PDO = Secure, Flexible, Modern Database Layer
Always:
Use PDO::ERRMODE_EXCEPTION
Use prepared statements
Use transactions for multiple dependent queries
🧱 STEP 1: Create a Database
You can create the database manually in phpMyAdmin / MySQL CLI or
via PHP using PDO.
✅ Using PHP to Create Database
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
try {
// Connect without specifying a DB
$pdo = new PDO("mysql:host=$host", $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
// Create database
$sql = "CREATE DATABASE IF NOT EXISTS testdb";
$pdo->exec($sql);
echo "Database created successfully!";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
This will create a database named testdb .
🧩 STEP 2: Create a Table
Now connect to the testdb database and create a table called users .
✅ Create Table Script
<?php
$host = 'localhost';
$dbname = 'testdb';
$user = 'root';
$pass = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $user,
$pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$sql = "CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
$pdo->exec($sql);
echo "Table 'users' created successfully!";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
⚙️ STEP 3: CRUD Operations (Full Example)
We’ll now perform all CRUD operations on the users table.
🟢 CREATE — Insert Data
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=testdb",
"root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO users (name, email, age) VALUES
(:name, :email, :age)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
'name' => 'John Doe',
'email' => 'john@example.com',
'age' => 28
]);
echo "User inserted successfully!";
} catch (PDOException $e) {
echo "Insert failed: " . $e->getMessage();
}
?>
🟡 READ — Fetch Data
Fetch All Records
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=testdb",
"root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
echo "ID: {$user['id']} | Name: {$user['name']} |
Email: {$user['email']} | Age: {$user['age']}<br>";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>
Fetch Single Record
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($user);
🟠 UPDATE — Modify Data
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=testdb",
"root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$sql = "UPDATE users SET email = :email, age = :age WHERE
id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
'email' => 'john.new@example.com',
'age' => 29,
'id' => 1
]);
echo "User updated successfully!";
} catch (PDOException $e) {
echo "Update failed: " . $e->getMessage();
}
?>
🔴 DELETE — Remove Data
<?php
try {
$pdo = new PDO("mysql:host=localhost;dbname=testdb",
"root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 1]);
echo "User deleted successfully!";
} catch (PDOException $e) {
echo "Delete failed: " . $e->getMessage();
}
?>
🔍 STEP 4: Testing the CRUD Operations
1. Run the “Create Database” script once.
2. Run the “Create Table” script once.
3. Then test each CRUD script one by one (insert, read, update,
delete).
4. You can verify results in phpMyAdmin or MySQL Workbench.
📋 STEP 5: Optional – Combine All CRUD in One
File
Here’s a simplified CRUD demo combining everything in a single PHP file
for testing.
<?php
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "root",
"");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// --- CREATE ---
$pdo->prepare("INSERT INTO users (name, email, age) VALUES (?,
?, ?)")
->execute(['Alice', 'alice@example.com', 22]);
// --- READ ---
echo "<h3>All Users:</h3>";
$stmt = $pdo->query("SELECT * FROM users");
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $user) {
echo "{$user['id']}. {$user['name']} ({$user['email']}) -
Age {$user['age']}<br>";
}
// --- UPDATE ---
$pdo->prepare("UPDATE users SET age = ? WHERE name = ?")
->execute([23, 'Alice']);
// --- DELETE ---
$pdo->prepare("DELETE FROM users WHERE name = ?")
->execute(['Alice']);
?>
✅ Summary
Step Action PHP Function
1 Create Database $pdo->exec("CREATE DATABASE ...")
2 Create Table $pdo->exec("CREATE TABLE ...")
3 Insert Data $pdo->prepare() → execute()
4 Read Data $pdo->query() / fetch()
5 Update Data $pdo->prepare() → execute()
6 Delete Data $pdo->prepare() → execute()
