Week 13: PHP + MySQL CRUD
Full Stack | PDO, Prepared Statements, CRUD, Auth, Pagination
- PHP و MySQL ولوحة الإدارة — شرح عملي — الاتصال بـ PDO في db.php، الاستعلامات المُجهّزة ضد SQL Injection، التسجيل في MySQL مع رفع الصورة، لوحة الإدارة بالإضافة والتعديل والحذف، وحماية CSRF · 11 دقيقة
Al-Aqsa University
PHP + MySQL CRUD & Auth
PDO connection, CRUD operations, prepared statements, pagination, file uploads, authentication, and security
Week 13
PDO vs mysqli
Connection
Queries
Prepared Statements
Fetching Data
CRUD
Search
Pagination
File Upload
Authentication
Security
1. PDO vs mysqli Why PDO is Better
Two Ways to Connect PHP to MySQL
PHP offers two main ways to interact with MySQL databases:
| Feature | PDO | mysqli |
| Supports multiple databases | Yes (MySQL, PostgreSQL, SQLite, etc.) | MySQL only |
| Named parameters | Yes (:name, :email) | No (only ? placeholders) |
| Object-oriented API | Yes | Yes (and procedural) |
| Error handling | Exceptions (try/catch) | Error codes |
| Security | Prepared statements | Prepared statements |
| Recommended | Yes - industry standard | Legacy projects only |
We Use PDO
PDO (PHP Data Objects) is the recommended way to work with databases in modern PHP. It supports multiple databases, has a cleaner API, and makes it easier to write secure code with prepared statements.
2. PDO Connection Connecting to MySQL
Basic Connection
db.php - Database Connection
<?php
// Database configuration
$host = "localhost";
$dbname = "online_store";
$username = "root";
$password = ""; // Default XAMPP has no password
try {
// Create PDO connection
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
$pdo = new PDO($dsn, $username, $password, [
// Throw exceptions on errors (instead of silent failures)
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
// Return results as associative arrays by default
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
// Use real prepared statements (not emulated)
PDO::ATTR_EMULATE_PREPARES => false,
]);
// echo "Connected successfully!"; // For testing only
} catch (PDOException $e) {
// Connection failed
die("Connection failed: " . $e->getMessage());
}
?>
PDO Options Explained
| Option | Value | Purpose |
ATTR_ERRMODE |
ERRMODE_EXCEPTION |
Throws PDOException on errors, so you can catch them with try/catch |
ATTR_DEFAULT_FETCH_MODE |
FETCH_ASSOC |
Returns rows as associative arrays (key => value) instead of numbered arrays |
ATTR_EMULATE_PREPARES |
false |
Uses real MySQL prepared statements for better security and performance |
Using the Connection File
Using db.php in other files
<?php
// Include the connection file at the top of every page that needs DB access
require_once "db.php";
// Now $pdo is available for database operations
$stmt = $pdo->query("SELECT * FROM products");
$products = $stmt->fetchAll();
?>
3. Executing Queries query() vs prepare()
query() - For Simple Queries (No User Input)
Using query()
<?php
require_once "db.php";
// query() is for static SQL with NO user input
$stmt = $pdo->query("SELECT * FROM products ORDER BY price DESC");
$products = $stmt->fetchAll();
// Count total rows
$stmt = $pdo->query("SELECT COUNT(*) FROM products");
$totalProducts = $stmt->fetchColumn();
echo "Total products: $totalProducts";
?>
prepare() + execute() - For Queries with User Input
Using prepare()
<?php
require_once "db.php";
// ALWAYS use prepare() when including user input in queries!
// This prevents SQL injection attacks.
$category_id = $_GET["category"] ?? 1;
// WRONG - SQL Injection vulnerable!
// $stmt = $pdo->query("SELECT * FROM products WHERE category_id = $category_id");
// CORRECT - Using prepared statement
$stmt = $pdo->prepare("SELECT * FROM products WHERE category_id = ?");
$stmt->execute([$category_id]);
$products = $stmt->fetchAll();
?>
4. Prepared Statements Positional and Named Placeholders
Positional Placeholders (?)
Positional Placeholders
<?php
// Each ? is replaced by the corresponding value in the array
$stmt = $pdo->prepare("SELECT * FROM products WHERE category_id = ? AND price < ?");
$stmt->execute([1, 100.00]); // First ? = 1, Second ? = 100.00
$products = $stmt->fetchAll();
// INSERT with positional placeholders
$stmt = $pdo->prepare("INSERT INTO products (name, price, quantity, category_id) VALUES (?, ?, ?, ?)");
$stmt->execute(["New Product", 29.99, 50, 2]);
?>
Named Placeholders (:name)
Named Placeholders
<?php
// Named placeholders are more readable and order-independent
$stmt = $pdo->prepare("
SELECT * FROM products
WHERE category_id = :category_id
AND price < :max_price
");
$stmt->execute([
":category_id" => 1,
":max_price" => 100.00,
]);
$products = $stmt->fetchAll();
// INSERT with named placeholders
$stmt = $pdo->prepare("
INSERT INTO products (name, price, quantity, category_id)
VALUES (:name, :price, :quantity, :category_id)
");
$stmt->execute([
":name" => "New Product",
":price" => 29.99,
":quantity" => 50,
":category_id" => 2,
]);
// Get the ID of the last inserted row
$newId = $pdo->lastInsertId();
echo "New product ID: $newId";
?>
NEVER Concatenate User Input into SQL!
// DANGEROUS - SQL Injection!
$name = $_POST["name"];
$pdo->query("SELECT * FROM users WHERE name = '$name'");
// If user enters: ' OR '1'='1 -> Returns ALL users!
// If user enters: '; DROP TABLE users; -- -> Deletes the table!
// SAFE - Always use prepared statements
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute([$name]);
5. Fetching Data fetch, fetchAll, fetchColumn
Fetch Methods
<?php
require_once "db.php";
// fetchAll() - Get ALL rows as an array of arrays
$stmt = $pdo->query("SELECT * FROM products");
$products = $stmt->fetchAll();
// $products = [
// ["id" => 1, "name" => "Smartphone", "price" => 599.99, ...],
// ["id" => 2, "name" => "Laptop", "price" => 1299.99, ...],
// ...
// ]
foreach ($products as $product) {
echo $product["name"] . ": $" . $product["price"] . "<br>";
}
// fetch() - Get ONE row (use for single record lookups)
$stmt = $pdo->prepare("SELECT * FROM products WHERE id = ?");
$stmt->execute([1]);
$product = $stmt->fetch();
// $product = ["id" => 1, "name" => "Smartphone", "price" => 599.99, ...]
if ($product) {
echo $product["name"];
} else {
echo "Product not found!";
}
// fetchColumn() - Get a single value from the first column
$stmt = $pdo->query("SELECT COUNT(*) FROM products");
$count = $stmt->fetchColumn();
echo "Total: $count";
// rowCount() - Number of affected rows (for INSERT/UPDATE/DELETE)
$stmt = $pdo->prepare("DELETE FROM products WHERE is_active = ?");
$stmt->execute([false]);
echo "Deleted: " . $stmt->rowCount() . " rows";
?>
6. CRUD Operations with PDO Create, Read, Update, Delete
CREATE - Insert a New Record
add.php - Insert Product
<?php
require_once "db.php";
if ($_SERVER["REQUEST_METHOD"] === "POST") {
$name = trim($_POST["name"]);
$price = (float) $_POST["price"];
$quantity = (int) $_POST["quantity"];
$category_id = (int) $_POST["category_id"];
$stmt = $pdo->prepare("
INSERT INTO products (name, price, quantity, category_id)
VALUES (:name, :price, :quantity, :category_id)
");
$stmt->execute([
":name" => $name,
":price" => $price,
":quantity" => $quantity,
":category_id" => $category_id,
]);
$newId = $pdo->lastInsertId();
echo "Product added with ID: $newId";
}
?>
READ - Display Records in HTML Table
index.php - List Products
<?php
require_once "db.php";
$stmt = $pdo->query("
SELECT p.*, c.name AS category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
ORDER BY p.created_at DESC
");
$products = $stmt->fetchAll();
?>
<table>
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Price</th>
<th>Category</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php foreach ($products as $p): ?>
<tr>
<td><?= $p["id"] ?></td>
<td><?= htmlspecialchars($p["name"]) ?></td>
<td>$<?= number_format($p["price"], 2) ?></td>
<td><?= htmlspecialchars($p["category_name"] ?? "N/A") ?></td>
<td>
<a href="edit.php?id=<?= $p["id"] ?>">Edit</a>
<a href="delete.php?id=<?= $p["id"] ?>">Delete</a>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
UPDATE - Edit a Record
edit.php - Update Product
<?php
require_once "db.php";
$id = (int) ($_GET["id"] ?? 0);
// Fetch current data to pre-fill the form
$stmt = $pdo->prepare("SELECT * FROM products WHERE id = ?");
$stmt->execute([$id]);
$product = $stmt->fetch();
if (!$product) {
die("Product not found!");
}
// Process update form
if ($_SERVER["REQUEST_METHOD"] === "POST") {
$stmt = $pdo->prepare("
UPDATE products
SET name = :name, price = :price, quantity = :quantity, category_id = :category_id
WHERE id = :id
");
$stmt->execute([
":name" => trim($_POST["name"]),
":price" => (float) $_POST["price"],
":quantity" => (int) $_POST["quantity"],
":category_id" => (int) $_POST["category_id"],
":id" => $id,
]);
// Redirect back to list with success message
header("Location: index.php?msg=updated");
exit;
}
?>
<!-- Form pre-filled with current values -->
<form method="POST">
<input name="name" value="<?= htmlspecialchars($product['name']) ?>">
<input name="price" value="<?= $product['price'] ?>">
<input name="quantity" value="<?= $product['quantity'] ?>">
<button type="submit">Update Product</button>
</form>
DELETE - Remove a Record
delete.php - Delete Product
<?php
require_once "db.php";
$id = (int) ($_GET["id"] ?? 0);
// Confirm before deleting
if (isset($_GET["confirm"]) && $_GET["confirm"] === "yes") {
$stmt = $pdo->prepare("DELETE FROM products WHERE id = ?");
$stmt->execute([$id]);
header("Location: index.php?msg=deleted");
exit;
}
// Show confirmation page
$stmt = $pdo->prepare("SELECT name FROM products WHERE id = ?");
$stmt->execute([$id]);
$product = $stmt->fetch();
?>
<p>Are you sure you want to delete "<?= htmlspecialchars($product["name"]) ?>"?</p>
<a href="delete.php?id=<?= $id ?>&confirm=yes">Yes, Delete</a>
<a href="index.php">Cancel</a>
7. Search WHERE LIKE with Prepared Statements
search.php
<?php
require_once "db.php";
$search = trim($_GET["q"] ?? "");
$results = [];
if (!empty($search)) {
// Add % wildcards to the search term, NOT in the SQL string
$stmt = $pdo->prepare("
SELECT p.*, c.name AS category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.name LIKE :search OR p.description LIKE :search
ORDER BY p.name ASC
");
$stmt->execute([":search" => "%$search%"]);
$results = $stmt->fetchAll();
}
?>
<form method="GET">
<input name="q" value="<?= htmlspecialchars($search) ?>" placeholder="Search products...">
<button>Search</button>
</form>
<p>Found <?= count($results) ?> results</p>
8. Pagination Displaying Data in Pages
index.php with Pagination
<?php
require_once "db.php";
// Pagination settings
$perPage = 10; // Items per page
$currentPage = max(1, (int) ($_GET["page"] ?? 1));
// Get total count
$stmt = $pdo->query("SELECT COUNT(*) FROM products");
$totalItems = $stmt->fetchColumn();
$totalPages = ceil($totalItems / $perPage);
// Calculate offset
$offset = ($currentPage - 1) * $perPage;
// Fetch current page data
$stmt = $pdo->prepare("SELECT * FROM products ORDER BY id DESC LIMIT :limit OFFSET :offset");
$stmt->bindValue(":limit", $perPage, PDO::PARAM_INT);
$stmt->bindValue(":offset", $offset, PDO::PARAM_INT);
$stmt->execute();
$products = $stmt->fetchAll();
?>
<!-- Display products here... -->
<!-- Pagination links -->
<div class="pagination">
<?php if ($currentPage > 1): ?>
<a href="?page=<?= $currentPage - 1 ?>">Previous</a>
<?php endif; ?>
<?php for ($i = 1; $i <= $totalPages; $i++): ?>
<a href="?page=<?= $i ?>"
<?php if ($i === $currentPage) echo 'class="active"'; ?>>
<?= $i ?>
</a>
<?php endfor; ?>
<?php if ($currentPage < $totalPages): ?>
<a href="?page=<?= $currentPage + 1 ?>">Next</a>
<?php endif; ?>
<span>Page <?= $currentPage ?> of <?= $totalPages ?></span>
</div>
Important: bindValue for LIMIT/OFFSET
When using LIMIT and OFFSET with prepared statements, you must use bindValue() with PDO::PARAM_INT because LIMIT/OFFSET require actual integers, not quoted strings.
9. File Upload $_FILES, move_uploaded_file
upload.php
<?php
if ($_SERVER["REQUEST_METHOD"] === "POST") {
// $_FILES contains uploaded file information
$file = $_FILES["image"];
// File properties
$fileName = $file["name"]; // Original filename
$tmpName = $file["tmp_name"]; // Temporary path on server
$fileSize = $file["size"]; // Size in bytes
$fileType = $file["type"]; // MIME type
$fileError = $file["error"]; // Error code (0 = no error)
// Validation
$allowedTypes = ["image/jpeg", "image/png", "image/gif", "image/webp"];
$maxSize = 5 * 1024 * 1024; // 5MB
if ($fileError !== 0) {
die("Upload error!");
}
if (!in_array($fileType, $allowedTypes)) {
die("Invalid file type! Only JPEG, PNG, GIF, WebP allowed.");
}
if ($fileSize > $maxSize) {
die("File too large! Max 5MB.");
}
// Generate unique filename to avoid conflicts
$extension = pathinfo($fileName, PATHINFO_EXTENSION);
$newName = uniqid("img_", true) . "." . $extension;
// Move file to uploads directory
$uploadDir = "uploads/";
if (!is_dir($uploadDir)) {
mkdir($uploadDir, 0755, true);
}
if (move_uploaded_file($tmpName, $uploadDir . $newName)) {
echo "File uploaded: $newName";
// Save $newName to database
} else {
die("Failed to move file!");
}
}
?>
<!-- IMPORTANT: enctype="multipart/form-data" is required for file uploads! -->
<form method="POST" enctype="multipart/form-data">
<input type="file" name="image" accept="image/*" required>
<button>Upload</button>
</form>
10. Authentication System Register, Login, Sessions
Password Security
Password Hashing
<?php
// NEVER store plain text passwords in the database!
// Hashing a password (during registration)
$password = "mySecurePassword123";
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);
// Result: "$2y$10$KjKi.UG1w.7S7LQz..." (60+ characters)
// Each hash is unique, even for the same password!
// Verifying a password (during login)
$inputPassword = "mySecurePassword123";
if (password_verify($inputPassword, $hashedPassword)) {
echo "Password is correct!";
} else {
echo "Wrong password!";
}
?>
Registration
register.php
<?php
require_once "db.php";
$errors = [];
if ($_SERVER["REQUEST_METHOD"] === "POST") {
$name = trim($_POST["name"]);
$email = trim($_POST["email"]);
$password = $_POST["password"];
$confirm = $_POST["confirm_password"];
// Validate
if (empty($name)) $errors[] = "Name is required";
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) $errors[] = "Invalid email";
if (strlen($password) < 6) $errors[] = "Password must be at least 6 characters";
if ($password !== $confirm) $errors[] = "Passwords don't match";
// Check if email already exists
$stmt = $pdo->prepare("SELECT id FROM users WHERE email = ?");
$stmt->execute([$email]);
if ($stmt->fetch()) {
$errors[] = "Email already registered";
}
if (empty($errors)) {
// Hash the password
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);
// Insert new user
$stmt = $pdo->prepare("
INSERT INTO users (name, email, password)
VALUES (:name, :email, :password)
");
$stmt->execute([
":name" => $name,
":email" => $email,
":password" => $hashedPassword,
]);
header("Location: login.php?registered=1");
exit;
}
}
?>
Login
login.php
<?php
session_start();
require_once "db.php";
// If already logged in, redirect to dashboard
if (isset($_SESSION["user_id"])) {
header("Location: dashboard.php");
exit;
}
$error = "";
if ($_SERVER["REQUEST_METHOD"] === "POST") {
$email = trim($_POST["email"]);
$password = $_POST["password"];
// Find user by email
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);
$user = $stmt->fetch();
// Verify password
if ($user && password_verify($password, $user["password"])) {
// Regenerate session ID to prevent session fixation
session_regenerate_id(true);
// Store user info in session
$_SESSION["user_id"] = $user["id"];
$_SESSION["username"] = $user["name"];
$_SESSION["email"] = $user["email"];
$_SESSION["role"] = $user["role"];
header("Location: dashboard.php");
exit;
} else {
$error = "Invalid email or password";
}
}
?>
Protected Page (Dashboard)
dashboard.php
<?php
session_start();
// Check if user is logged in (middleware)
if (!isset($_SESSION["user_id"])) {
header("Location: login.php");
exit;
}
// User is authenticated - show dashboard
$username = htmlspecialchars($_SESSION["username"]);
$email = htmlspecialchars($_SESSION["email"]);
$role = htmlspecialchars($_SESSION["role"]);
?>
<h1>Welcome, <?= $username ?>!</h1>
<p>Email: <?= $email ?></p>
<p>Role: <?= $role ?></p>
<a href="logout.php">Logout</a>
Logout
logout.php
<?php
session_start();
// Clear all session data
$_SESSION = [];
// Delete the session cookie
if (ini_get("session.use_cookies")) {
$params = session_get_cookie_params();
setcookie(
session_name(), "", time() - 42000,
$params["path"], $params["domain"],
$params["secure"], $params["httponly"]
);
}
// Destroy the session
session_destroy();
// Redirect to login page
header("Location: login.php");
exit;
?>
11. Security SQL Injection, XSS, CSRF
SQL Injection Prevention
SQL injection is when an attacker inserts malicious SQL code through user input. Always use prepared statements to prevent this.
SQL Injection Example
<?php
// VULNERABLE CODE (never do this!):
$email = $_POST["email"]; // User enters: ' OR '1'='1' --
$sql = "SELECT * FROM users WHERE email = '$email'";
// Becomes: SELECT * FROM users WHERE email = '' OR '1'='1' --'
// This returns ALL users! The attacker can see everything.
// SAFE CODE (always use prepared statements!):
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$_POST["email"]]);
// The input is treated as a literal string, not SQL code.
?>
XSS Prevention
Cross-Site Scripting (XSS) is when an attacker injects JavaScript through user input that gets displayed on the page.
XSS Prevention
<?php
// VULNERABLE: Displays user input directly
echo "Welcome, " . $_GET["name"];
// If name = <script>alert('hacked!')</script>
// The script will run in the user's browser!
// SAFE: Always escape output with htmlspecialchars()
echo "Welcome, " . htmlspecialchars($_GET["name"], ENT_QUOTES, "UTF-8");
// Converts < to < and > to >
// The script is displayed as text, not executed.
?>
CSRF Protection Basics
Cross-Site Request Forgery (CSRF) tricks a logged-in user into making unintended requests (like deleting their account).
CSRF Token
<?php
session_start();
// Generate a CSRF token and store in session
if (!isset($_SESSION["csrf_token"])) {
$_SESSION["csrf_token"] = bin2hex(random_bytes(32));
}
?>
<!-- Include token in every form -->
<form method="POST">
<input type="hidden" name="csrf_token"
value="<?= $_SESSION["csrf_token"] ?>">
<!-- other form fields -->
<button>Submit</button>
</form>
<?php
// Verify token on form submission
if ($_SERVER["REQUEST_METHOD"] === "POST") {
if (!isset($_POST["csrf_token"]) ||
$_POST["csrf_token"] !== $_SESSION["csrf_token"]) {
die("CSRF validation failed!");
}
// Process form safely...
}
?>
Security Checklist
| Threat | Prevention |
| SQL Injection | Always use prepared statements with PDO |
| XSS | Always use htmlspecialchars() when displaying user input |
| CSRF | Use CSRF tokens in forms |
| Password theft | Use password_hash() and password_verify() |
| Session hijacking | Use session_regenerate_id() after login |
| File upload attacks | Validate file type, size, and rename files |
| Directory traversal | Never use user input directly in file paths |
Al-Aqsa University - Web Development 1