April 06, 2022

Upload & Save an Image to MySQL Database in PHP

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

Upload and Save Image to Database in PHP and MySQL
Download

Storing images in database can be useful when the images are not large in size and there are not too many images. Storing images provides a centralized storage of data and ensures better security as images are controlled by database security system. However saving images in database can increase the risk of corrupt data for images if image storing is not handled properly. This post demonstrates how to upload and save 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 file upload form.
  4. add-contact.php: The server-side script code to upload files to google drive.
  5. style.css: The CSS stylesheet for HTML page.
 

How to Save Image in Database (PHP)

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

 

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 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,
  `image` longblob,
  PRIMARY KEY (`id`)
);
 

Step 2: Database Constants

We need to connect to our database to store any data to 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 server. We process form submission on server side to save data to database along with image. Create an HTML page file with form to upload image file and show image from database on the same page.

  • Create a contact form with basic fields for First name, Last name, Email and File input field for Image.
  • The form must have enctype="multipart/form-data" attribute to enable file uploads.
  • Provide an action script where the form will be submitted and image will stored in database.
  • Below form show existing database records.
  • For each record image display base64 encoded image from 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 server-side with PHP script and convert image to base64 encoded image before saving it to database. Create a server-side script to handle form submission and save submitted data to database. Follow these steps to save image to database in base64 encoded format.

  • Create a connection to database and store it in a variable $db_connection.
  • Sanitize input post array.
  • Check if post array is not empty, then prepare a $data array for database record.
  • Check if there was image file uploaded without error.
  • Get the contents of uploaded file using file_get_contents() function and base64 encode the image using base64_encode() function.
  • Run a query to insert data to database table.
  • Redirect user back to index page where we show base64 encoded image from 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 entire page, the form that we created to upload an image, and the styles for table that displays records fetched from 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 database in PHP with other form data. This approach works well for not very large applications, for larger systems best approach will be to save images on a file system or cloud storage.

Home