Manufacture List

Insert Image File in MySQL

MySQL has a BLOB (binary large object) data type that can hold a large amount of binary data. The BLOB data type is perfect for storing image data in the database. In MySQL, four BLOB types are available – TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The LONGBLOB data type is perfect to store the image file data.

Create Database Table

To store the file content, a table is required in the database. The following SQL creates an images table with the LONGBLOB data type field in the MySQL database.

CREATE TABLE `images` ( `id` int(11) NOT NULL AUTO_INCREMENT,

`image` longblob NOT NULL,

`created` datetime NOT NULL DEFAULT current_timestamp(),

PRIMARY KEY (`id`) )

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Configuration (dbConfig.php)

The dbConfig.php file is used to connect and select the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.<?php
// Database configuration
$dbHost     = “localhost”;
$dbUsername = “root”;
$dbPassword = “root”;
$dbName     = “codexworld_db”;

// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

// Check connection
if ($db->connect_error) {
    die(“Connection failed: ” . $db->connect_error);
}

Image Upload Form

Create an HTML form with a file input field to select an image file for upload. Make sure the <form> tag contains the following attributes.

method=”post”
enctype=”multipart/form-data”

<label>Select Image File:</label>

</form>

Store Image File in Database (upload.php)

The upload.php file handles the image upload and database insertion process.

Check whether the user selects an image file to upload.
Retrieve the content of image file by the tmp_name using PHP file_get_contents() function.
Insert the binary content of the image in the database using PHP and MySQL.
Show the image uploading status to the user.

<?php
// Include the database configuration file
require_once ‘dbConfig.php’;

// If file upload form is submitted
$status = $statusMsg = ”;
if(isset($_POST[“submit”])){
    $status = ‘error’;
    if(!empty($_FILES[“image”][“name”])) {
        // Get file info
        $fileName = basename($_FILES[“image”][“name”]);
        $fileType = pathinfo($fileName, PATHINFO_EXTENSION);

        // Allow certain file formats
        $allowTypes = array(‘jpg’,’png’,’jpeg’,’gif’);
        if(in_array($fileType, $allowTypes)){
            $image = $_FILES[‘image’][‘tmp_name’];
            $imgContent = addslashes(file_get_contents($image));

            // Insert image content into database
            $insert = $db->query(“INSERT into images (image, created) VALUES (‘$imgContent’, NOW())”);

            if($insert){
                $status = ‘success’;
                $statusMsg = “File uploaded successfully.”;
            }else{
                $statusMsg = “File upload failed, please try again.”;
            }
        }else{
            $statusMsg = ‘Sorry, only JPG, JPEG, PNG, & GIF files are allowed to upload.’;
        }
    }else{
        $statusMsg = ‘Please select an image file to upload.’;
    }
}

// Display status message
echo $statusMsg;
?>