Database in PHP with PDO

CRUD interactive (Create, Read, Delete) with prepared statements

🎯 Live Demo (simulated table so you don't need your own DB)

Add product

IDNamePrice
1 Wireless mouse 19.99 €
2 Monitor 24" 129.00 €

📖 The same CRUD with PDO real (MySQL)

Connection

$pdo = new PDO(
    'mysql:host=localhost;dbname=mi_tienda;charset=utf8mb4',
    'usuario',
    'password',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

📝 INSERT with prepared statement

$stmt = $pdo->prepare(
    "INSERT INTO productos (nombre, precio) VALUES (:nombre, :precio)"
);
$stmt->execute([
    'nombre' => $_POST['nombre'],
    'precio' => $_POST['precio']
]);
💡 Concept: Prepared statements

The placeholders :nombre and :precio are sent separately from the SQL. So even if the user types something like '; DROP TABLE productos; --, PDO treats it as plain text, not as SQL code — this prevents SQL.

📝 SELECT (read all)

$stmt = $pdo->query("SELECT id, nombre, precio FROM productos");
$productos = $stmt->fetchAll(PDO::FETCH_ASSOC);

📝 DELETE with parameter

$stmt = $pdo->prepare("DELETE FROM productos WHERE id = :id");
$stmt->execute(['id' => $_POST['id']]);

🎯 Key Concepts

PDO

  • A single layer for MySQL, SQLite, PostgreSQL…
  • Throws exceptions with ERRMODE_EXCEPTION

Prepared statements

  • Separate the SQL from user data
  • Prevent injection SQL

fetchAll / execute

  • fetchAll(PDO::FETCH_ASSOC) for associative arrays
  • execute([...]) to pass the parameters

💪 Practical Exercise

Complete the CRUD

  1. Add an "edit" action that updates name and price with a UPDATE ... WHERE id = :id.
  2. Wrap the connection in a try/catch catching PDOException.
  3. Add pagination with LIMIT :inicio, :cantidad.

Solution 1

$stmt = $pdo->prepare(
    "UPDATE productos SET nombre = :nombre, precio = :precio WHERE id = :id"
);
$stmt->execute(['nombre' => $nombre, 'precio' => $precio, 'id' => $id]);

Solution 2

try {
    $pdo = new PDO($dsn, $usuario, $clave, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
} catch (PDOException $e) {
    die(t('Error de conexión: ') . $e->getMessage());
}

Solution 3

$stmt = $pdo->prepare("SELECT * FROM productos LIMIT :inicio, :cantidad");
$stmt->bindValue(':inicio', $inicio, PDO::PARAM_INT);
$stmt->bindValue(':cantidad', $cantidad, PDO::PARAM_INT);
$stmt->execute();