MySQL & PDO

PDO (PHP Data Objects) is PHP's standard way to talk to a database — it works the same way across MySQL, PostgreSQL, SQLite, and others, and its prepared statements are the single most important habit for writing PHP that talks to a database safely.

Connecting with PDO

PHP connect.php

<?php
$dsn = "mysql:host=localhost;dbname=shop;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, "app_user", "secret_password");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Output

Connected successfully

The DSN (data source name) string tells PDO what kind of database it is, where to find it, and which database to use. Setting ATTR_ERRMODE to ERRMODE_EXCEPTION means a failed query throws a catchable PDOException instead of failing silently — almost always what you want.

Prepared statements: the right way to query

Never build a SQL query by concatenating a variable straight into the string — that's how SQL injection happens. A prepared statement separates the query's structure from its data, sending them to the database separately:

PHP lookup.php

<?php
$username = $_GET["username"] ?? "";

$stmt = $pdo->prepare("SELECT id, email FROM users WHERE username = ?");
$stmt->execute([$username]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

print_r($user);
?>

Output, for username "jamie99"

Array
(
    [id] => 7
    [email] => jamie99@example.com
)

The ? in the SQL string is a placeholder — $username is bound into it separately by execute(), never spliced directly into the query text. This makes it structurally impossible for user input to be interpreted as SQL, which is exactly what an attacker relies on when they submit something like ' OR '1'='1 as a "username."

Security note: the vulnerable version of this code would look like "SELECT * FROM users WHERE username = '$username'" — building the query with string interpolation. If $username came in as anything' OR '1'='1, the query's actual meaning changes completely and could return every row in the table, or worse. Prepared statements aren't an optional best practice here — treat raw string interpolation into SQL as a bug every time you see it.

Named placeholders and inserting data

PHP insert.php

<?php
$stmt = $pdo->prepare(
    "INSERT INTO users (username, email) VALUES (:username, :email)"
);
$stmt->execute([
    "username" => "newuser",
    "email" => "newuser@example.com"
]);

echo "Inserted row ID: " . $pdo->lastInsertId();
?>

Output

Inserted row ID: 42

Named placeholders (:username, :email) work the same way as the plain ? form but read more clearly once a query has several parameters — matched by name rather than by position. lastInsertId() hands back the auto-generated ID of the row that was just created.

Fetching multiple rows

PHP list-users.php

<?php
$stmt = $pdo->prepare("SELECT username FROM users WHERE active = ?");
$stmt->execute([1]);

foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    echo $row["username"] . "\n";
}
?>

Output

jamie99
priya_codes
sam_writes

fetch() from the earlier example returns one row; fetchAll() returns every matching row as an array of associative arrays, ready for a foreach — the same pattern from the Associative Arrays and Loops lessons, now driven by real data from the database.

PreviousForms & Superglobals NextSessions & Cookies