If you want to store binary data, such as image files and HTML files, directly 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 procedure for how to store a file
An example procedure for how to access a file
Build 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 do 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 (+), bin_data longblob, filename char (n), FileSize char (50), filetype Char
If there are no surprises, the database and tables should be set up well.
An example of how to store a file in this example you can transfer a file to a database using an HTML form.
Store.php3
<?php
store.php3-by Florian Dittmer <[email protected]>;
?>;
<HTML>;
<HEAD>;<TITLE>; Store binary data into SQL database</title>;<BODY>;
<?php
If you submit the form, the code is executed:
if ($submit) {
Connecting to a database
(You may need to adjust the 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)".
"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 form that stores the new data is displayed
?>;
<form method= "POST" action= "<?php echo $PHP _self;?>;" enctype= "Multipart/form-data" >;
File description:<br>;
<input type= "text" name= "form_description" size= ">;"
<input type= "hidden" name= "max_file_size" value= "1000000" >;
<br>; File to Upload/store in database:<br>;
<input type= "File" Name= "Form_data" size= ">;"
<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.
When 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 use it later.
An example procedure for how to access a file
You can access the files you just saved through this program.
<?php
getdata.php3-by Florian Dittmer <[email protected]>;
Calling method: getdata.php3?id=<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 to access that file, you must use the ID as a parameter.
For example, a file has an ID of 2 in the database. You can call it this way:
getdata.php3?id=2 If you store the picture in a database, you can call it like a picture.
Example: A picture file has an ID of 3 in the database. You can call it this way:
;
How to store files larger than 1MB:
If you want to store files larger than 1MB, you have to make a lot of changes to your program, PHP settings, and SQL settings.
The following may help you to store files less than 24MB:
Modify the STORE.PHP3 to change the value of the max_file_size to 24000000.
Modify your PHP settings, in general, PHP only allows files less than 2MB, you must change the value of Max_filesize (in php.ini) to 24000000
Remove the MySQL packet size limit and, in general, MySQL less than 1 MB of packets.
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
If the error still occurs:
May be a timeout error, if you have a very slow connection to store a very large file, PHP default time limit is 30 seconds.
You can change the value of Max_execution_time (in php.ini) to-1
How to store music and picture files in MySQL
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
