How do I store images in a database in php
- 2020-05-06 11:47:00
- OfStack
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 the HTML form and how to access and use them.
Overview of this article:
. Create a new database
in mysql . An example of how to store files
. An example of how to access files
Create a new database
in mysql First, you must 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,
, 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(50), bin_ LONGBLOB, filename CHAR(50), filesize CHAR(50), filetype CHAR(50));" (not line breaking)
If there are no surprises, the database and tables should be set up.
An example of how to store files
With this example you can transfer files to a database via the Html form.
store.php3
// store.php3 - by Florian Dittmer < dittmer@gmx.net >
? >
// if the form is submitted, the code will be executed:
if ($submit) {
// connect to 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)
[next 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 displays the form
for storing the 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, 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 the ID, which will be used later.
An example of how to access files
You can access the file
from this program // getdata.php3 - by Florian Dittmer < dittmer@gmx.net >
// 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 to access that file, you must take ID as an argument.
For example: a file in the database ID is 2. you can call it
getdata.php3?id=2
If you store the image in the database, you can call it just like you call the image.
An image file in the database has ID as 3. you can call it
How to store files larger than 1MB:
If you want to store files larger than 1MB, you will have to make many changes to your program, PHP Settings, and SQL Settings.
The following may help you save files smaller than 24MB:
1. Modify store.php3 , and change the value of MAX_FILE_SIZE to 24000000.
2. Modify your PHP Settings. In general, PHP only allows files smaller than 2MB, you must change the value of max_filesize (in php.ini) to 24000000
3. Remove the packet size limit of MYSQL, and generally MYSQL is 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 still wrong:
This could 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
Overview of this article:
. Create a new database
in mysql . An example of how to store files
. An example of how to access files
Create a new database
in mysql First, you must 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,
, 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(50), bin_ LONGBLOB, filename CHAR(50), filesize CHAR(50), filetype CHAR(50));" (not line breaking)
If there are no surprises, the database and tables should be set up.
An example of how to store files
With this example you can transfer files to a database via the Html form.
store.php3
// store.php3 - by Florian Dittmer < dittmer@gmx.net >
? >
// if the form is submitted, the code will be executed:
if ($submit) {
// connect to 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)
[next 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 displays the form
for storing the 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, 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 the ID, which will be used later.
An example of how to access files
You can access the file
from this program // getdata.php3 - by Florian Dittmer < dittmer@gmx.net >
// 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 to access that file, you must take ID as an argument.
For example: a file in the database ID is 2. you can call it
getdata.php3?id=2
If you store the image in the database, you can call it just like you call the image.
An image file in the database has ID as 3. you can call it
How to store files larger than 1MB:
If you want to store files larger than 1MB, you will have to make many changes to your program, PHP Settings, and SQL Settings.
The following may help you save files smaller than 24MB:
1. Modify store.php3 , and change the value of MAX_FILE_SIZE to 24000000.
2. Modify your PHP Settings. In general, PHP only allows files smaller than 2MB, you must change the value of max_filesize (in php.ini) to 24000000
3. Remove the packet size limit of MYSQL, and generally MYSQL is 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 still wrong:
This could 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