Storing Images in MySQL
I just wrote a quick example that explains how to store images in MySQL database using the BLOB fieldtype which can used to store Binary Data. But I have been used to store the images in the filesystem and have a reference to it in the database. Most of them feel storing images or other binary data in the database is a bad idea as it creates too much overhead. But there are some advantages using this method as well. Have fun..
—– Table Structure —–
1 2 3 4 5 6 | mysql> CREATE TABLE `imgtest` ( -> `id` int(10) unsigned NOT NULL auto_increment, -> `imgstr` longblob NOT NULL, -> PRIMARY KEY (`id`) -> ); |
Insert image to the database
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | <?php #Image Path $imagePath="/path/to/img/img3.jpg"; #Connect to MySQL mysql_connect("localhost","user","******"); mysql_select_db("test"); #Read Image file into a String $imgStr=addslashes(file_get_contents($imagePath)); #Store Image String to the database $query="insert into imgtest (imgstr) values ('".$imgStr."')"; mysql_query($query) or die(mysql_error()); ?> |
Display image from database
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <?php #Set Content Type header("Content-Type: image/jpg"); #Image Id $imgId=(isset($_GET["id"]))?$_GET["id"]:1; #Connect to MySQL mysql_connect("localhost","root","*****"); mysql_select_db("test"); #Output Image String from database $query="select imgstr from imgtest where id='$imgId'"; $result=mysql_query($query); $imgStr=mysql_result($result,0,"imgstr"); echo $imgStr; ?> |
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
