April 06, 2022

Upload & Save an Image to MySQL Database in PHP

Normally, when files are uploaded to a web application, they are stored on disk and the path to the file is saved in the database. MySQL also supports a field type longblob, which can be used to store images in the database. Learn in this post, how to save an image to a MySQL database in PHP.

Upload and Save Image to Database in PHP and MySQL
Download

Storing images in a database can be useful when the images are not large and there are not too many of them. Storing images provides a centralized storage of them and ensures better security as images are controlled by a database security system. However, saving images in a database can increase the risk of corrupt data for images if image storage is not handled properly. This post demonstrates how to upload and save an image to MySQL in PHP applications. Files we are going to create for this post:

  1. contacts.sql: The HTML page with file upload form
  2. constants.php: The HTML page with file upload form.
  3. index.php: The HTML page with a file upload form.
  4. add-contact.php: The server-side script code to save form data including the image.
  5. style.css: The CSS stylesheet for the HTML page.
 

How to Save an Image in a Database (PHP)

Storing images in a database in a PHP application can be achieved in a few simple steps. The steps basically involve having a longtext or longblob field in a database table, an interface to upload images along with other form data and a service-side script to handle form submission and storing images. In my opinion, the best way to store images in a database is using a longblob field in a table. Steps to save images to the database are as follows:

 

Step 1: Database Table with `LONGBLOB` Column

We need a database table to store images, so first we need to import the table. We are using a sample contacts table for demo, with a longblob field for image

contacts.sql

CREATE TABLE IF NOT EXISTS `contacts` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(150) NOT NULL UNIQUE,
  `image` longblob,
  PRIMARY KEY (`id`)
);
 

Step 2: Database Constants

We need to connect to our database to store any data in the database. So we create a PHP file to define constants for our database connection in PHP.

constants.php 

<?php
define('DB_HOST', 'DATABASE_HOST');  // Your database host
define('DB_NAME', 'DATABASE_NAME');  // Your database name
define('DB_USER', 'DATABASE_USERNAME');  // Username for database
define('DB_PASSWORD', 'DATABASE_PASSWORD'); // Password for database
 

Step 3: User Interface to Upload & Display Images from Database

We need an interface that allows us to upload images to the server. We process form submission on the server side to save data to the database along with the image. Create an HTML page file with a form to upload an image file and show an image from the database on the same page.

  • Create a contact form with basic fields for first name, last name, email and a file input field for image.
  • The form must have the enctype="multipart/form-data" attribute to enable file uploads.
  • Provide an action script where the form will be submitted and the image will be stored in the database.
  • Below the form, show existing database records.
  • For each record with an image, display a base64 encoded image from the database.

index.php

<?php
include_once 'constants.php';

$db_connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die(mysqli_connect_error());

$query = mysqli_query($db_connection, 'SELECT * FROM contacts');
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Upload and Save Image to MySQL Database in PHP - Demo</title>
        <meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
        <meta content="width=device-width, initial-scale=1, maximum-scale=1" name="viewport"/>
        <link rel="stylesheet" href="css/style.css"/>
    </head>
    <body>
        <section class="section py-4">
            <div class="container">
                <div class="mb-4">
                    <form action="add-contact.php" method="POST" enctype="multipart/form-data" class="contact-form">
                        <div class="mb-4">
                            <label class="inline-block mb-1">Full name: <span class="text-red">*</span></label>
                            <input type="text" name="first_name" class="form-control" required="required"
                                   placeholder="First name..."/>
                        </div>
        
                        <div class="mb-4">
                            <label class="inline-block mb-1">Full name: <span class="text-red">*</span></label>
                            <input type="text" name="last_name" class="form-control" required="required"
                                   placeholder="Last name..."/>
                        </div>

                        <div class="mb-4">
                            <label class="inline-block mb-1">Email address: <span class="text-red">*</span></label>
                            <input type="email" name="email" class="form-control" required="required"
                                   placeholder="Email address..."/>
                        </div>
                        <div class="mb-4">
                            <label class="inline-block mb-1">Image</label>
                            <input type="file" name="image" class="form-control"/>
                        </div>

                        <div class="mb-4">
                            <button type="submit" class="btn btn-blue">Send</button>
                        </div>
                    </form>
                </div>

                <div class="mb-4">
                    <?php
                    while ($contact = mysqli_fetch_assoc($query)) { ?>
                        <div class="contact-row">
                            <div class="contact-image">
                                <?php if (!empty($contact['image'])) { ?>
                                    <img src="data:image/jpg;base64,<?= $contact['image']; ?>" class="image-responsive"
                                         width="100" height="100" loading="lazy" alt=""/>
                                <?php } ?>
                            </div>
                            <div class="contact-details">
                                <div><?= $contact['first_name']; ?> <?= $contact['last_name']; ?></div>
                                <div><?= $contact['email']; ?></div>
                            </div>
                        </div>
                        <?php
                    }
                    ?>
                </div>
            </div>
        </section>
    </body>
</html>
 

Step 4: Save an Image in MySQL Database in PHP (Server-Side)

We need to handle the form on the server-side with a PHP script and convert the image to a base64 encoded image before saving it to the database. Create a server-side script to handle form submission and save submitted data to the database. Follow these steps to save an image to a database in base64 encoded format.

  • Create a connection to the database and store it in a $db_connection variable.
  • Sanitize input post array.
  • Check if the post array is not empty, then prepare a $data array for the database record.
  • Check if an image file was uploaded without error.
  • Get the contents of the uploaded file using the file_get_contents() function and base64 encode the image using the base64_encode() function.
  • Run a query to insert data into the database table.
  • Redirect the user back to the index page, where we show a base64 encoded image from the database.

add-contact.php

<?php
include_once 'constants.php';

$db_connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die(mysqli_connect_error());

// Sanitize post array
$post = filter_input_array(INPUT_POST);

if (!empty($post)) {
    $data = [];
    $data['first_name'] = trim($post['first_name']);
    $data['last_name'] = trim($post['last_name']);
    $data['email'] = trim($post['email']);

    // If there was no error with image add it to array
    if (isset($_FILES['image']) && $_FILES['image']['error'] === UPLOAD_ERR_OK) {
        $data['image'] = base64_encode(file_get_contents($_FILES['image']['tmp_name']));
    }

    $sql = sprintf('INSERT INTO contacts (%s) VALUES(%s)',
        implode(',', array_keys($data)),
        implode(',', array_fill(0, count($data), '?')));

    $stmt = mysqli_prepare($db_connection, $sql);

    mysqli_stmt_bind_param($stmt, str_repeat('s', count($data)), ...array_values($data));

    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);

    header('Location: ' . filter_var($_SERVER['HTTP_REFERER'], FILTER_SANITIZE_URL));
    exit;
}
 

Step 5: Add CSS Styles for File Upload User Interface

Add some CSS styles for the entire page, the form that we created to upload an image, and the styles for the table that displays records fetched from the database.

style.css 

* {
    box-sizing: border-box;
}
html,body {
    margin: 0;
    padding: 0;
}
body {
    background-color: #f6f6f6;
    font-family: "Segoe UI", "Roboto", "Helvetica", sans-serif;
    font-size: 15px;
    font-weight: normal;
    font-style: normal;
}
.container {
    max-width: 1024px;
    margin: 0 auto;
    padding-left: 15px;
    padding-right: 15px;
}
.inline-block {
    display: inline-block;
}
.py-4 {
    padding-top: 1rem;
    padding-bottom: 1rem;
}
.my-1, .mb-1 {
    margin-bottom: .25rem;
}
.my-4, .mb-4 {
    margin-bottom: 1rem;
}
.form-control {
    display: block;
    width: 100%;
    padding: .375rem .75rem;
    font-size: 1rem;
    font-weight: 400;
    line-height: 1.5;
    color: #1c2528;
    background-color: #ffffff;
    background-clip: padding-box;
    border: 1px solid #d1d2d3;
    -webkit-appearance: none;
    -moz-appearance: none;
    appearance: none;
}
.btn {
    display: inline-block;
    padding: .375rem .75rem;
    cursor: pointer;
    font-size: inherit;
}
.btn-blue {
    background-color: #0369a1;
    border: 1px solid #0369a1;
    color: #ffffff;
}
.btn-blue:hover {
    background-color: #005D95;
}
.contact-row {
    display: flex;
    background: #fff;
    color: #333;
    padding: 10px;
    margin-bottom: 15px;
    box-shadow: 0 0 2px rgba(100,100,100,0.5);
}
.contact-image {
    flex: 100px 0 0;
    overflow:hidden;
}
.contact-image img {
    max-width: 100%;
    height: auto;
    display: block;
}
.contact-details {
    padding-left: 10px;
    flex: 1 0 100px;
}

We now have a fully working interface to upload and save an image to the database in PHP with other form data. This approach works well for not very large applications, for larger systems the best approach will be to save images on a file system or cloud storage.

Home