jump to navigation

Storing Binary Data in MySQL Databases with PHP September 17, 2010

Posted by Tournas Dimitrios in Mysql, PHP.

A lot of people are curious to know how to add binary data such a Zip files, images etc in a MySQL Database, this articles shows you how to store binary data in a MySQL database and then how to reterive them back.Binary data can be stored in a MySQL database in a BLOB field.

“A BLOB is a binary large object that can hold a variable amount of data.” This essentially means that BLOB is a datatype that can hold binary content, and we can use it to store files.

In order to understand the concept I will work with an example we will first create a table, then I will show you how to add (Binary Data) in it and then reterive it back.

Creating a Sample Table: We will name the table as ‘binary_data_files’ which will hold our Binary Data.

CREATE TABLE binary_data_files

(file_id tinyint(3) unsigned NOT NULL auto_increment,

bin_data mediumblob NOT NULL,

description tinytext NOT NULL,

filename varchar(50) NOT NULL,

filesize varchar(50) NOT NULL,

filetype varchar(50) NOT NULL,

PRIMARY KEY (file_id)); 

Now that we have a table I made a HTML Form, from where we can upload Binary Data (binary_form.htm)

<TITLE>Upload your files</TITLE>
<font face=verdana size=2>
<FORM METHOD="post" ACTION="add_binary_data.php" ENCTYPE="multipart/form-data">
 <INPUT TYPE="hidden" NAME="MAX_FILE_SIZE" VALUE="500000">
 Set the size of MAX_FILE_SIZE I have set it to approx 500KB means when we add
 binary data add.php will check if the binaryfile size is > MAX_FILE_SIZE it wont allow
 this is just a precaution so that users do not upload very large files.
 <INPUT TYPE="hidden" NAME="action" VALUE="upload">
 <TABLE BORDER="1" cellspacing=0 cellpadding=0>
   <TD>File Description: </TD>

   <TD><TEXTAREA NAME="file_description" ROWS="3" COLS="50"></TEXTAREA></TD>
   <TD>File: </TD>
   <TD><INPUT TYPE="file" NAME="binary_File"></TD>
   <TD COLSPAN="2"><INPUT TYPE="submit" VALUE="Upload File"></TD>


add_binary_data.php :

 <? p h p

$db = mysql_connect("localhost", "root","");

mysql_select_db("mybuddy",$db); //connects to our mybuddy database

//replace the above two string's with your database specific values

if (isset($binary_File) && $binary_File != "none")


  $data = addslashes(fread(fopen($binary_File, "r"), filesize($binary_File)));

  $strDescription = addslashes($file_description);

  $sql = "INSERT INTO binary_data_files ";

  $sql .= "(description, bin_data, filename, filesize, filetype) ";

  $sql .= "VALUES ('$strDescription', '$data', ";

  $sql .= "'$binary_File_name', '$binary_File_size', '$binary_File_type')";

  $result = mysql_query($sql, $db);

 echo "<font face=verdana size=2>The file was successfully added to our database.<P>";

 echo "<P><B>File Name: </B>". $binary_File_name;

echo "<BR><B>File Size: </B>". $binary_File_size ." bytes  (approx ". ($binary_File_size/1024) ." KB)";

echo "<P><B>File Type: </B>". $binary_File_type;




Okay now that we have added data to the database, we will write a programm that shows the data in a table and another programme that retrives it.

We have two programms the first view_data.php and the second download_binary_data.php the first programme view_data.php is a very simple programme that lists the details of the entire table where our data is stored in a neat table.

view_data.php :


$db = mysql_connect("localhost", "root","");


//replace the above two string's with your database specific values

$sql="SELECT file_id, description, filename, filesize, ";

$sql .="filetype FROM binary_data_files";

$sql_results = mysql_query($sql,$db);


<TABLE BORDER=1 cellspacing=0 cellpadding=5>

<TR><TD>File Name</TD><TD>Description</TD>

<TD>File size</TD><TD>File type</TD></TR>


while ($rs = mysql_fetch_array($sql_results))


	echo "<TR><TD><a href=download_binary_data.php?id=". $rs[0] .">" . $rs[2] ."</a></TD>";

	echo "<TD>". $rs[1] ."</TD>"; //description

	echo "". ($rs[3]/1024) ." KB"; //filesize

	echo "". $rs[4] .""; //filetype




<P></P>* Click on the file name to download the file

download_binary_data.php which downloads the actual data.

$db = mysql_connect("localhost", "root","");
//replace the above two string's with your database specific values
// Let's extract the id from the query string 
if (isset($_GET["id"] )) {
  $id = mysql_real_escape_string($_GET["id"]) ;  
} else {
    die('Unknown reference') ; 

$sql = "SELECT bin_data, filetype, filename, ";
$sql .="filesize FROM binary_data_files WHERE file_id=$id";
  $result = @mysql_query($sql, $db);
  $data = @mysql_result($result, 0, "bin_data");
  $name = @mysql_result($result, 0, "filename");
  $size = @mysql_result($result, 0, "filesize");
  $type = @mysql_result($result, 0, "filetype");
  header("Content-type: $type");
  header("Content-length: $size");
  header("Content-Disposition: attachment; filename=$name");
  header("Content-Description: PHP Generated Data");
  echo $data;


1. Robert Anthonie Soriano - April 29, 2013

Hello. in the download_binary_data.php … the $id is not present anywhere, i mean not declared, can you please help me? i really need this. thanks.

tournasdimitrios1 - April 29, 2013

Thanks for visiting my Blog .
At first , I would emphasize that the code was written three years ago . Although the code is still working , the driver (MySQL) is deprecated and should be replaced by the new driver (MySQLi) . Also , there is a lot of criticism in the PHP-developer community , its against this practice (using MySQL for storing binary data) . Nowadays , better solutions are available to gain benefits by storing binary data into a database . For instance , using noSQL databases are more suited for such tasks (my preferences are MongoDB , CouchDB and Redis) . Sometimes though , we have no alternative solution and are forced to use MySQL (as many shared hosts support only MySQL ) . Anyway , sorry for this long introduction , let’s go straight to the “meat” .
During the process of storing a new item into MySQL’s table , it’s given an unique number (file_id tinyint(3) unsigned NOT NULL auto_increment) . This “id” is used as a reference to retrieve a specific item from the database table . In my examples , view_data.php is used to list all items with its corresponding ‘Id” , and then , that “id” is used to build a query-string that makes a request to download_binary_data.php (it extraxts the “id” from the URL and finaly knows which item to retrieve from the database) . In simple words , you can’t directly access the “download_binary_data.php” script (just because , you don’t know the “id” yet) . You should first access the “view_data.php ” .
Hope this helps …..

2. Mehmet - May 8, 2014


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s