Uploading and Storing Image File in Database using PHP and MySQL

Server-side file upload can be easily accomplished using PHP. There are many ways to upload images to a server and display them on a webpage. In a dynamic web application, uploaded images are typically stored in a directory on the server and the file name is inserted into a database. Later, the images can be retrieved from the server based on the file name stored in the database and displayed on the web page.

While it is possible to upload images directly to a database, doing so will increase the size of the database and the load time of the web page. Therefore, it is generally recommended to upload images to the server and store the file names in the database. In this tutorial, we will demonstrate the entire process of uploading and storing an image file in a MySQL database using PHP.

CREATE TABLE `images` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`file_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`uploaded_on` datetime NOT NULL,
`status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE INDEX `file_name_UNIQUE` (`file_name` ASC),
INDEX `uploaded_on_idx` (`uploaded_on` ASC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This code creates a MySQL database table named “images” with four columns: “id”, “file_name”, “uploaded_on”, and “status”. The “id” column is an integer type with a maximum length of 11 and is set to auto-increment. The “file_name” column is a string type with a maximum length of 255 characters and is set to not allow null values. The “uploaded_on” column is a date and time type and is also set to not allow null values. The “status” column is an enumeration type with two possible values (‘1’ or ‘0’) and is set to not allow null values, with a default value of ‘1’.

Additionally, two indexes are created for the table. The “file_name_UNIQUE” index ensures that the “file_name” column contains unique values, while the “uploaded_on_idx” index creates an index on the “uploaded_on” column to optimize search queries that filter or sort by that column. The table is set to use the InnoDB storage engine, and the character set and collation are set to UTF-8 Unicode.

Create the file db.php

<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName = "coder";

// Create database connection
try {
$pdo = new PDO("mysql:host=$dbHost;dbname=$dbName", $dbUsername, $dbPassword);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
?>

In this code, we have created a new PDO object to establish the database connection. The setAttribute() method is used to set the error mode to ERRMODE_EXCEPTION, which throws exceptions in case of errors instead of just warnings. This can make error handling easier and more consistent.

Also note that the error handling is done using a try…catch block, instead of checking the connection error using connect_error property, as PDO uses exceptions to handle errors.

<form action="upload.php" method="post" enctype="multipart/form-data" onsubmit="return validateForm()">
Select Image File to Upload (max 10MB, JPG/PNG):
<input type="file" name="file" accept="image/jpeg,image/png" required>
<input type="submit" name="submit" value="Upload">
</form>

<script>
function validateForm() {
const fileInput = document.querySelector('input[name="file"]');
const file = fileInput.files[0];
const maxSize = 10 * 1024 * 1024; // 10MB
const allowedTypes = ['image/jpeg', 'image/png'];

if (!file) {
alert("Please select a file to upload.");
return false;
}

if (file.size > maxSize) {
alert("File size is too large. Max size is 10MB.");
return false;
}

if (!allowedTypes.includes(file.type)) {
alert("File type is not allowed. Only JPG/PNG files are allowed.");
return false;
}

return true;
}
</script>

This code is a HTML form that allows a user to upload an image file to the server. The form includes an input field that allows the user to select a file to upload, a submit button, and a client-side validation function that runs when the form is submitted.

The form element includes several attributes that control how the form behaves when submitted. The action attribute specifies the URL of the server-side script that will handle the file upload, the method attribute specifies the HTTP method to use when submitting the form, and the enctype attribute specifies the encoding type used to send the form data to the server.

The input element with the type=”file” attribute is used to create a file selection control that allows the user to select an image file from their local device. The accept attribute specifies the file types that are allowed to be uploaded, in this case, only image/jpeg and image/png. The required attribute ensures that the user selects a file before submitting the form.

The script element contains a client-side validation function that runs when the form is submitted. The querySelector() method is used to retrieve the selected file from the input field, and the function checks the file size and type against the allowed limits specified in the maxSize and allowedTypes variables. If the file does not meet the requirements, the function displays an alert message and returns false to prevent the form from being submitted. If the file meets the requirements, the function returns true to allow the form to be submitted to the server for processing.

<?php
// Include the database configuration file
require_once 'db.php';

if (isset($_POST["submit"]) && !empty($_FILES["file"]["name"])) {
// File upload path
$targetDir = "uploads/";
$fileName = basename($_FILES["file"]["name"]);
$targetFilePath = $targetDir . $fileName;
$fileType = pathinfo($targetFilePath, PATHINFO_EXTENSION);

// Allow certain file formats
$allowTypes = array('jpg','png','jpeg','gif','pdf');
if (in_array($fileType, $allowTypes)) {
// Upload file to server
if (move_uploaded_file($_FILES["file"]["tmp_name"], $targetFilePath)) {
// Insert image file name into database
$stmt = $pdo->prepare("INSERT into images (file_name, uploaded_on) VALUES (?, NOW())");
$stmt->execute([$fileName]);

if ($stmt->rowCount()) {
$statusMsg = "The file ".$fileName. " has been uploaded successfully.";
} else {
$statusMsg = "File upload failed, please try again.";
}
} else {
$statusMsg = "Sorry, there was an error uploading your file.";
}
} else {
$statusMsg = 'Sorry, only JPG, JPEG, PNG, GIF, & PDF files are allowed to upload.';
}
} else {
$statusMsg = 'Please select a file to upload.';
}

// Display status message
echo $statusMsg;
?>

This code uploads an image file to the server and inserts the image file name into a MySQL database table. It begins by including the database configuration file. It then checks if the form has been submitted and if a file has been selected for upload. If yes, it defines the file upload path, file name, and file type. It also defines an array of allowed file types.

If the selected file type is allowed, the code uploads the file to the server and inserts the image file name into the images table in the database. To do this, it prepares an SQL statement with a placeholder for the file name and uses the bind_param() function to bind the file name to the statement. It then executes the statement and checks if the insertion was successful. If successful, it displays a success message, otherwise, it displays an error message.

If the selected file type is not allowed or no file has been selected for upload, the code displays an appropriate error message. Finally, the code displays the status message.

<?php
// Include the database configuration file
require_once 'db.php';

// Get images from the database
$stmt = $pdo->query("SELECT file_name FROM images ORDER BY uploaded_on DESC");

// Output images or message if none exist
if($stmt->rowCount() > 0){
while($row = $stmt->fetch()){
$imageURL = 'uploads/'.$row["file_name"];
echo "<img src=\"$imageURL\" alt=\"\" />\n";
}
} else {
echo "<p>No images found...</p>\n";
}
?>

This PHP code retrieves image file names from the database table named “images” ordered by their upload time in descending order. It uses PDO (PHP Data Objects) to query the database. The retrieved file names are used to construct image URLs and displayed in HTML using the “img” tag. If there are no images found in the database, a message is displayed stating so.

Download Source Code