Sr. Web Developer
mediabistro.com
US-NY-New York

Justtechjobs.com Post A Job | Post A Resume

How to Store Images Directly in the Sql Database
Florian Dittmer Florian Dittmer
If you want to store binary data like images and html files directly in your MySQL database, this column is for you!
I will show how you can store the data via the HTML forms "File" feature in your database and how you can access and use this data in your webproject.
If you have read the article "PHP, MySQL and Images" by William Samplonius here on phpbuilder.com, this might be interesting for you as William stores the binary data somewhere on your harddisk (using a shell command), instead of storing the image directly in the Sql-Database.
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 intructions (the table structure) to the monitor
  • the database+table should be created

CREATE TABLE binary_data (
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description CHAR(50),
bin_data LONGBLOB,
filename CHAR(50),
filesize CHAR(50),
filetype CHAR(50)
);
A sample php3 script you can use to store data in your database
With the php script store.php3 you can transfer files via a html form interface into the created database.
store.php3

<?php

// store.php3 - by Florian Dittmer <dittmer@gmx.net>
// Example php script to demonstrate the storing of binary files into
// an sql database. More information can be found at http://www.phpbuilder.com/
?>

<html>
<head><title>Store binary data into SQL Database</title></head>
<body>

<?php
// code that will be executed if the form has been submitted:

if ($submit) {

    // connect to the database
    // (you may have to adjust the hostname,username or password)

    MYSQL_CONNECT("localhost","root","password");
    mysql_select_db("binary_data");

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

    $result=MYSQL_QUERY("INSERT INTO binary_data (description,bin_data,filename,filesize,filetype) ".
        "VALUES ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')");

    $id= mysql_insert_id();
    print "<p>This file has the following Database ID: <b>$id</b>";

    MYSQL_CLOSE();

} else {

    // else show the form to submit new data:
?>

    <form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data">
    File Description:<br>
    <input type="text" name="form_description"  size="40">
    <input type="hidden" name="MAX_FILE_SIZE" value="1000000">
    <br>File to upload/store in database:<br>
    <input type="file" name="form_data"  size="40">
    <p><input type="submit" name="submit" value="submit">
    </form>

<?php

}

?>

</body>
</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.
After the file has been uploaded to the webserver, the script will tell you which database ID the uploaded file has. You need to know this ID to access this data (with the following script).
A Sample php3 Script With Which You Can Access the Stored Data
The script getdata.php3 is an example script that fetches the binary data from the database and passes it directly to the user.

<?php

// getdata.php3 - by Florian Dittmer <dittmer@gmx.net>
// Example php script to demonstrate the direct passing of binary data
// to the user. More infos at http://www.phpbuilder.com
// Syntax: getdata.php3?id=<id>

if($id) {

    // you may have to modify login information for your database server:
    @MYSQL_CONNECT("localhost","root","password");

    @mysql_select_db("binary_data");

    $query = "select bin_data,filetype from binary_data where id=$id";
    $result = @MYSQL_QUERY($query);

    $data = @MYSQL_RESULT($result,0,"bin_data");
    $type = @MYSQL_RESULT($result,0,"filetype");

    Header( "Content-type: $type");
    echo $data;

};
?>
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 webpage.
Example: You saved an Image as ID 3 in the database and want to show it on your webpage. Use the following code:
<img src="getdata.php3?id=3">
How to Handle Files Larger Than 1 MB:
If you want to upload and store files bigger than 1 MB, you have make several changes to the scripts and your php/sql setup, as it is caused by default limitations of the programs. Do the following to be able to store files as large as 24 Megabyte:
  1. Edit the store.php3 script. Change the MAX_FILE_SIZE value (in the form) to 24000000.
  2. Remove the filesize limitation from your php installation. This is set either in your php.ini or in your apache config files. By default, php3 only allowes files smaller than 2 MB. You have to change the max_filesize variable to: max_filesize = 24000000.
  3. Remove the mysql packet size limitation. By default, mysql only accepts packets that are smaller than 1 MB.
  4. You have to restart your database with some parameters, which will remove the limitations.
Mark Leidy wrote in that the following set up worked for him (and it worked for me, too):
/usr/local/bin/safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24M
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
Some Last Words...
Yes, I know that this is a really short column and not a very detailed description, but even if you are new to php (like me), it should be possible for you to understand the provided scripts.
phpbuilder.com-reader Mark Leidy rewrote the scripts. You might want to check out his versions.
If you have any questions or hints for me to improve the scripts, I would be glad to talk to you through email.