Unit 11 ยท Database Operations

11.2CRUD Operations

CRUD means Create, Read, Update, Delete, matching SQL INSERT, SELECT, UPDATE, DELETE.

SQL injection warning. Never paste user input into SQL text. An attacker typing ' OR '1'='1 could read or destroy your data. Always use prepared statements with placeholders (? or :name), as below.

Create (INSERT)

PHP
$stmt = $pdo->prepare('INSERT INTO products (name, description, price, stock) VALUES (?, ?, ?, ?)');
$stmt->execute([$name, $description, $price, $stock]);
$newId = $pdo->lastInsertId();

Read (SELECT)

PHP
// Many rows
$stmt = $pdo->query('SELECT * FROM products ORDER BY created_at DESC');
$products = $stmt->fetchAll();

foreach ($products as $p) {
    echo htmlspecialchars($p['name']) . ' - Rs. ' . number_format($p['price'], 2) . '<br>';
}

// One row with a parameter
$stmt = $pdo->prepare('SELECT * FROM products WHERE id = ?');
$stmt->execute([$_GET['id'] ?? 0]);
$product = $stmt->fetch();          // false if not found
if (!$product) { http_response_code(404); exit('Not found'); }

// Searching
$stmt = $pdo->prepare('SELECT * FROM products WHERE name LIKE ?');
$stmt->execute(['%' . $q . '%']);

Update (UPDATE)

PHP
$stmt = $pdo->prepare('UPDATE products SET name = ?, price = ?, stock = ? WHERE id = ?');
$stmt->execute([$name, $price, $stock, $id]);
echo $stmt->rowCount() . ' row(s) updated';

Delete (DELETE)

PHP
$stmt = $pdo->prepare('DELETE FROM products WHERE id = ?');
$stmt->execute([$id]);

Always include WHERE; without it every row is changed or deleted. Perform deletes with POST (not a plain link) and ask for confirmation.

Useful SQL: WHERE price > 100 AND stock > 0, ORDER BY price ASC, LIMIT 10 OFFSET 20, COUNT(*), SUM(), AVG(), GROUP BY, and joins:

SQL
SELECT o.id, u.name, o.total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

Transactions make several queries succeed or fail together (needed for orders; see Unit 13):

PHP
$pdo->beginTransaction();
try {
    // insert order, insert order items, reduce stock
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

Mini application: Product manager. Pages: index.php (list, with Edit/Delete), add.php (form then INSERT), edit.php?id= (prefilled form then UPDATE), delete.php (POST then DELETE). Use one shared db.php, validate on the server, escape all output, and redirect after each successful write.

Check yourself. The questions for this part are on the 11.3 Review Questions page, with answers to check your work.