Photo Database with HTML, PHP, and MySQL

Storing images in a database with other information is convenient when your application strongly relies on the database. For example, you'll need to synchronize images with other data if you are developing a program for a checkpoint system; in this case, along with personal data, you need to store a photo to identify the person.

There is a special MySQL data type for storing such content, BLOB. It accommodates large binary data: images, PDFs, or other multimedia files. 

An alternative to using the BLOB type is storing images inside the file system. But in this case, we make the application less portable and secure; there are at least two closely related modules: file system and database. Besides, when creating backups, you won't need to take snapshots of system directories; it will be enough to save MySQL dumps.

In this article, we will work on the photo database using a server with the LAMP stack installed (Linux, Apache2, MySQL, and PHP). You will need a user with sudo privileges to work with the server. 

As an example, in this article, we'll be working on the university's checkpoint system app.

Creating a database

First, let's create a database for our new project. You can do this through the console or any DBMS interface, such as phpMiniAdmin. We will use the first option. 

Connect to the server via SSH and log in to the MySQL server with superuser privileges:

sudo mysql -u root -p

Then, run the command to create the database. Let's call it access_control:

mysql> CREATE DATABASE access_control;

If you see an output like: Query OK, 1 row affected (0.01 sec), the database has been successfully created. Now we can start working with tables. 

But before that, for security purposes, we need to create a separate user who will work only with this database. For convenience, let's name it the same as the database:

mysql> CREATE USER 'access_control'@'localhost' IDENTIFIED BY 'Pas$w0rd!';

where passw0rd is your strong password. 

Now let's grant it permissions for all operations on the access_control database:

mysql> GRANT ALL PRIVILEGES ON access_control.* TO 'access_control'@'localhost';

After that, you need to clear the permissions table for MySQL to apply the changes:

mysql> FLUSH PRIVILEGES;

Now, we can start creating tables. We will need the students table, where we will store students' information, photos, and access rights. 

Let's log into MySQL with the newly created access_control user: 

mysql -u access_control -p

Switch to our database:

mysql> USE 'access_control';

And create the students table:

CREATE TABLE `students` (
    id INT INT PRIMARY KEY COMMENT "Student ID",
    name VARCHAR(200) NOT NULL COMMENT "Student's full name",
    access_rights ENUM ('full', 'extended', 'basic', 'denied') DEFAULT 'basic' COMMENT "Access Rights",
    userpic BLOB COMMENT 'Student Photo',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT "Enrollment Date"
) ENGINE = InnoDB;

Here:

• id is the table's primary key, numeric identifier of students. When adding new records, MySQL will independently generate identifiers in ascending order because we used the AUTO_INCREMENT keyword.

• name is the student's full name. We use VARCHAR data type with a limit of 200, because we won't need more than 200 characters for the name.

• access_rights is access rights for the student. The ENUM type assumes that one value from the list will be selected.

• userpic is a student's photo. The BLOB data type will store the data in binary format.

• created_at is the date of record creation. When we add a new record, MySQL will automatically add the current timestamp to this column.

We have chosen InnoDB to store the data, which will allow us to use a wide range of features, such as MySQL transactions.

-

Creating PHP scripts to fill in the database

The students table is ready, so now we can load data into it. Let's write a PHP script to register students in the system and add data to students.

First, let's create a configuration file config.php with database connection parameters.

<?php

$params = [
     'user' => 'control_access', 
     'name' => 'control_access', 
     'host' => 'localhost', 
     'pass => 'Pas$w0rd!'
];

$pdo = new PDO(
     'mysql:host=' . $params['host'] . '; dbname=' . $params['name'],
     $params['user'], 
     $params['pass']
);

To work with the database, we use the PDO driver. It passes connection parameters (database name, user name, password and the server's address where the database is located). 

Now, let's create a script to fill our table with test data.

<?php

// Connecting the configuration file 
require_once dirname(__FILE__) . '/config.php';
// Filling the array with data
$students = [
     [
        'name' => 'John Smith,
        'access_rights' => 'basic',
        'userpic' => file_get_contents(dirname(__FILE) . '/userpic/1.png')
    ],
    [
        'name' => 'Juan Hernandez',
        'access_rights' => 'full',
        'userpic' => file_get_contents(dirname(__FILE) . '/userpic/2.png')
    ],
    [
        'name' => 'Richard Miles',
        'access_rights' => 'extended',
        'userpic' => file_get_contents(dirname(__FILE) . '/userpic/3.png')
    ],
];

$sql_statement = 'INSERT INTO students (`name`, `access_rights`, `userpic`) '; $sql_statement .= 'VALUES (:name, :access_rights, :userpic);'

foreach($students as $student)
{
    $query = $pdo->prepare($sql_statement);
    $query->execute($student);
}

echo "done";

With this script, we connect to the database and then insert the necessary data. This script clearly shows how to add a picture to an SQL database; you just need to put its content in the appropriate field. To get the file's contents, we used the built-in php function file_get_contents. Then, we insert each array element into the database in a loop using the INSERT expression.

Displaying information

We have placed student information into the database; now, we need to display the data. We'll display everything in the table on a separate view.php page for convenience.

<?php

// Connect the config file 
require_once dirname(__FILE__) . '/config.php';
// Query all records from the students table 
$query= $pdo->prepare('
    SELECT
        *
    FROM
        students
');
$query->execute();


?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>MySQL BLOB test</title>
</head>
<body>
    <table>
        <thead>
            <tr>
                <td>Name</td>
                <td>Access Rights</td>
                <td>Photo</td>
            </tr>
        </thead>
        <tbody> 
        <?php

        while($row - $query->fetch(PDO::FETCH_ASSOC))
        {

            echo '<tr>';
                echo '<td>' . $row['name'] . '</td>";
                echo '<td>' . $row['access_rights'] . '</td>'; 
                echo '<td>';
                    echo '<image src="data:image/png;base64,' . base64_encode($row['userpic']) . '"';
                echo '</td>';
            echo '</tr>';
        }
        </tbody>
    </table>
</body>
</html>

We again use the connection to PDO inside the config.php file and then request a sample of all students using the SELECT * FROM students expression. 

We display all the obtained data in an HTML table.

To output the data stored in the BLOB object to the browser, we encoded the data into base64 format using the php built-in function and used the following syntax when specifying the image source in the img tag:

data:{type};base64, {data}

where {type} is the data type, in our case image/png, and {data} is the base64 data. 

Conclusion

In this article, using a student checkout system as an example, we have learned how to store images in a database using the BLOB data type. 

In addition, we learned how to insert media files into BLOB fields in MySQL and how to output them to the browser.