Connecting PHP with MySQL using PDO – Secure Database Access

What is PDO in PHP?

PDO (PHP Data Objects) is a database access layer providing a uniform method of access to multiple databases (like MySQL, SQLite, PostgreSQL, etc.) using the same set of functions.

Why Use PDO?

• Supports multiple database systems
• Prevents SQL injection using prepared statements
• Cleaner, object-oriented syntax
• Easier to handle errors and exceptions

Setting Up MySQL Database (Using phpMyAdmin or CLI)

Before connecting PHP to MySQL:

1. Start Apache and MySQL using XAMPP/WAMP
2. Visit http://localhost/phpmyadmin
3. Create a database named testdb
4. Run the following SQL:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

Basic PDO Connection in PHP

Syntax:

$pdo = new PDO("mysql:host=localhost;dbname=testdb", "root", "");

$pdo = new PDO("mysql:host=localhost;dbname=testdb", "root", "");

• host: Usually localhost
• dbname: The database name
• root: Default MySQL username (on XAMPP)
• “”: Empty password (default)

Full Connection Example:

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

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

Always use try...catch to gracefully handle connection failures.

Inserting Data with PDO (Prepared Statements)

Example:

<?php
$name = "Jay";
$email = "jay@example.com";

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

echo "User added successfully!";
?>

<?php
$name = "Jay";
$email = "jay@example.com";

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

echo "User added successfully!";
?>

• :name, :email are named placeholders
• bindParam() binds actual values
• execute() runs the statement securely

Fetching Data from MySQL

Fetch All Users:

<?php
$stmt = $pdo->query("SELECT * FROM users");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo $row['name'] . " - " . $row['email'] . "<br>";
}
?>

<?php
$stmt = $pdo->query("SELECT * FROM users");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo $row['name'] . " - " . $row['email'] . "<br>";
}
?>

• PDO::FETCH_ASSOC returns an associative array
• You can also use fetchAll() if needed:

$rows = $stmt->fetchAll();

$rows = $stmt->fetchAll();

Fetch with Prepared Statement

Example (search by email):

<?php
$email = "jay@example.com";

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);

$user = $stmt->fetch();

if ($user) {
  echo "User found: " . $user['name'];
} else {
  echo "No user found.";
}
?>

<?php
$email = "jay@example.com";

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);

$user = $stmt->fetch();

if ($user) {
  echo "User found: " . $user['name'];
} else {
  echo "No user found.";
}
?>

Updating Records with PDO

Example:

<?php
$newName = "Jay Updated";
$id = 1;

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

echo "Record updated!";
?>

<?php
$newName = "Jay Updated";
$id = 1;

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

echo "Record updated!";
?>

Deleting Records Securely

Example:

<?php
$idToDelete = 2;

$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute(['id' => $idToDelete]);

echo "Record deleted.";
?>

<?php
$idToDelete = 2;

$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute(['id' => $idToDelete]);

echo "Record deleted.";
?>

Best Practices for PDO

• Use prepared statements to prevent SQL injection
• Set error mode to PDO::ERRMODE_EXCEPTION for easier debugging
• Close connections automatically by letting the $pdo variable go out of scope
• Reuse prepare() statements when executing the same query multiple times
• Use try...catch blocks around DB code to catch issues

⚠️ Common Mistakes to Avoid

• Using raw variables inside SQL (e.g., "SELECT * FROM users WHERE email = '$email'")
• Not using setAttribute() for error reporting
• Confusing bindParam() with bindValue() (bindParam requires a variable reference)
• Forgetting to call execute()

Notes:

• PDO is a modern, secure, and flexible way to interact with MySQL using PHP
• Use prepared statements to safely insert, update, and fetch data
• Handle connection errors gracefully using try/catch
• Use placeholders to separate query logic from user input

Practice Tasks

Task 1: Database Connection Test
Write a PHP script that connects to testdb and displays “Connection Successful” or an error.

Task 2: Insert New User
Create a form that takes name and email, then inserts them into the database using PDO.

Task 3: List All Users
Write a PHP page that retrieves and displays all rows from the users table in an HTML table.