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` BLOB 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.
Ohh not satisfied yet.. want to display in html page too.. No issues I have solution for that too…
<html> <head> <title>Image Test</title> </head> <body> <h1>Displaying image from database</h1> <img src="showimage.php" /> </body> </html> // 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"]);