MySQL — Delete, Update & Limit

DELETE FROM removes rows. Always use WHERE — DELETE without WHERE wipes the entire table.

Delete Data

DELETE FROM removes rows. Always use WHERE — DELETE without WHERE wipes the entire table.

Real-life example: DELETE is removing expired items from a fridge — specify which items, not everything.

Safe DELETE

<?php
$id = filter_input(INPUT_POST, "id", FILTER_VALIDATE_INT);
if ($id) {
  $stmt = $pdo->prepare("DELETE FROM students WHERE id = :id");
  $stmt->execute([":id" => $id]);
  echo "Deleted row $id";
}
?>

SQL DELETE

DELETE FROM students WHERE id = 5;
DELETE FROM students WHERE score < 50;

Update Data

UPDATE table SET column = value WHERE condition changes existing rows. Combine with prepared statements.

Real-life example: UPDATE is editing one row in a spreadsheet — change Asha's score from 88 to 90.

UPDATE with PDO

<?php
$stmt = $pdo->prepare(
  "UPDATE students SET score = :score WHERE id = :id"
);
$stmt->execute([":score" => 95, ":id" => 1]);
echo "Rows updated: " . $stmt->rowCount();
?>

SQL UPDATE

UPDATE students SET score = 90 WHERE name = 'Asha';
UPDATE courses SET level = 'advanced' WHERE id = 3;

Limit

LIMIT caps how many rows return — essential for pagination. Use OFFSET for page 2, 3, etc.

Real-life example: LIMIT is reading only the first 10 search results instead of all million matches.

LIMIT and pagination

<?php
$page = 2;
$perPage = 10;
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare(
  "SELECT * FROM students ORDER BY id LIMIT :limit OFFSET :offset"
);
$stmt->bindValue(":limit", $perPage, PDO::PARAM_INT);
$stmt->bindValue(":offset", $offset, PDO::PARAM_INT);
$stmt->execute();
?>

SQL LIMIT

SELECT * FROM students ORDER BY score DESC LIMIT 5;
SELECT * FROM students LIMIT 10 OFFSET 20; -- page 3, 10 per page