Hello World! My name is Francisco, fcoterroba on the Internet and today I bring you a post in which we are going to talk, theoretically and practically, what a CRUD is and how to carry out a CRUD in the most used web back-end language today, PHP.
Performing a CRUD, regardless of the programming language used, is one of the most basic points that we must know how to do to start considering ourselves developers. If right now you don't know what a CRUD is, don't worry, I'll explain what it is shortly.
Before we start with this, maybe you should start playing around with PHP by following the tutorial I uploaded a few months ago in which we learned how to capture forms using PHP in addition to using Google's Captcha service. You can read the full post here.
Of course, before starting to develop in PHP, we will need a web server enabled with PHP and MySQL, for this you can install XAMPP on any operating system or follow mi tutorial if you prefer the Window's native option.
Before starting, although later I will explain what it is, I will I recommend visiting a post that I uploaded more than a month ago, in which I explain many of the most used computer terms in our day to day. Since, in this post, you will see words that you probably do not sound a lot. 🤯 You can reed the post here.
I also want to remind you that a few months ago I uploaded a video to my YouTube channel, very interesting, focused on home automation. Specifically, we connect, configure and install a smart light bulb 💡 with which you can change its color, turn it off, turn it on and much more simply by using your mobile phone and/or voice assistants such as Google, Alexa, etc. 👇🏻
Now yes, let's start!
What is a CRUD?
According to Wikipedia, in Computer Science, CRUD is an acronym that refers to the basic functions in databases.
This acronym was first popularized by James Martin in 1980 in his book Managing the Data-base Enviroment.
CRUD -> Create, Read, Update and Delete. Crear, Leer, Actualizar y Borrar.
In a nutshell, CRUD summarizes the functions required by a user to create and manage data. Any web application that works, to a greater or lesser extent, with data, will use this acronym.
And that is why it is so important to know what it is and how to do it.
1️⃣ FIRST STEP
The first thing we will have to do is open our web server by turning on both the PHP module and the MySQL module or the database manager we use. In my case it will be MySQL. All this is set up in a LAMP with php 7.4.18 with MySQL 8.0.23 on Linux Mint 20.1 Cinnamon.
Later we are going to create a folder within the web server called, for example, crud_php
Once this is done, we can create the four files needed initially: create.php, read.php, update.php, delete.php empty at the moment
Also, as a bonus of good practices, we are going to create a file called conn.php. I will explain its use later.
We should have something more or less like this:
2️⃣ SECOND STEP 2️⃣
Let's start editing the conn file since, as I said before, it is good practice to isolate as much code as possible into functions, objects and so on. So, we are going to use this script to make a connection to the database.
This script has only 10 lines of code including the try/catch and message outputs.
<?php
try {
## Creamos la variable $dbh que es la conexión completa a la base de datos, pasándole
# los parámetros de conexión del host, la base de datos, el usuario y la contraseña
$dbh = new PDO("mysql:host=127.0.0.1;dbname=crud_example_php", "fcoterroba", "Password123#@!");
} catch (PDOException $e){
$dbh = $e->getMessage();
}
?>Once we have made this connection and have verified that it works (we can put echoes in the try and catch), we go to the next step.
3️⃣ THIRD STEP 3️⃣
For the third step we are going to need data, so we are going to create a simple table with an autoincremental ID, first name and last name. The most basic.
-- Poner el nombre de la tabla CREATE TABLE main_data( -- Añadir un campo ID para controlar, que no pueda ser NULL y que se autoincremente en cada registro ID int NOT NULL AUTO_INCREMENT, -- Nombre y apellidos de tipo texto y máximo 255 nombre VARCHAR(255), apellidos VARCHAR(255), -- Indicamoss que la clave primaria de la tabla es el ID PRIMARY KEY (ID) );
Once created, let's add some information.
INSERT INTO main_data (nombre, apellidos) VALUES ("Nuria del Mar", "Lara Molina");
INSERT INTO main_data (nombre, apellidos) VALUES ("Adolfo", "Borras");
INSERT INTO main_data (nombre, apellidos) VALUES ("Cayetano", "Montero");
INSERT INTO main_data (nombre, apellidos) VALUES ("Susi", "Pote");
INSERT INTO main_data (nombre, apellidos) VALUES ("Susana", "Oria");
INSERT INTO main_data (nombre, apellidos) VALUES ("Javier", "Arnau");
INSERT INTO main_data (nombre, apellidos) VALUES ("Leonardo", "Messi");
INSERT INTO main_data (nombre, apellidos) VALUES ("Cristiano", "Ronaldo");4️⃣ FOURTH STEP 4️⃣
Now yes, we are going to start with the CRUD, specifically with the R, the reading.
Let's open our file read.php and later we will include the file with the connection, conn.php.
Next, we are going to create a table with html tags that reach only the header.
Subsequently, we will generate the rest of the rows in the table as we perform the query.
For this we use the function prepare of our connection and we pass in parentheses the SQL query we want to make.
Subsequently, we must execute this query and we will use a loop to extract all the information using the arrows -> as if it were an object.
<!DOCTYPE html>
<html>
<head>
<title>cRud - @fcoterroba.com</title>
</head>
<body>
<h1>cRud - Read - Lectura</h1>
<?php
include 'conn.php';
?>
<table>
<tr>
<th>ID</th>
<th>Nombre</th>
<th>Apellidos</th>
</tr>
<?php
$get_all = $dbh->prepare("SELECT * FROM main_data");
$get_all->execute();
while($fila = $get_all->fetch(PDO::FETCH_OBJ)){
echo "<tr><td>$fila->ID</td><td>$fila->nombre</td><td>$fila->apellidos</td></tr>";
}
?>
</table>
</body>
</html>If we add a little bit of styles we will have something like this:
5️⃣ FIFTH STEP 5️⃣
Let's now do the part of creating and adding data. The C in create.
For this part, we will open the file create.php and we add a form with empty action and POST method as well as two text entries, one for the first name and one for the last name. The ID is not added because it is something that would have to be executed automatically.
Once done, we are going to go below the header and in the event that we have received data by POST (both the first name and the last name), we add the connection file and carry out the query with its respective execution.
This will look more or less like this:
<!DOCTYPE html>
<html>
<head>
<title>Crud - @fcoterroba.com</title>
</head>
<body>
<h1>Crud - Create - Crear</h1>
<?php
if (!empty($_POST['name']) && !empty($_POST['apellidos'])) {
include 'conn.php';
$nombre = $_POST['name'];
$apellidos = $_POST['apellidos'];
$insert_data = $dbh->prepare("INSERT INTO main_data (nombre, apellidos) VALUES ('$nombre', '$apellidos')");
$insert_data->execute();
}
?>
<form action="" method="POST">
Escriba su nombre:
<br>
<input type="text" name="name" placeholder="Escriba su nombre" required>
<br>
Escriba sus apellidos:
<br>
<input type="text" name="apellidos" placeholder="Escriba sus apellidos" required>
<br>
<input type="submit" value="Crear usuario">
</form>
</body>
</html>Adding a little styles, a library of alerts and so on, we would be left with something like this:
6️⃣ SIXTH STEP 6️⃣
Penultimate step before finishing our CRUD. The U for update, update.
The first thing we are going to do is open the file update.php and show, in a similar way to read.php, all the values of the table by adding one more field that is a button for each record where we will then proceed to edit the values.
Once the table is made, we are going to make each button redirect to that same page but adding a GET with the ID of the record to modify.
When the page has been reloaded, we show the current values in name and surname, allowing the user to modify it to their liking and then a send button, sending data by POST to that same page and then we will do the query.
The code is something more or less like this:
<!DOCTYPE html>
<html>
<head>
<title>crUd - @fcoterroba.com</title>
</head>
<body>
<h1>crUd - Update - Actualizar</h1>
<?php
include 'conn.php';
if(!empty($_POST['nombre']) && !empty($_POST['apellidos'])){
$id = $_GET['id'];
$name = $_POST['nombre'];
$apellidos = $_POST['apellidos'];
$get_all = $dbh->prepare("UPDATE main_data SET nombre = '$name', apellidos = '$apellidos' WHERE ID = '$id'");
$get_all->execute();
header('Location: http://localhost/prueba_CRUD/update.php');
}else if (!empty($_GET['id'])) {
$id = $_GET['id'];
echo "<form action='' method='POST'>";
$get_all = $dbh->prepare("SELECT * FROM main_data WHERE ID = '$id'");
$get_all->execute();
while($fila = $get_all->fetch(PDO::FETCH_OBJ)){
echo "Nombre a modificar: <input type='text' value='$fila->nombre' name='nombre'><br>Apellidos a modificar: <input type='text' value='$fila->apellidos' name='apellidos'>";
}
echo "<br><input type='submit' value='Actualizar datos'></form>";
}else{
echo "<table>
<tr>
<th>ID</th>
<th>Nombre</th>
<th>Apellidos</th>
<th>Actualizar</th>
</tr>";
$get_all = $dbh->prepare("SELECT * FROM main_data");
$get_all->execute();
while($fila = $get_all->fetch(PDO::FETCH_OBJ)){
echo "<tr><td>$fila->ID</td><td>$fila->nombre</td><td>$fila->apellidos</td><td><button id='boton' onclick='prueba($fila->ID);'>Actualizar</button></td></tr>";
}
echo "</table>";
}
?>
<script type="text/javascript">
function prueba(id){
window.location.replace("http://localhost/prueba_CRUD/update.php?id="+id);
}
</script>
</body>
</html>Adding a little bit of styles, the complete process would be as follows:
7️⃣ SÉPTIMO PASO 7️⃣
For the last step and we are done with the post and our basic CRUD carried out with good practices, we are going to do the delete option, the Delete.
The first thing we are going to do is open the file delete.php and show, in a similar way to read.php, all the values of the table by adding one more field that is a button with an X to indicates the user that he'll delete this record.
It can be posed in many different ways but I am going to pose it similar to the previous step. The button will change the location to this same one but adding the ID via GET and the script will execute the query as soon as it receives it.
We would need to add a bit of confirmation in case the user accidentally gave it. I'll let you add it yourself!
The code would look more or less like this:
<!DOCTYPE html>
<html>
<head>
<title>cruD - @fcoterroba.com</title>
</head>
<body>
<?php
include 'conn.php';
if(!empty($_GET['id'])){
$id = $_GET['id'];
$get_all = $dbh->prepare("DELETE FROM main_data WHERE ID = '$id'");
$get_all->execute();
header('Location: http://localhost/prueba_CRUD/delete.php');
}
?>
<h1>cruD - Delete - Borrar</h1>
<table>
<tr>
<th>ID</th>
<th>Nombre</th>
<th>Apellidos</th>
<th>Borrar</th>
</tr>
<?php
$get_all = $dbh->prepare("SELECT * FROM main_data");
$get_all->execute();
while($fila = $get_all->fetch(PDO::FETCH_OBJ)){
echo "<tr><td>$fila->ID</td><td>$fila->nombre</td><td>$fila->apellidos</td><td><button onclick='borra($fila->ID);'>❌</button></td></tr>";
}
?>
</table>
<script type="text/javascript">
function borra(id){
window.location.replace("http://localhost/prueba_CRUD/delete.php?id="+id);
}
</script>
</body>
</html>Adding styles would look like this:
And little more to add guys, I hope you have learned a lot from this post! Knowing what it is and how to do a CRUD correctly is the foundation of any programmer, especially the backend.
Finally, remind you that if you like my content and want to contribute financially (since I don't earn anything from Adsense), you can send me the amount you want for Paypal. Any amount is well received! 🙂
I also hope you have a great week and see you here soon! A greeting and remember to follow me on the networks as Twitter, Facebook, Instagram, GitHub and LinkedIn. 🤟🏻
sources: IONOS.ES, Wikipedia, diego.com.es
English 
Spanish