Store pictures in MySQL database

If you want to put binary data, such as picture files and HTML files, directly stored in your MySQL database, then this article is written for you! I'll show you how to store these files through an HTML form, and how to access and use them.

This article outlines:

。 Create a new database in MySQL

。 An example of how to save a file

。 An example of how to access a file program

Create a new database in MySQL

First, you have to create a new database in your MySQL and we will store those binaries in this database. In the example I will use the following structure, in order to build the database,

You must take the following steps:

。 Access to the MySQL controller

。 Enter 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 (m), Bin_data Longblob,

FileName Char, FileSize char (50),

FileType CHAR (50)); "(Cannot break the break)

If there are no surprises, the database and tables should be set up.

An example of how to save a file

With this example you can transfer files to a database through an HTML form.

Store.php3

Store.php3-by Florian Dittmer

?>

If a form is submitted, the code is executed:

if ($submit) {

Connecting to a database

(You may need to adjust the host name, 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)

[Next 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 that stores the new data

?>

}

?>

If you perform 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 you just uploaded, and remember this ID, which will be used later.

An example of how to access a file program

You can access the files you just saved through this program.

Getdata.php3-by Florian Dittmer

Call Method: Getdata.php3?id=

if ($id) {

You may need to adjust the host name, 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 that to access that file, you must take the ID as an argument.

For example, the ID of a file in the database is 2. You can call it this way:

getdata.php3?id=2

If you store a picture in a database, you can call it as you would call a picture.

Example: The ID of a picture file in the database is 3. You can call it this way:

How to store files larger than 1MB:

If you want to store files larger than 1MB, you must make a number of changes to your program, PHP settings, and SQL settings.

The following may help you save files that are less than 24MB:

1, modify the STORE.PHP3, change the value of max_file_size to 24000000.

2, modify your PHP settings, in general, PHP only allow less than 2MB of files, you have to Max_filesize (in the php.ini) of the value of the change to 24000000

3, remove the MySQL packet size limit, in general, MySQL is less than 1 MB packet.

4, you must restart your MySQL with the following parameters

/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

5, If still error:

It may be a timeout error, and if you store a large file through a slow connection, the default time limit for PHP is 30 seconds. You can change the value of Max_execution_time (in php.ini) to-1.