Storing Binary Data in MySQL Databases

[tkv]

Overview: 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
add_binary_data.php

<?php

$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;
}
mysql_close();
?>
</font>
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

<?php

$db = mysql_connect("localhost", "root","");
mysql_select_db("mybuddy",$db);
//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>

<?php
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 "<TD>". ($rs[3]/1024) ." KB</TD>"; //filesize
echo "<TD>". $rs[4] ."</TD>"; //filetype
}

?>
</table>
<P></P>* Click on the file name to download the file
download_binary_data.php which downloads the actual data.

<?php
$db = mysql_connect("localhost", "root","");
mysql_select_db("mybuddy",$db);
//replace the above two string's with your database specific values

$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;

?>