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 of how to save files
. An example program of 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 the 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));" (Cannot break lines)
If there are no accidents, the database and table should be established.
An example program of how to store files
Using this example, you can transfer files to the database through Html forms.
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 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)
[Continue from the 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
?>
File Description:
File to upload/store in database:
}
?>
If you execute this program, you will see a simple Html form, click Browse to select a file, 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 use later.
An example program of how to access files
You can access the file you just saved through this program
// getdata.php3 - by Florian Dittmer
// Call 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: The ID of a file in the database is 2. You can call it like this:
getdata.php3?id=2
If you store the picture in the database, you can call it like calling the picture it.
Example: An image file has an ID of 3 in the database. You can call it like this:
How to store files larger than 1MB:
If you want to store files larger than 1MB, you must configure your program , PHP settings, SQL settings to make many modifications.
The following items may help you store 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 packet size limit. Under normal circumstances, MYSQL is less than 1 MB data 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 the error still occurs:
It may be a timeout error. If you store a large file through a very slow connection, PHP's default time The limit is 30 seconds. You can change the value of max_execution_time (in php.ini) to -1
[Related recommendations]
2.Detailed explanation of how to write SQL statements to delete tables in different databases
3.Teach you how to use one machine to run multiple MySQL services
4.Small Mysql database backup script without virtual host
5.Detailed examples of using mysqldump to back up and restore tables
The above is the detailed content of Detailed explanation of examples of php storing images in mysql. For more information, please follow other related articles on the PHP Chinese website!