Parse and store images directly in MySQL

Guidance:In progressMySQL databaseDuring the operation, you may encounter many problems, such as the direct storage of slice files. Next we will mainly introduce the directStore imagesAnd hope to help you.

If you want to directly store binary data, such as image files and HTML files, in your MySQL database, this article is for you!

I will show you how to store these files through HTML forms and how to access and use these files.

Overview:

Create a new database in mysql

Example program for storing files

Example program on how to access a file

Create a new database in mysql

First, you must create a new database in your mysql database. We will store those binary files in this database. In this example, I will use the following structure. To create a database, you must perform the following steps:

Go to MySql Controller

Enter the command "create database binary_data ;"

Enter the command "use binary_data ;"

Enter the 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); "(line cannot be broken)

If there is no accident, the database and table should be created.

An example of how to store files. With this example, You can transmit files to the database through Html forms ..

Store. php3

? Php

// Store. php3-by Florian Dittmer dittmer@gmx.net>

?>

HTML>

HEAD> TITLE> Store binary data into SQL Database/TITLE>/HEAD>

BODY>

? Php

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

If ($ submit ){

// Connect to the database

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

"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 {

// Otherwise, the new data storage form is displayed.

?>

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>

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

After the file is uploaded to the web server, the program will tell you the ID of the file you just uploaded. Remember this ID and you will need it later.

Example program on how to access a file

You can use this program to access the files you just stored.

? Php

// Getdata. php3-by Florian Dittmer dittmer@gmx.net>

// Call method: getdata. php3? Id = id>

If ($ id ){

// You may need to adjust the host name, user name, 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 the file to be accessed, and you must take the ID as a parameter.

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

Getdata. php3? Id = 2 If you store an image in a database, you can call it like calling an image.

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

Img src = "getdata. php3? Id = 3 ">

How to store files larger than 1 MB:

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

The following several items may help you store files smaller than 24 MB:

Modify store. php3 and change MAX_FILE_SIZE to 24000000.

Modify Your PHP settings. In general, PHP only allows files smaller than 2 MB. You must change the value of max_filesize (in php. ini) to 24000000.

Remove the MYSQL data packet size limit. In general, MYSQL data packets smaller than 1 MB.

You must use the following parameters to restart your MYSQL

/Usr/local/bin/safe_mysqld-O key_buffer = 16 M-O table_cache = 128-O sort_buffer = 4 M-O record_buffer = 1 M-O max_allowed_packet = 24 M

If the error persists:

It may be a timeout error. If you store a large file through a very slow connection, the default PHP time limit is 30 seconds.

You can change the value of max_execution_time (in php. ini) to-1.

This article introduces the knowledge of directly storing images in MySQL. I believe that through the above learning, everyone should be able to master the operation of directly storing image files in MySQL, similar problems can be easily solved in future work.