How to store images directly in MySQL

HowMySQL databaseDirectStore images? This is a problem that has plagued many people. 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. We will provide a detailed explanation below, hoping to help you.

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 "createdatabasebinary_data ;"

Enter the command "usebinary_data ;"

Enter the command "CREATETABLEbinary_data (idINT (4) NOTNULLAUTO_INCREMENTPRIMARYKEY,

DescriptionCHAR (50), bin_dataLONGBLOB, filenameCHAR (50), filesizeCHAR (50), filetypeCHAR (50); "the row 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 is referenced below:

// Store. php3-byFlorianDittmer

?>

// 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 ("INSERTINTObinary_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"

ThisfilehasthefollowingDatabaseID: $ id ";

MYSQL_CLOSE ();

} Else {

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

?>

Filetoupload/storeindatabase:

}

?>

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.

Reference content is as follows:

// Getdata. php3-byFlorianDittmer

// Call method: getdata. php3? 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 = "selectbin_data, filetypefrombinary_datawhereid = $ 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:

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 max_filesize value 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-Okey_buffer = 16M-Otable_cache = 128-Osort_buffer = 4M-Orecord_buffer = 1M-Omax_allowed_packet = 24 M

The explanation of directly storing images in the MySQL database is all over. You can refer to the content mentioned above for reference, I hope everyone will be able to gain some benefits from the content described above.