15. CRUD

Leer en español

You're going to read the word CRUD a lot in the programming world. It's a simple acronym:

  • Create -> Create
  • Read -> Read
  • Update -> Update
  • Delete -> Delete

Or, in other words, managing a table in a database with the minimum set of operations.

table with data

Preparing the database

Before we start writing our queries we'll need data. Run the following SQL code to get a small list of books. It will create a database, a table and a few sample rows.

CREATE DATABASE ejemplo DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
USE ejemplo;
CREATE TABLE IF NOT EXISTS libros (
    codigo INT AUTO_INCREMENT,
    titulo VARCHAR(255) NOT NULL,
    autor VARCHAR(255) NOT NULL,
    disponible TINYINT NOT NULL,
    PRIMARY KEY (codigo)
)  ENGINE=INNODB;
INSERT INTO libros VALUES ('', 'War and Peace', 'Leo Tolstoy', TRUE);
INSERT INTO libros VALUES ('', 'The Adventures of Huckleberry Finn', 'Mark Twain', FALSE);
INSERT INTO libros VALUES ('', 'Hamlet', 'William Shakespeare', TRUE);
INSERT INTO libros VALUES ('', 'In Search of Lost Time', 'Marcel Proust', FALSE);
INSERT INTO libros VALUES ('', 'Don Quixote', 'Miguel de Cervantes', TRUE);

Reading data

Our PHP code will make a simple query to the database.

// Variables
$hostDB = '127.0.0.1';
$nombreDB = 'ejemplo';
$usuarioDB = 'root';
$contrasenyaDB = '';
// Connect to the database
$hostPDO = "mysql:host=$hostDB;dbname=$nombreDB;";
$miPDO = new PDO($hostPDO, $usuarioDB, $contrasenyaDB);
// Prepare the SELECT
$miConsulta = $miPDO->prepare('SELECT * FROM libros;');
// Run the query
$miConsulta->execute();

And then we'll iterate over each row with a foreach into a table.

<table>
    <tr>
        <th>Code</th>
        <th>Title</th>
        <th>Author</th>
        <th>Available?</th>
    </tr>
<?php foreach ($miConsulta as $clave => $valor): ?>
    <tr>
       <td><?= $valor['codigo']; ?></td>
       <td><?= $valor['titulo']; ?></td>
       <td><?= $valor['autor']; ?></td>
       <td><?= $valor['disponible'] ? 'Yes' : 'No'; ?></td>
    </tr>
<?php endforeach; ?>
</table>

All the code together would look like this.

<?php
// Variables
$hostDB = '127.0.0.1';
$nombreDB = 'ejemplo';
$usuarioDB = 'root';
$contrasenyaDB = '';
// Connect to the database
$hostPDO = "mysql:host=$hostDB;dbname=$nombreDB;";
$miPDO = new PDO($hostPDO, $usuarioDB, $contrasenyaDB);
// Prepare the SELECT
$miConsulta = $miPDO->prepare('SELECT * FROM libros;');
// Run the query
$miConsulta->execute();

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Read - CRUD PHP</title>
    <style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        table td {
            border: 1px solid orange;
            text-align: center;
            padding: 1.3rem;
        }
        .button {
            border-radius: .5rem;
            color: white;
            background-color: orange;
            padding: 1rem;
            text-decoration: none;
        }
    </style>
</head>
<body>
    <p><a class="button" href="nuevo.php">Create</a></p>
    <table>
        <tr>
            <th>Code</th>
            <th>Title</th>
            <th>Author</th>
            <th>Available?</th>
            <td></td>
            <td></td>
        </tr>
    <?php foreach ($miConsulta as $clave => $valor): ?>
        <tr>
           <td><?= $valor['codigo']; ?></td>
           <td><?= $valor['titulo']; ?></td>
           <td><?= $valor['autor']; ?></td>
           <td><?= $valor['disponible'] ? 'Yes' : 'No'; ?></td>
           <!-- Used later to indicate whether you want to modify or delete the record -->
           <td><a class="button" href="modificar.php?codigo=<?= $valor['codigo'] ?>">Modify</a></td>
           <td><a class="button" href="borrar.php?codigo=<?= $valor['codigo'] ?>">Delete</a></td>
        </tr>
    <?php endforeach; ?>
    </table>
</body>
</html>

If you're going to read a single record you can use $registro = $miConsulta->fetch();.

Have you tried running several foreach loops with the same query? It's not possible, because the pointer stays at the end of the list. A trick is to use $registros = $miConsulta->fetchAll(). You'll end up with an array that you can read as many times as you need.

Create a new record

We put together a small form asking for: title, author and availability.

<form method="post">
    <p>
        <label for="titulo">Title</label>
        <input id="titulo" type="text" name="titulo">
    </p>
    <p>
        <label for="autor">Author</label>
        <input id="autor" type="text" name="autor">
    </p>
    <p>
        <div>Available?</div>
        <input id="si-disponible" type="radio" name="disponible" value="1" checked> <label for="si-disponible">Yes</label>
        <input id="no-disponible" type="radio" name="disponible" value="0"> <label for="no-disponible">No</label>
    </p>
    <p>
        <input type="submit" value="Save">
    </p>
</form>

Then, in the same file, we tell it that if we receive a POST method it should save it to the database with an INSERT.

// Check whether we receive data via POST
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Collect variables
    $titulo = isset($_REQUEST['titulo']) ? $_REQUEST['titulo'] : null;
    $autor = isset($_REQUEST['autor']) ? $_REQUEST['autor'] : null;
    $disponible = isset($_REQUEST['disponible']) ? $_REQUEST['disponible'] : null;
    // Variables
    $hostDB = '127.0.0.1';
    $nombreDB = 'ejemplo';
    $usuarioDB = 'root';
    $contrasenyaDB = '';
    // Connect to the database
    $hostPDO = "mysql:host=$hostDB;dbname=$nombreDB;";
    $miPDO = new PDO($hostPDO, $usuarioDB, $contrasenyaDB);
    // Prepare the INSERT
    $miInsert = $miPDO->prepare('INSERT INTO libros (titulo, autor, disponible) VALUES (:titulo, :autor, :disponible)');
    // Run the INSERT with the data
    $miInsert->execute(
        array(
            'titulo' => $titulo,
            'autor' => $autor,
            'disponible' => $disponible
        )
    );
    // Redirect to Read
    header('Location: leer.php');
}

All together it would look like this.

<?php
// Check whether we receive data via POST
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Collect variables
    $titulo = isset($_REQUEST['titulo']) ? $_REQUEST['titulo'] : null;
    $autor = isset($_REQUEST['autor']) ? $_REQUEST['autor'] : null;
    $disponible = isset($_REQUEST['disponible']) ? $_REQUEST['disponible'] : null;
    // Variables
    $hostDB = '127.0.0.1';
    $nombreDB = 'ejemplo';
    $usuarioDB = 'root';
    $contrasenyaDB = '';
    // Connect to the database
    $hostPDO = "mysql:host=$hostDB;dbname=$nombreDB;";
    $miPDO = new PDO($hostPDO, $usuarioDB, $contrasenyaDB);
    // Prepare the INSERT
    $miInsert = $miPDO->prepare('INSERT INTO libros (titulo, autor, disponible) VALUES (:titulo, :autor, :disponible)');
    // Run the INSERT with the data
    $miInsert->execute(
        array(
            'titulo' => $titulo,
            'autor' => $autor,
            'disponible' => $disponible
        )
    );
    // Redirect to Read
    header('Location: leer.php');
}
?>
<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <title>Create - CRUD PHP</title>
</head>
<body>
    <form action="" method="post">
        <p>
            <label for="titulo">Title</label>
            <input id="titulo" type="text" name="titulo">
        </p>
        <p>
            <label for="autor">Author</label>
            <input id="autor" type="text" name="autor">
        </p>
        <p>
            <div>Available?</div>
            <input id="si-disponible" type="radio" name="disponible" value="1" checked> <label for="si-disponible">Yes</label>
            <input id="no-disponible" type="radio" name="disponible" value="0"> <label for="no-disponible">No</label>
        </p>
        <p>
            <input type="submit" value="Save">
        </p>
    </form>
</body>
</html>

Modify

Our code must perform two actions: get the code of the book to modify and overwrite the data. When entering the page we'll do it with a route similar to this one:

modificar.php?codigo=3

To capture this value we do it like any other variable.

$codigo = isset($_REQUEST['codigo']) ? $_REQUEST['codigo'] : null;

Our PHP code would look like this.

// Variables
$hostDB = '127.0.0.1';
$nombreDB = 'ejemplo';
$usuarioDB = 'root';
$contrasenyaDB = '';
$codigo = isset($_REQUEST['codigo']) ? $_REQUEST['codigo'] : null;
$titulo = isset($_REQUEST['titulo']) ? $_REQUEST['titulo'] : null;
$autor = isset($_REQUEST['autor']) ? $_REQUEST['autor'] : null;
$disponible = isset($_REQUEST['disponible']) ? $_REQUEST['disponible'] : null;

// Connect to the database
$hostPDO = "mysql:host=$hostDB;dbname=$nombreDB;";
$miPDO = new PDO($hostPDO, $usuarioDB, $contrasenyaDB);

// Check whether we receive data via POST
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Prepare the UPDATE
    $miUpdate = $miPDO->prepare('UPDATE libros SET titulo = :titulo, autor = :autor, disponible = :disponible WHERE codigo = :codigo');
    // Run the UPDATE with the data
    $miUpdate->execute(
        [
            'codigo' => $codigo,
            'titulo' => $titulo,
            'autor' => $autor,
            'disponible' => $disponible
        ]
    );
    // Redirect to Read
    header('Location: leer.php');
} else {
    // Prepare the SELECT
    $miConsulta = $miPDO->prepare('SELECT * FROM libros WHERE codigo = :codigo;');
    // Run the query
    $miConsulta->execute(
        [
            codigo => $codigo
        ]
    );
}

// Get a single result
$libro = $miConsulta->fetch();

While our HTML will be similar to the creation form, except that we'll need to fill in the value attributes.

<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <title>Create - CRUD PHP</title>
</head>
<body>
    <form method="post">
        <p>
            <label for="titulo">Title</label>
            <input id="titulo" type="text" name="titulo" value="<?= $libro['titulo'] ?>">
        </p>
        <p>
            <label for="autor">Author</label>
            <input id="autor" type="text" name="autor" value="<?= $libro['autor'] ?>">
        </p>
        <p>
            <div>Available?</div>
            <input id="si-disponible" type="radio" name="disponible" value="1"<?= $libro['disponible'] ? ' checked' : '' ?>> <label for="si-disponible">Yes</label>
            <input id="no-disponible" type="radio" name="disponible" value="0"<?= !$libro['disponible'] ? ' checked' : '' ?>> <label for="no-disponible">No</label>
        </p>
        <p>
            <input type="hidden" name="codigo" value="<?= $codigo ?>">
            <input type="submit" value="Modify">
        </p>
    </form>
</body>
</html>

All together it would compact into something like this.

<?php
// Variables
$hostDB = '127.0.0.1';
$nombreDB = 'ejemplo';
$usuarioDB = 'root';
$contrasenyaDB = '';
$codigo = isset($_REQUEST['codigo']) ? $_REQUEST['codigo'] : null;
$titulo = isset($_REQUEST['titulo']) ? $_REQUEST['titulo'] : null;
$autor = isset($_REQUEST['autor']) ? $_REQUEST['autor'] : null;
$disponible = isset($_REQUEST['disponible']) ? $_REQUEST['disponible'] : null;

// Connect to the database
$hostPDO = "mysql:host=$hostDB;dbname=$nombreDB;";
$miPDO = new PDO($hostPDO, $usuarioDB, $contrasenyaDB);

// Check whether we receive data via POST
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Prepare the UPDATE
    $miUpdate = $miPDO->prepare('UPDATE libros SET titulo = :titulo, autor = :autor, disponible = :disponible WHERE codigo = :codigo');
    // Run the UPDATE with the data
    $miUpdate->execute(
        [
            'codigo' => $codigo,
            'titulo' => $titulo,
            'autor' => $autor,
            'disponible' => $disponible
        ]
    );
    // Redirect to Read
    header('Location: leer.php');
} else {
    // Prepare the SELECT
    $miConsulta = $miPDO->prepare('SELECT * FROM libros WHERE codigo = :codigo;');
    // Run the query
    $miConsulta->execute(
        [
            codigo => $codigo
        ]
    );
}

// Get a result
$libro = $miConsulta->fetch();

?>
<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <title>Create - CRUD PHP</title>
</head>
<body>
    <form method="post">
        <p>
            <label for="titulo">Title</label>
            <input id="titulo" type="text" name="titulo" value="<?= $libro['titulo'] ?>">
        </p>
        <p>
            <label for="autor">Author</label>
            <input id="autor" type="text" name="autor" value="<?= $libro['autor'] ?>">
        </p>
        <p>
            <div>Available?</div>
            <input id="si-disponible" type="radio" name="disponible" value="1"<?= $libro['disponible'] ? ' checked' : '' ?>> <label for="si-disponible">Yes</label>
            <input id="no-disponible" type="radio" name="disponible" value="0"<?= !$libro['disponible'] ? ' checked' : '' ?>> <label for="no-disponible">No</label>
        </p>
        <p>
            <input type="hidden" name="codigo" value="<?= $codigo ?>">
            <input type="submit" value="Modify">
        </p>
    </form>
</body>
</html>

Delete

Again we have a route with the code to delete.

borrar.php?codigo=3

Our PHP would capture it, delete the record with DELETE and go back to the page that displays the data.

We create a file called borrar.php and include the following code.

// Variables
$hostDB = '127.0.0.1';
$nombreDB = 'ejemplo';
$usuarioDB = 'root';
$contrasenyaDB = '';
// Connect to the database
$hostPDO = "mysql:host=$hostDB;dbname=$nombreDB;";
$miPDO = new PDO($hostPDO, $usuarioDB, $contrasenyaDB);
// Get the code of the book to delete
$codigo = isset($_REQUEST['codigo']) ? $_REQUEST['codigo'] : null;
// Prepare the DELETE
$miConsulta = $miPDO->prepare('DELETE FROM libros WHERE codigo = :codigo');
// Run the SQL statement
$miConsulta->execute([
    codigo => $codigo
]);
// Redirect to the PHP file with all the data
header('Location: leer.php');

A tip from experience: Never delete, deactivate instead. Add a boolean column that lets you enable or disable the data. Among other things, you'll avoid problems with some relationships.

Activity 1

The RAE asks you to set up a 100-word flash fiction contest.

  • Show a form where the following can be submitted: title, story, name and email.
  • Save the information in the database only if the data is valid.
  • Send a confirmation email.
  • List the story at the bottom along with all the previous ones.
  • Show a counter with all the saved stories.
  • Add a button to give a Like.
Activity 2

It's time to write down somewhere all the series you've watched, there are too many and your memory is very short.

  • Create a table in MySQL named series.
  • Create a field (input) to type the title and a button with the text "Add".
  • When the button is pressed it will be saved into the MySQL table.
  • Show all the titles from the database in an HTML table.
  • Include, in each row of the table, a button with the text "Delete".
  • When it's pressed, the title should disappear from the database.
  • Include, in each row of the table, a button with the text "Modify".
  • When pressed it will take you to a new page where you can modify the title text. When you save the change it will redirect you back to the previous page.

Pro:

  • Add the rating field to the MySQL table. Modify your activity so it can include, in addition to the title, a rating between 0 and 10.
  • Sort the results by rating.

Pro 2:

  • Build a search feature.
Activity 3
  • Build a CRUD to store the following information: First name, last name, phone and Email.

This work is under a Attribution-NonCommercial-NoDerivatives 4.0 International license.

Building SPAs with Django and HTML Over the Wire: Learn to build real-time single page applications with Python

Building SPAs with Django and HTML Over the Wire: Learn to build real-time single page applications with Python

The HTML over WebSockets approach simplifies single-page application (SPA) development and lets you bypass learning a JavaScript rendering framework such as React, Vue, or Angular, moving the logic to Python. This web application development book provides you with all the Django tools you need to simplify your developments with real-time results.

Buy the book

Help me keep writing

Every coffee gives me a push toward the next article.

Ko-fi donationsSure, it's on me!

Comments

There are no comments yet.