How to Store Images Directly in the SQL Database

If you want to store binary data like images and HTML files directly in your MySQL database, this Article is for you!

Overview:

* Create a new database on your SQL Server
* A sample php3 script you can use to store data in your database
* A sample php3 script with which you can access the stored data

Create a new database on your SQL Server

First of all, you have to create a new database on your SQL server in which your script will store the binary data.

For my example, I use the following structure. To create this database, you have to do the following steps:

* login to the MySQL monitor
* enter the command “create database binary_data;”
* enter the command “use binary_data;”
* copy and paste the following instructions (the table structure) to the monitor
* the database table should be created

CREATE TABLE binary_data (<br>id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,<br>description CHAR(50),<br>bin_data LONGBLOB,<br>filename CHAR(50),<br>filesize CHAR(50),<br>filetype CHAR(50)<br>);

A sample php3 script you can use to store data in your database

With the php script store.php3 you can transfer files via an HTML form interface into the created database.

store.php3

<html><br><head><title>Store binary data into SQL Database</title></head><br><body><br><br><?php<br>// code that will be executed if the form has been submitted:<br><br>if ($submit) {<br><br>    // connect to the database<br>    // (you may have to adjust the hostname,username or password)<br><br>    MYSQL_CONNECT("localhost","root","password");<br>    mysql_select_db("binary_data");<br><br>    $data = addslashes(fread(fopen($form_data, "r"), filesize($form_data)));<br><br>    $result=MYSQL_QUERY("INSERT INTO binary_data (description,bin_data,filename,filesize,filetype) ".<br>        "VALUES ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')");<br><br>    $id= mysql_insert_id();<br>    print "<p>This file has the following Database ID: <b>$id</b>";<br><br>    MYSQL_CLOSE();<br><br>} else {<br><br>    // else show the form to submit new data:<br>?><br><br>    <form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data"><br>    File Description:<br><br>    <input type="text" name="form_description"  size="40"><br>    <input type="hidden" name="MAX_FILE_SIZE" value="1000000"><br>    <br>File to upload/store in database:<br><br>    <input type="file" name="form_data"  size="40"><br>    <p><input type="submit" name="submit" value="submit"><br>    </form><br><br><?php<br><br>}<br><br>?><br><br></body><br></html>
So if you execute this script, you will see a simple HTML form. Use the “browse” button to select a file (for example an image) and press the “submit” button.

A Sample php3 Script With Which You Can Access the Stored Data

<?php<br><br> Syntax: getdata.php3?id=<id><br><br>if($id) {<br><br>    // you may have to modify login information for your database server:<br>    @MYSQL_CONNECT("localhost","root","password");<br><br>    @mysql_select_db("binary_data");<br><br>    $query = "select bin_data,filetype from binary_data where id=$id";<br>    $result = @MYSQL_QUERY($query);<br><br>    $data = @MYSQL_RESULT($result,0,"bin_data");<br>    $type = @MYSQL_RESULT($result,0,"filetype");<br><br>    Header( "Content-type: $type");<br>    echo $data;<br><br>};<br>?>

The script getdata.php3 is an example script that fetches the binary data from the database and passes it directly to the user.

As the script needs to “know” which file is requested, you have to add the ID as a parameter.

Example: A file has been stored with ID 2 in the database. To get this file, you have to call:

getdata.php3?id=2

If you have images saved in the database, you can use the getdata script as <img src> in your web page.

Example: You saved an Image as ID 3 in the database and want to show it on your web page. Use the following code:

<img src="getdata.php3?id=3">

If you are using Unix, check out your init-tree and change the corresponding startup file.

I hope this works fine for all of you. I also want to thank those of you, who wrote in improvements and fixes which helped me to complete this article.

If You Still Get Errors

This could be a timeout problem. If you upload large files via a slow connection, PHP’s default timeout of 30 seconds might kill your process. Change the max_execution:time variable in your php.ini to:

max_execution_time=-1
Please follow and like us:



Leave a Comment

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