PHP MySQL CRUD

<?php
// Assume $pdo is already connected (see MySQL Connect page)

// INSERT with named placeholders
$sql  = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
$stmt = $pdo->prepare($sql);

$stmt->execute([
    ':name'  => 'Alice',
    ':email' => 'alice@example.com',
    ':age'   => 30,
]);

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

// INSERT with positional placeholders
$sql  = "INSERT INTO users (name, email) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['Bob', 'bob@example.com']);

// INSERT with MySQLi prepared statement
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email); // "ss" = two strings
$name  = "Carol";
$email = "carol@example.com";
$stmt->execute();
echo "Inserted ID: " . $mysqli->insert_id;
$stmt->close();
?>

<?php
// Select all rows
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(); // PDO::FETCH_ASSOC by default
foreach ($users as $user) {
    echo $user['name'] . " - " . $user['email'] . "\n";
}

// Select with WHERE (prepared statement)
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->execute([':id' => 1]);
$user = $stmt->fetch(); // single row
echo $user['name'];

// Select with multiple conditions
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > ? AND active = ?");
$stmt->execute([18, 1]);
$adults = $stmt->fetchAll();

// Count rows
$stmt = $pdo->query("SELECT COUNT(*) FROM users");
echo "Total users: " . $stmt->fetchColumn();

// MySQLi fetch
$result = $mysqli->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "\n";
}
$result->free();
?>

<?php
// UPDATE
$stmt = $pdo->prepare("UPDATE users SET name = :name, age = :age WHERE id = :id");
$stmt->execute([
    ':name' => 'Alice Updated',
    ':age'  => 31,
    ':id'   => 1,
]);
echo "Rows affected: " . $stmt->rowCount();

// DELETE
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute([':id' => 5]);
echo "Deleted: " . $stmt->rowCount() . " row(s)";

// Transaction — all or nothing
try {
    $pdo->beginTransaction();

    $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?")
        ->execute([100, 1]);

    $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?")
        ->execute([100, 2]);

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