PHP PDO: Connect to MySQL the Right Way

What is it?

PDO (PHP Data Objects) is PHP's built-in database library. It provides a consistent API for MySQL, PostgreSQL, SQLite and 10+ other databases, with built-in support for prepared statements.

Why does it matter?

PDO's prepared statements are the single most important defence against SQL injection — the #1 web vulnerability. They also work with multiple database engines so your code is not locked to MySQL.

Learn PHP PDO — connecting to MySQL, fetching rows, and preventing SQL injection with prepared statements.

Real-World Use Cases

🔐 User authentication - Use a prepared statement to look up a user by email — never interpolate the email directly into the SQL string.
📦 Product catalogue - Fetch products with pagination using LIMIT and OFFSET placeholders for safe, dynamic queries.
🛒 Order management - Insert new orders and their items in a transaction using multiple prepared statement executions.
📊 Admin dashboard - Run aggregate queries (totals, counts, averages) safely with named placeholders for date range filters.

Connecting to MySQL

<?php

$dsn = "mysql:host=localhost;dbname=my_database;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, 'root', 'password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false
    ]);

    echo "Connected!";
} catch (PDOException $e) {
    die($e->getMessage());
}

Fetching Rows

<?php

$users = $pdo->query("SELECT id, name, email FROM users")->fetchAll();

foreach ($users as $user) {
    echo $user['name'] . " - " . $user['email'] . PHP_EOL;
}

Prepared Statements — Safe Queries

<?php

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

$stmt->execute([
    ':name' => 'Rahul',
    ':email' => 'r@ezycoders.in'
]);

echo $pdo->lastInsertId();

Q: Why use PDO instead of the old mysql_ functions?

The mysql_ functions were removed in PHP 7. PDO supports multiple databases, has a clean OOP API, and prepared statements work identically across all drivers.

Quick Access

Connecting to MySQL

Fetching Rows

Prepared Statements — Safe Queries

Related Posts

Comments (0)

Leave a Comment