Although there are millions of tutorials about MySQL and PHP, I decided to make mine but in a very very simple way. Before you start, you must have a basic knowledge in MySQL.
It is worth mentioning that we will not see good practices of databases, relationships, etc. We will use the tables as they are, without looking at normalization, or things like that.
Note: this tutorial uses PDO, but remember that we can also use mysqli functions. Personally I recommend PDO, because it is object oriented. However, I hope to write a tutorial about mysqli in the future.
As you can see we will use a database called mysql_tutorial, and we will work with a table called person.
You can paste the file in your MySQL console or in phpmyadmin. For this example we will only save basic information.
We have defined the table, but not the connection. We need to define a file with the user, database and password. Mine looks like this:
<?php/*
PHP and MySQL tutorial
https://parzibyte.me/blog/en
*/$password ="";
$user ="root";
$databaseName ="mysql_tutorial";
try{
$database =newPDO('mysql:host=localhost;dbname='. $databaseName, $user, $password);
}catch(Exception $e){
echo"Error connecting to db: ". $e->getMessage();
}
?>
Remember to change your user and password according to your system. Pay attention to this file because we will use it to interact with the MySQL database.
The form action is add.php where we will process the data and save into MySQL table. Please check that we have marked the values as required, so the browser validates them.
Don’t forget to use the post method so the data is sent in the request body.
Now it’s time to use PHP to save the form. Please see the code:
<?php# If one of these fields are not present, exit
if (!isset($_POST["first_name"]) ||!isset($_POST["last_name"]) ||!isset($_POST["gender"])) {
exit();
}
# If everything is OK this code is executed
include_once"database.php";
$first_name = $_POST["first_name"];
$last_name = $_POST["last_name"];
$gender = $_POST["gender"];
/*
When we include the "database.php" file, all of its variables are present
in the current scope, so we can access "$database" defined in the file
*/$statement = $database->prepare("INSERT INTO person(first_name, last_name, gender) VALUES (?, ?, ?);");
$result = $statement->execute([$first_name, $last_name, $gender]); # Pasar en el mismo orden de los ?
#execute returns true or false depending on success
if ($result ===true) {
echo"Inserted successfully";
} else {
echo"Something went wrong";
}
First we make sure the form has data by using isset. If one of the fields are not present, we call exit so the script stops.
Then we include the database.php file, and when we include it, we can access to its variables (for example $database). Then we prepare a statement and use placeholders ? to prevent sql injections.
Please note that here we are using the insert statement.
The data is really inserted when we execute the statement, passing the true data in the same order as the placeholders.
We save the result and show a result depending on its value. If everything is ok you should see a “Inserted successfully” message, if not, check your credentials and code.
If you pay attention, to show rows we use <tr> and to show cells inside rows we use <td>. If you see this file in the browser it looks like this:
Now we have to find a way to render this table but using PHP.
First we have to make a query to bring the data from the table; by using PDO we can get it as an array. Then we only have to iterate the array and in each step draw a <tr> and the <td>s.
We include the database.php file again, then we make a query and finally to get the array we call fetchAll. We are using PDO::FETCH_OBJ to bring the data as objects.
Then in line 24 we make a loop and iterate all the data. In each step we draw a new row.
Please note that we added two links: one for edit and one for delete; and we are passing the id in the URL.
If you execute this code, depending on the data you have, you should see something like this:
Form to edit
When we click edit, we see another form that is similar to the form to insert data but with the inputs filled:
<?phpif (!isset($_GET["id"])) {
exit();
}
$id = $_GET["id"];
include_once"database.php";
$statement = $database->prepare("SELECT * FROM person WHERE id = ?;");
$statement->execute([$id]);
$person = $statement->fetch(PDO::FETCH_OBJ);
if ($person ===false) {
#not found
echo"Person not found!";
exit();
}
#If the person exists, this code is executed
?><!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Edit person</title>
</head>
<body>
<form method="post" action="update.php">
<!-- Put the id hidden in the form so we can use it later -->
<input type="hidden" name="id" value="<?php echo $person->id; ?>">
<label for="nombre">Name:</label>
<br>
<input value="<?php echo $person->first_name ?>" name="first_name" required type="text" id="first_name" placeholder="First name">
<br><br>
<label for="last_name">Last name:</label>
<br>
<input value="<?php echo $person->last_name ?>" name="last_name" required type="text" id="last_name" placeholder="Last name">
<br><br>
<label for="gender">Gender</label>
<select name="gender" required name="gender" id="gender">
<!--
Para seleccionar una opción con defecto, se debe poner el atributo selected.
Usamos el operador ternario para que, si es esa opción, marquemos la opción seleccionada
-->
<option value="">--Please select--</option>
<option <?php echo $person->gender === 'M' ? "selected='selected'" : "" ?> value="M">Male</option>
<option <?php echo $person->gender === 'F' ? "selected='selected'" : "" ?> value="F">Female</option>
</select>
<br><br><input type="submit" value="Save">
</form>
</body>
</html>
First we make a query to get one record based on the id; we are using the select statement combined with where. As we ara passing the variable in the URL we get it from $_GET.
If the person does not exist, we stop the script.
Then we show the form again but fill the input with the value attribute. Pay attention to the hidden input, we will use it to update the record later (when we process the form).
Now the form action is update.php. If you list your data and click on edit, you should see a form like this:
This file is like the add.php but now we execute an update instead of an insert. The code looks like this:
<?php#If one of these are not present, exit
if (
!isset($_POST["first_name"]) ||!isset($_POST["last_name"]) ||!isset($_POST["gender"]) ||!isset($_POST["id"])
) {
exit();
}
#If everything is OK, this code is executed
include_once"database.php";
$id = $_POST["id"];
$first_name = $_POST["first_name"];
$last_name = $_POST["last_name"];
$gender = $_POST["gender"];
$statement = $database->prepare("UPDATE person SET first_name = ?, last_name = ?, gender = ? WHERE id = ?;");
$result = $statement->execute([$first_name, $last_name, $gender, $id]); # Pass data in the same order as placeholders
if ($result ===true) {
echo"Saved";
} else {
echo"Something went wrong";
}
We include the database file again, and prepare a statement using placeholders to prevent sql injections. Then we execute the update and show the results.
If you edit a record you should see a “saved” message; if not, check your code!
You can download and see the full source code on my GitHub.
Si el post ha sido de tu agrado te invito a que me sigas para saber cuando haya escrito un nuevo post, haya
actualizado algún sistema o publicado un nuevo software.
Facebook
| X
| Instagram
| Telegram |
También estoy a tus órdenes para cualquier contratación en mi página de contacto