PHP: عمليات قاعدة البيانات PDO

<?php
$host = 'localhost';
$dbname = 'myblog';
$username = 'root';
$password = '';
$charset = 'utf8mb4';

$dsn = "mysql:host={$host};dbname={$dbname};charset={$charset}";

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    echo "✅ Database connected successfully!<br>";
} catch (PDOException $e) {
    die("❌ Connection failed: " . $e->getMessage());
}
?>

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

// Iterate results
foreach ($users as $user) {
    echo "ID: {$user['id']}, Username: {$user['username']}<br>";
}

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

echo $user['username'];  // John
?>

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

// Associative array (default)
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['username'];

// Numeric index
$row = $stmt->fetch(PDO::FETCH_NUM);
echo $row[1];

// Object
$row = $stmt->fetch(PDO::FETCH_OBJ);
echo $row->username;

// Fetch a single column
$usernames = $stmt->fetchAll(PDO::FETCH_COLUMN, 1);
print_r($usernames);  // ['John', 'Jane', ...]
?>

<?php
// ❌ Dangerous: direct SQL concatenation (SQL injection vulnerability)
// $stmt = $pdo->query("SELECT * FROM users WHERE username = '$input'");

// ✅ Safe: prepared statement
$username = $_GET['username'] ?? '';

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

if ($user) {
    echo "Found user: {$user['username']}";
} else {
    echo "User not found";
}
?>

<?php
$sql = "INSERT INTO users (username, email, password, age) 
        VALUES (:username, :email, :password, :age)";

$stmt = $pdo->prepare($sql);
$stmt->execute([
    'username' => 'Alex',
    'email'    => 'alex@example.com',
    'password' => password_hash('123456', PASSWORD_DEFAULT),
    'age'      => 24,
]);

$newId = $pdo->lastInsertId();
echo "New user ID: {$newId}";
?>

<?php
$sql = "UPDATE users SET age = :age WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['age' => 27, 'id' => 1]);

echo "Affected {$stmt->rowCount()} row(s)";
?>

<?php
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 5]);

echo "Deleted {$stmt->rowCount()} row(s)";
?>

<?php
// Database connection (same as above, omitted for brevity)
$pdo = new PDO($dsn, $username, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// Handle form submissions
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $action = $_POST['action'] ?? '';
    
    if ($action === 'add') {
        $stmt = $pdo->prepare(
            "INSERT INTO users (username, email, password, age) 
             VALUES (:username, :email, :password, :age)"
        );
        $stmt->execute([
            'username' => $_POST['username'],
            'email'    => $_POST['email'],
            'password' => password_hash($_POST['password'], PASSWORD_DEFAULT),
            'age'      => (int)$_POST['age'],
        ]);
        echo "<p style='color:green'>User added successfully!</p>";
    } elseif ($action === 'delete') {
        $stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
        $stmt->execute(['id' => (int)$_POST['id']]);
        echo "<p style='color:green'>User deleted!</p>";
    }
}

// Query all users
$users = $pdo->query("SELECT * FROM users ORDER BY id DESC")->fetchAll();
?>

<h2>User Management</h2>

<table border="1" cellpadding="8">
    <tr><th>ID</th><th>Username</th><th>Email</th><th>Age</th><th>Registered</th><th>Actions</th></tr>
    <?php foreach ($users as $u): ?>
    <tr>
        <td><?= $u['id'] ?></td>
        <td><?= htmlspecialchars($u['username']) ?></td>
        <td><?= htmlspecialchars($u['email']) ?></td>
        <td><?= $u['age'] ?></td>
        <td><?= $u['created_at'] ?></td>
        <td>
            <form method="POST" style="display:inline">
                <input type="hidden" name="action" value="delete">
                <input type="hidden" name="id" value="<?= $u['id'] ?>">
                <button type="submit" onclick="return confirm('Confirm deletion?')">Delete</button>
            </form>
        </td>
    </tr>
    <?php endforeach; ?>
</table>

<h3>Add User</h3>
<form method="POST">
    <input type="hidden" name="action" value="add">
    <input type="text" name="username" placeholder="Username" required>
    <input type="email" name="email" placeholder="Email" required>
    <input type="password" name="password" placeholder="Password" required>
    <input type="number" name="age" placeholder="Age">
    <button type="submit">Add</button>
</form>