Detailed explanation:——How to store pictures in the database

Detailed explanation:——How to store pictures in the database_PHP tutorial

WBOY

Release： 2016-07-21 15:59:24

Original

836 people have browsed it

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 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 line break)

If there are no accidents, the database and tables should be created.

A sample program on how to save files

Using this example you can transfer files to the database through Html form.

<ccid_code>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 ($submit) {
// 连接到数据库
// (你可能需要调整主机名，用户名和密码)
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 {
// 否则显示储存新数据的表单
?>
<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></ccid_code>

Copy after login

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 just uploaded. Remember this ID for later use.

A sample program on how to access files

You can access the files you just saved through this app

<ccid_code><?php
// getdata.php3 - by Florian Dittmer <dittmer@gmx.net>
// 调用方法: getdata.php3?id=<id>
if($id) {
// 你可能需要调整主机名，用户名和密码:
 @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;
};
?></ccid_code>

Copy after login

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 image in the database, you can call it like a picture.

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

How to store files larger than 1MB:

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

The following tips 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 data packet size limit. Under normal circumstances, MYSQL data packets are less than 1 MB.

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:

This may be a timeout error. If you are storing a large file over a slow connection, PHP's default time limit is 30 seconds. You can change the value of max_execution_time (in php.ini) to -1