PHP — MySQL Database

PDO connection

<?php
try {
    $pdo = new PDO(
        'mysql:host=localhost;dbname=mydb;charset=utf8mb4',
        'username',
        'password',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

Querying

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

foreach ($users as $user) {
    echo $user['name'] . "\n";
}

// Single row
$stmt = $pdo->query("SELECT * FROM users WHERE id = 1");
$user = $stmt->fetch();
?>

Prepared statements

<?php
// Insert
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(['Alice', 'alice@example.com']);

// Named parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");
$stmt->execute(['name' => 'Alice']);
$user = $stmt->fetch();

// Update
$stmt = $pdo->prepare("UPDATE users SET email = :email WHERE id = :id");
$stmt->execute(['email' => 'new@example.com', 'id' => 1]);
?>

Transactions

<?php
try {
    $pdo->beginTransaction();

    $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}
?>

Error handling

<?php
try {
    $stmt = $pdo->prepare("SELECT * FROM nonexistent");
    $stmt->execute();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Mini Practice

Write PHP code that:

1. Connects to a database with PDO
2. Uses prepared statements for INSERT
3. Executes a transaction
4. Handles database errors

Up Next

Congratulations! You've completed the PHP fundamentals. Continue exploring advanced topics like Composer, MVC, and frameworks.