Saving images in MySQL with PHP
People have different opinions on saving images in database. Some says, "Why bother database if we can handle this by saving images to disk". I am agree with this;).
Most of the requirements can be fulfilled by saving the images to disk. This reduces the unnecessary load on MySQL.
Here is the small code to save the image in MySQL with the help of PHP.
Images are saved in MySQL as BINARY data. BINARY data can not be saved in varchar or char data types, for this purpose we need a data type which can handle binary data. BLOB columns are treated as binary strings (byte strings). The following table is fulfilling our requirements for a simple test with a BLOB field.
CREATE TABLE `images` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `image` MEDIUMBLOB NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM;
Now we have a MySQL table ready to store the image. Next steps are easy i.e.
- Read the image
- Encode the image data
- Save binary data in DB
These 3 steps are performed with the following PHP code
$image = chunk_split(base64_encode(file_get_contents("image.jpg"))); $query = "INSERT INTO images (image) VALUES('$image')"; mysql_query($query) or die(mysql_error()); echo "Image id is ".mysql_insert_id();
Now we have saved the image in database successfully. The next step is to display the image.
// showimage.php header('Content-type: image/jpeg'); $query = "SELECT image from images where id=1"; $rs = mysql_fetch_array(mysql_query($query)); echo base64_decode($rs["image"]);
Ohhh!!! You wanted to display the image in HTML page. No problem, call this file as a image in <img> .e.g.
<html> <head> <title>Image Test</title> </head> <body> <h1>Displaying image from database</h1> <img src="showimage.php" /> </body> </html>
Download multiple files upload script
Let me know if you need some more info on this topic.
Matthew said,
March 9, 2008 @ 6:14 am
Hi I was just reading you little tutorial and was wondering if you would be kind enough maby to explain how you could work this as a form to insert one or several images to the database. Im working on a gallery done in css and php although im not very good a php yet but id like to try your idea and see if it works out for me…
Wasif said,
March 10, 2008 @ 11:52 pm
You need to make a form to upload files and read the uploaded files with $_FILES.
Here is a sample form for single file upload.
<form action=”file_upload.php” enctype=”multipart/form-data” method=”post”>
Provide Image: <input type=”file” name=”image_file” />
<input type=”submit” title=”Upload” />
</form>
And the PHP code to upload file
< ?
// file_upload.php
// Check if the file uploaded successfully
if(is_uploaded_file($_FILES["image_file"]['tmp_name']))
{
$image_type = "";
//for security check the image type
switch($_FILES["image_file"]["type"])
{
case "image/pjpeg":
case "image/jpeg":
case "image/gif":
$image_type = $_FILES["image_file"]["type"];
default:
echo "Invalid image type";
exit;
break;
}
$image = chunk_split(base64_encode(file_get_contents($_FILES["image_file"]['tmp_name'])));
// here you can save the image and image type in DB
// You will output image type in header() when displaying image
}
?>
For multiple files upload, visit the above link or ask again.
Margaret said,
May 3, 2008 @ 10:41 pm
I am new to php. I am working on a website where clients would search my website for listings. I have no idea how to design a database that upon request the listings would display information about particular club together with an image. I also wanted to have links displayed for example, “send to a friend”, “rate this” etc. I’ve consulted many books in this matter and still have no idea how to start with this. I need help!!!
PHP-Ausgabe incl. Bilder auf Server speichern - PHP @ tutorials.de: Forum, Tutorial, Anleitung, Schulung & Hilfe said,
May 14, 2008 @ 1:55 am
[...] und im Dateisystem ablegen? Wie Bilder in einer Datebank gespeichert werden, erklärt z. B. dieses Tutorial. Ein großer Nachteil des Ganzen ist natürlich, das Bilder groß sein können. Das heißt gerade wenn [...]
Chavez said,
June 24, 2008 @ 3:06 am
I need multiple images upload, please help me
Wasif said,
June 24, 2008 @ 9:44 pm
For multiple file upload you need to use ‘[]‘ with the HTML file element name. e.g. <input type=â€file†name=â€image_file[]†/>. You can find the example @ http://www.php.net/manual/en/features.file-upload.multiple.php.
gaurav said,
June 30, 2008 @ 1:18 am
this helps a lot and provide rapid help to me.. thnx alot
babybo said,
September 27, 2008 @ 9:32 am
I saved image on mysql.But can not display it on my browser (firefox and IE ), all i see on my firefox and IE browser is “http://localhost/showimage.php” text.please help me ! thank you very much for your reply .
mrPROB said,
December 22, 2008 @ 8:59 pm
i have the same problem as babybo,
i experimented before one year with images and mysql and i didnt have a problem..
but now all the scripts that i am trying has the same result…
”
I saved image on mysql.But can not display it on my browser (firefox and IE ), all i see on my firefox and IE browser is “http://localhost/showimage.phpâ€
”
eventhough the same script that i used last year.
i use wampserver 2.0c
Apache Version : 2.2.8
PHP Version : 5.2.5
MySQL Version : 5.0.51a
thanx in advance
Praveen Kumar.V said,
February 12, 2009 @ 6:19 pm
Hi,
as per my little knowledge i came to know that blob has can stroe aroung 2MB size data(image). I would like to know whether we can save big size pictures in mysql
Wasif said,
February 12, 2009 @ 8:09 pm
Praveen,
You can use MEDIUMBLOB or LONGBLOB for larger sizes. LONGBLOB can store upto 4GB.
Nauman said,
February 16, 2009 @ 5:35 pm
nice help, thanks
Yohan said,
February 25, 2009 @ 1:03 pm
I just can’t make it work…
upload should be success, data goes into mysql
but can’t load it to html
http://gspsite.comuf.com
warhead said,
February 27, 2009 @ 1:34 pm
Hi,
I got a question. Refering to the “showimage.php”, in the sql statement, Id is always equal to 1. So how can i make it dynamic? Thanx in advance.
Wasif said,
February 27, 2009 @ 9:42 pm
@Yohan
Can you post your showimage.php code and preferably the whole code?
Wasif said,
February 27, 2009 @ 9:49 pm
@warhead
I made the id static to illustrate the working. You can use anything to fetch the relevant image e.g. plant name to fetch the plant picture. You can create an additional page and get all the ids using a while() loop from the table and display them one by one.
What is your requirement or what you are trying to do?
warhead said,
February 28, 2009 @ 11:14 am
Thanx for the quick reply. Now im developing a site to sell a property. In one page, the page will show all the details including the picture. To “echo” details from db is not a hard task. But in the detail page,i will send ID to grab the picture from image table. I had already do like this,
showImage.php?id. To do like this,i need to post the page twice so that “showImage.php” will run and send the parameter to the showImage.php. Then grab the picture. Fail.
One of my biggest prob is,
———————–
header(‘Content-type: image/jpeg’);
$query = “SELECT image from images where id=1″;
$rs = mysql_fetch_array(mysql_query($query));
echo base64_decode($rs["image"]);
————————-
the header part, i always get this error,
“Cannot modify header information – headers already sent by “. I read there are alot of prob regarding this issue. So,my question, can we sent multiple header in same page for example “header(‘Content-type: image/jpeg’);” and header”(‘Content-type: text/html’);”.
I already lost with my question. I hope u can get the point that i want to ask u
. Thanx in advance.
Wasif said,
March 2, 2009 @ 2:39 am
The code you posted is correct to display the image. I believe this is the code of showimage.php file. You need to display image via tag in show detail page. Can you post the code of show detail page?
You can not send multiple headers in a single page.
warhead said,
March 2, 2009 @ 11:35 am
I had already tried by copy paste the whole code but still cannot display the image. hmm,if u dont mind, can u please send me the example? the html page and also the showimage.php. I want to try to run it on my local. Send to my email, [removed]@yahoo.com.sg Thanx in advance.
warhead said,
March 2, 2009 @ 11:46 am
can u give me ur email addr? i will send my pages and database to u. U can try u run it.
warhead said,
March 3, 2009 @ 7:03 am
hmmm…funny…i can run ur code. So now the prob how can i ran code that send parameter eg. showimage.php?id. Got any idea?
Wasif said,
March 5, 2009 @ 1:29 am
I can not advise you until I see your application.
warhead said,
March 5, 2009 @ 12:22 pm
hi,sorry for the late reply. Actually i had change my application by using “link image”. Bcoz of the time constraint, i really dont have time to do the experiment. Thanx for ur replies. Maybe in the future,if i opt to use this method,i will ask u again.
. Thank you very much.
Malinda Ramadhani said,
March 17, 2009 @ 1:29 pm
Hi I was just reading you little tutorial and was wondering if you would be kind enough maby to explain how you could work this as a form to insert one or several images to the database. Im working on a gallery done in css and php although im not very good a php yet but id like to try your idea and see if it works out for me…
Wasif said,
March 19, 2009 @ 2:20 am
Hi Malinda,
I have uploaded a basic script to upload multiple files, save them in database and display all the images on a single page.
Download multiple files upload script
Emman said,
May 4, 2009 @ 1:35 am
Hell Wasif, By the way, my name is Emman, I’m new to PHP.. I’ve already take your code on how to upload image in a form and I did it successfully but my problem is on how to display the image that I’ve stored in the database. Please help.
Thanks in advance.
Wasif said,
May 4, 2009 @ 7:12 pm
Did you use the exact code which I have posted above? If yes, did you look at showimage.php? I have also uploaded working code which can upload, and display the images. You can download the code from http://www.mwasif.com/wp-content/uploads/2007/07/multiple_files_upload.zip.
dddd said,
June 3, 2009 @ 9:43 pm
i copied your code but cannot get it to display the image!!!!!!!!11
i have the blob inDB but cannot see it in a browser…???
HELP
Wasif said,
June 3, 2009 @ 9:47 pm
Post your complete code.
Rusty777 said,
July 21, 2009 @ 8:36 pm
Hello,
I am new to php and mysql… Fairly proficient in HTML
My situation is:
I have a mysql table with member info and a field named graphic1. The variable graphic1 contains the member’s logo/graphic.
I am able to use mysql_query and fetch_array to echo the filename of the member’s graphic but I need to display the actual graphic, not just the filename of the graphic.
Is it possible to pass the contents of variable “graphic1″, (i.e.this is the file’s name on my server) so I can use it in an html “img src=” statement.
Thanx for any assistance with this…
Wasif said,
July 22, 2009 @ 7:45 pm
Where did you store the graphic, in database or on the disk? If you have stored the graphic on disk, you can simply pass the path to the graphic/logo in <img> tag like below
<img src=”path_to_graphic”>
Rusty777 said,
July 22, 2009 @ 10:20 pm
Your “tag” did not show up but my graphics/logos are stored as files on the server. The NAME of each graphic is stored in a record in a mysql table which has the member data including the name of their logo, not the picture file, just the name of the file.
I have a bunch of members and need a way to pass the filename stored in mysql table to the “img src” statement in html.
In php, I have been successful in getting the name of the file into a variable ($logo) but have not found a way to use the variable in the “img src” statement.
The $logo var contains marie.jpg but HTML does not resolve the variable name. It just shows a broken image/link on the screen.
I hope this makes sense…
Thanks again.
Wasif said,
July 22, 2009 @ 10:51 pm
You just need to pass the $logo variable (I assume $logo contains the location of the image file) <img src=’<?php echo $logo?>’>
If you still have confusion, then post your code to review.
Rusty777 said,
July 23, 2009 @ 10:30 am
The code you provided worked great. I wish I had posted the problem a week ago. But I’m sure all the reading I did this week before I got desperate will pay off later. Thank you so much.
roni08 said,
July 29, 2009 @ 10:10 am
i’ve tried your provided code and it’s great and work properly. But my question is how do i resize the images or picture that i’ve been upload and save to my database. when i’m going to display the images/picture it has been cut. i hope you could help me. thanks.
roni08 said,
July 30, 2009 @ 11:23 am
can anyone could help me with the fix image size, to display not necessarily damaging the resolution of the image?. it’s all about the code above. pls. help me. thanks.
4ward.NRGie said,
November 11, 2009 @ 12:56 pm
Here is my showimage.php
[code]
[code]
But I can't olad it ti index.php
dark_byte said,
December 26, 2009 @ 7:36 pm
Hi nice article but what if I want to take multiple images form the database and display them.
Will I have to build a script for each image; please I badly need help over this.
Wasif said,
December 29, 2009 @ 7:35 am
You need a single file like showimage.php and just need to pass the image id as I did in the example id=1
$query = “SELECT image from images where id=1″;