How to store images in MySQL database in PHP_PHP tutorial

If you want to save binary data, such as image files and HTML files, directly in your MySQL database, then this article is for you! I'll show you how to store these files through HTML forms, and how to access and use these files.

Overview of this article:

. Create a new database in mysql

. An example program on how to save files

. An example program on how to access files

Create a new database in mysql

First, you must create a new database in your mysql, we will store those binary files in this database. In the example I will use the following structure, in order to create the database,

You must do the following steps:

. Enter MySql Controller

. Enter the command "create database binary_data;"

. Enter the command "use binary_data;"

. Enter command

"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));" (cannot line break)

If there are no accidents, the database and tables should be created.

A sample program on how to save files

Using this example you can transfer files to the database through Html form.

store.php3

// store.php3 - by Florian Dittmer

?>

// If the form is submitted, the code will be executed:

if ($submit) {

//Connect to the database

// (You may need to adjust hostname, username and 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)

[Continue from previous line:] VALUES ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')");

$id= mysql_insert_id();

print "

This file has the following Database ID: $id";

MYSQL_CLOSE();

} else {

// Otherwise, display the form to store new data

?>

}

?>

If you execute this program, you will see a simple Html form, click "Browse" to select a file, and then click Submit.

When the file is uploaded to the web server, the program will tell you the ID of the file just uploaded. Remember this ID for later use.

A sample program on how to access files

You can access the files you just saved through this program

// getdata.php3 - by Florian Dittmer

// Calling method: getdata.php3?id=

if($id) {

// You may need to adjust the hostname, username and password:

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

};

?>

The program must know which file to access, and you must pass the ID as a parameter.

For example: a file has an ID of 2 in the database. You can call it like this:

getdata.php3?id=2

If you store the image in the database, you can call it like a picture.

Example: The ID of an image file in the database is 3. You can call it like this:

How to store files larger than 1MB:

If you want to store files larger than 1MB, you must make many modifications to your program, PHP settings, and SQL settings.

The following tips may help you save files smaller than 24MB:

1. Modify store.php3 and change the value of MAX_FILE_SIZE to 24000000.

2. Modify your PHP settings. Under normal circumstances, PHP only allows files smaller than 2MB. You must change the value of max_filesize (in php.ini) to 24000000

3. Remove the MYSQL data packet size limit. Under normal circumstances, MYSQL data packets are less than 1 MB.