How to store music and picture files in MySQL

Last Update:2015-02-20 Source: Internet

Author: User

Tags html form

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

How do I store music and picture files in MySQL?

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

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More