Project: a guestbook with PHP and MySQL

CRUD is the four things you do with data: Create, Read, Update, Delete. Almost every web app is, at its core, a CRUD over a database. Here we build one with a guestbook.

Simulator

Envía un mensaje y borra alguno. La consola muestra la consulta SQL equivalente:

Step 1: the table

CREATE TABLE visitas (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  nombre  VARCHAR(80)  NOT NULL,
  mensaje VARCHAR(500) NOT NULL,
  fecha   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: connect with PDO

<?php
$pdo = new PDO(
    "mysql:host=localhost;dbname=miweb;charset=utf8mb4",
    "usuario", "contraseña",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

Step 3: Create and Read

// CREATE — consulta preparada: los datos van APARTE del SQL
if ($_SERVER["REQUEST_METHOD"] === "POST") {
    $stmt = $pdo->prepare(
        "INSERT INTO visitas (nombre, mensaje) VALUES (?, ?)"
    );
    $stmt->execute([$_POST["nombre"], $_POST["mensaje"]]);
    header("Location: libro.php");   // recargar para no reenviar al refrescar
    exit;
}

// READ
$visitas = $pdo
    ->query("SELECT id, nombre, mensaje, fecha FROM visitas ORDER BY fecha DESC")
    ->fetchAll(PDO::FETCH_ASSOC);

Step 4: display (with escaping) and delete

<?php foreach ($visitas as $v): ?>
  <article>
    <strong><?= htmlspecialchars($v["nombre"]) ?></strong>
    <p><?= htmlspecialchars($v["mensaje"]) ?></p>
    <a href="libro.php?borrar=<?= $v["id"] ?>">Borrar</a>
  </article>
<?php endforeach; ?>

<?php
// DELETE
if (isset($_GET["borrar"])) {
    $stmt = $pdo->prepare("DELETE FROM visitas WHERE id = ?");
    $stmt->execute([$_GET["borrar"]]);
    header("Location: libro.php"); exit;
}

htmlspecialchars() when displaying prevents XSS: if someone signs with <script>, it shows as text.

Where to go next

• UPDATE: an edit form that runs UPDATE visitas SET ... WHERE id = ?.
• Pagination with LIMIT and OFFSET.
• A CSRF token in the form to prevent forged submissions.
• An admin login to moderate the messages.