PHP & MySQL tutorial using PDO

Source code of PHP file connection

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.

Source code of PHP file connection

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.

The database

We have to create a database, inside that database we can create tables. This file has the database and table definitions:

CREATE DATABASE IF NOT EXISTS mysql_tutorial;
USE mysql_tutorial;
CREATE TABLE person(
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 first_name VARCHAR(255) NOT NULL,
 last_name VARCHAR(255) NOT NULL,
 gender CHAR NOT NULL,
 PRIMARY KEY(id)
);

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 = new PDO('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.

Form to insert

We have to define a form to ask user for data, for this we only need HTML:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <title>Add new person</title>
    </head>
    <body>
        <form method="post" action="add.php">
            <label for="first_name">Name:</label>
            <br>
            <input name="first_name" required type="text" id="first_name"
                placeholder="First name">
            <br><br>
            <label for="last_name">Last name:</label>
            <br>
            <input 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">
                <option value="">--Please select--</option>
                <option value="M">Male</option>
                <option value="F">Female</option>
            </select>
            <br><br><input type="submit" value="Save">
        </form>
    </body>
</html>

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.

So far our form looks like this:

Now let’s take a look at the add.php file.

Insert data into MySQL table

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.

Select data, but first, a static table

We can use a table to show the data from MySQL, but first we have to know how to render a static table. We can draw a basic HTML table like this:

<!DOCTYPE html>
<html lang="en">
 <head>
  <meta charset="UTF-8">
  <title>Table example</title>
  <style>
   table, th, td {
    border: 1px solid black;
   }
  </style> 
 </head>
 <body>
  <table>
   <thead>
    <tr>
     <th>ID</th>
     <th>First name</th>
     <th>Last name</th>
     <th>Gender</th>
    </tr>
   </thead>
   <tbody>
    <tr>
     <td>1</td>
     <td>John</td>
     <td>Doe</td>
     <td>M</td>
    </tr>
    <tr>
     <td>2</td>
     <td>María José</td>
     <td>Ejemplo</td>
     <td>F</td>
    </tr>
    <tr>
     <td>3</td>
     <td>Pedro</td>
     <td>Ramírez</td>
     <td>M</td>
    </tr>
   </tbody>
  </table>
 </body>
</html>

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.

Show MySQL data 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.

Let’s see the code:

<?php
include_once "database.php";
$statement = $database->query("SELECT * FROM person;");
$people = $statement->fetchAll(PDO::FETCH_OBJ);
?>
<!--We can interpolate HTML and PHP-->
<!DOCTYPE html>
<html lang="en">
<head>
 <meta charset="UTF-8">
 <title>People list</title>
 <style>
 table, th, td {
     border: 1px solid black;
 }
 </style>
</head>
<body>
 <table>
  <thead>
   <tr>
    <th>ID</th>
    <th>Fist Name</th>
    <th>Last name</th>
    <th>Gender</th>
    <th>Edit</th>
    <th>Delete</th>
   </tr>
  </thead>
  <tbody>
   <!--
    we use foreach to repeat <tr>
     (look at static_table.html)
   -->
   <?php foreach ($people as $person) {?>
   <tr>
    <td><?php echo $person->id ?></td>
    <td><?php echo $person->first_name ?></td>
    <td><?php echo $person->last_name ?></td>
    <td><?php echo $person->gender ?></td>
    <td><a href="<?php echo "edit.php?id=" . $person->id ?>">Edit</a></td>
    <td><a href="<?php echo "delete.php?id=" . $person->id ?>">Delete</a></td>
   </tr>
   <?php }?>
  </tbody>
 </table>
</body>
</html>

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:

<?php
if (!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:

Update data

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!

Delete data

To finish this MySQL and PHP tutorial we have to see how to delete; in this case, for the sake of simplicity we are using the URL to delete a record.

The code is really simple, we execute a DELETE statement with WHERE; please note that we are using placeholders to prevent sql injections.

<?php
if (!isset($_GET["id"])) {
    exit();
}

$id = $_GET["id"];
include_once "database.php";
$statement = $database->prepare("DELETE FROM person WHERE id = ?;");
$result = $statement->execute([$id]);
if ($result === true) {
    echo "Deleted";
} else {
    echo "Something went wrong";
}

We get the id from $_GET and then execute a statement, that’s all.

Conclusion

You can download and see the full source code on my GitHub.

parzibyte

Freelancer programmer ready to work with you. Web, mobile and desktop applications. PHP, Java, Go, Python, JavaScript, Kotlin and more :) https://parzibyte.me/