Saving Images in MySQL
Sometimes, it's more convenient to save images in a database than as files.
MySQL and PHP make it very easy to do this. In this article, I will describe
How to save images in a MySQL database and display them later on.
Setting up the database
The difference between any regular text or integer fields and a field that
Needs to save an image is the amount of data that is needed to be held in
Field. MySQL uses special fields to hold large amounts of data. These fields
Are known as blobs (BLOB ).
Here is the Blob definition from the MySQL site:
A blob is a binary large object that can hold a variable amount of data.
Four blob types tinyblob, blob, mediumblob and longblob differ only in
Maximum length of the values they can hold
For more information about MySQL BLOBs check out http://www.mysql.net/Manual_c
Hapter/manual_Reference.html # BLOB
Use the next syntax to create a basic table that will hold the images:
Create table Images (
PicNum int not null AUTO_INCREMENT primary key,
Image BLOB
);
Setting the upload script
An example of a file upload front end can be seen at File Uploading by berber
(29/06/99). What we need now is the PHP script that will get the file and
Insert it into MySQL. The next script does just that. In the script, I'm
Assuming that the name of the file field is "Picture ".
<?
If ($ Picture! = "None "){
$ PSize = filesize ($ Picture );
$ MysqlPicture = addslashes (fread (fopen ($ Picture, "r"), $ PSize ));
Unlink ($ Picture );
Mysql_connect ($ host, $ username, $ password)
Or die ("Unable to connect to SQL server ");
@ Mysql_select_db ($ db)
Or die ("Unable to select database ");
Mysql_query ("insert into Images (Image) VALUES '($ mysqlPicture ')")
Or die ("Can't Perform Query ");
}
Else {
Echo "You did not upload any picture ";
}
?>
This is all that is needed to enter the image into the database. Note that in
Some cases you might get an error when you try to insert the image
MySQL. In such a case you shoshould check the maximum packet size allowed
Your MySQL ver. It might be too small and you will see an error about this in
The MySQL error log.
What we did in the above file is:
1. Check if a file was uploaded with If ($ Picture! = "None ").
2. addslashes () to the picture stream to avoide errors in MySQL.
3. Delete the temporary file.
3. Connect to MySQL, choose the database and insert the image.
Displaying the images
Now that we know how to get the images into the database we need to figure
Out how to get them out and display them. This is more complicated
Getting them in but if you follow these steps you will have this up and
Running in no time.
Since showing a picture requires a header to be sent, we seem to be in
Impossible situation in which we can only show one picture and than we can't
Show anymore since once the headers are sent we can't send any more headers.
This is the tricky part. To outsmart the system we use two files. The first
File is the HTML template that knows where we want to display the image (s ).
It's a regular PHP file, which builds the HTML that contains the tags,
As we want to display them. The second file is called to provide the actual
File stream from the database directly into the SRC property of the
Tag.
This is how a simple script of the first type shoshould look like:
<HTML>
<Body>
<?
Mysql_connect ($ host, $ username, $ password)
Or die ("unable to connect to SQL Server ");
@ Mysql_select_db ($ dB)
Or die ("unable to select database ");
Mysql_query ("select * from images ")
Or die ("can't perform query ");
While ($ ROW = mysql_fetch_object ($ result )){
Echo " picnum/"> ";
}
?>
</Body>
</Html>
While the HTML is being displayed, the SecondType. php3 file is called
Each image we want to display. The script is called with the Picture ID
(PicNum) which allows us to fetch the image and display it.
The SecondType. php3 file looks like this:
<?
$ Result = mysql_query ("SELECT * FROM Images WHERE PicNum = $ PicNum ")
Or die ("Can't perform Query ");
$ Row = mysql_fetch_object ($ result );
Header ("Content-type: image/gif ");
Echo $ row-> Image;
?>
This is the whole theory behind images and MySQL. The scripts in this example
Are the basics. You can now enhance these scripts to include thumbnails, set
The images in various positions, enhance the database table to hold an ALT
Field, Check the width and height of the images before you insert them
The database and keep that data in the table too etc...
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
