Storing Binary Data in MySQL Databases with PHP September 17, 2010
Posted by Tournas Dimitrios in Mysql, PHP.trackback
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)
<HTML> <TITLE>Upload your files</TITLE> <BODY> <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> <TR> <TD>File Description: </TD> <TD><TEXTAREA NAME="file_description" ROWS="3" COLS="50"></TEXTAREA></TD> </TR> <TR> <TD>File: </TD> <TD><INPUT TYPE="file" NAME="binary_File"></TD> </TR> <TR> <TD COLSPAN="2"><INPUT TYPE="submit" VALUE="Upload File"></TD> </TR> </TABLE> </FORM> </font> </BODY> </HTML>
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;
}
mysql_close();
?>
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 "". ($rs[3]/1024) ." KB"; //filesize
echo "". $rs[4] .""; //filetype
}
?>
</table>
<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","");
mysql_select_db("mybuddy",$db);
//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;
Linux >>> 
Tournas Dimitrios 
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.
@Robert
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 …..
Thanks.