Download File From MySQL Blob Using PHP

Download File

Here in this example I will show you how to download file from MySQL database using PHP language. The file data is stored as a blob data type in the MySQL server.

The file content itself was stored into MySQL database, so you haven’t stored the file into a physical location of the system and you want to download the content from the database as a file.

Related Posts:

Prerequisites

PHP 8.2.7, MySQL 8.1.0

MySQL Database

The table structure in the MySQL database server is given as follows but I am not going to show you here what data are there in this table. You can download it from the source code.

CREATE DATABASE IF NOT EXISTS `roytuts`;
USE `roytuts`;

CREATE TABLE IF NOT EXISTS `file` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content` mediumblob,
  `size` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Notice the column content is of type mediumblob, you may choose the type (tinyblob, blob, mediumblob, longblob) according to your application’s requirement.

Project Directory

The name of the project’s root directory is php-mysql-download-file. I may not mention the project’s root folder’s name but the file I will be creating with respect to the project’s root folder.

Database Connectivity

Now I am moving to the file download example and retrieve the stored file from MySQL database using PHP.

The first step is to establish the database connection to MySQL server using PHP scripting language.

The following database connectivity code is written into the file db_config.php:

<?php  
// Database connection details
$db_host     = "localhost";
$db_username = "root";
$db_password = "root";
$db_name     = "roytuts";
  
// Database connection
$db = new mysqli($db_host, $db_username, $db_password, $db_name);
  
// Verify connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

File Download

Here I am going to write PHP code for download a file using web page from the MySQL database. The page will show you files with Download option and you can download file by clicking on the Download link as shown in the below image.

file download from mysql using php

The source code for downloading a file from database is given below:

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

$id = $_GET['id'];

$query = "SELECT * FROM file WHERE id = $id";
$result = $db->query($query);

list($id, $name, $content, $size, $type) = mysqli_fetch_array($result);

header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header("Content-Transfer-Encoding: binary");
header('Pragma: public');
header("Content-length: $size");
header("Content-type: $type");
header("Content-Disposition: attachment; filename=$name");
echo $content;

Source Code

Download

Leave a Reply

Your email address will not be published. Required fields are marked *