15. CRUD

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.

Desafíos de programación atemporales y multiparadigmáticos

Desafíos de programación atemporales y multiparadigmáticos

Te encuentras ante un librillo de actividades, divididas en 2 niveles de dificultad. Te enfrentarás a los casos más comunes que te puedes encontrar en pruebas técnicas o aprender conceptos elementales de programación.

Buy the book

Will you buy me a coffee?

This is how I keep writing without ads or paywalls.

Ko-fi donationsSure, it's on me!

Comments

There are no comments yet.