PHPBuilder - How to Store Images Directly in the Sql Database



RSS Twitter
Articles Databases

How to Store Images Directly in the Sql Database

by: PHP Builder Staff
|
April 9, 2012

After the file has been uploaded to the web server, 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 info 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 allows only 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.


1
|
2
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:

Comment:



Comment:

(Maximum characters: 1200). You have characters left.