How can I store and retrieve images from a MySQL database using PHP?

Question

How can I insert an image in MySQL and then retrieve it using PHP?

I have limited experience in either area, and I could use a little code to get me started in figuring this out.

Answer 1

First you create a MySQL table to store images, like for example:

create table testblob (     image_id        tinyint(3)  not null default '0',     image_type      varchar(25) not null default '',     image           blob        not null,     image_size      varchar(25) not null default '',     image_ctgy      varchar(25) not null default '',     image_name      varchar(50) not null default '' ); 

Then you can write an image to the database like:

/***  * All of the below MySQL_ commands can be easily  * translated to MySQLi_ with the additions as commented  ***/  $imgData = file_get_contents($filename); $size = getimagesize($filename); mysql_connect("localhost", "$username", "$password"); mysql_select_db ("$dbname"); // mysqli  // $link = mysqli_connect("localhost", $username, $password,$dbname);  $sql = sprintf("INSERT INTO testblob     (image_type, image, image_size, image_name)     VALUES     ('%s', '%s', '%d', '%s')",     /***      * For all mysqli_ functions below, the syntax is:      * mysqli_whartever($link, $functionContents);       ***/     mysql_real_escape_string($size['mime']),     mysql_real_escape_string($imgData),     $size[3],     mysql_real_escape_string($_FILES['userfile']['name'])     ); mysql_query($sql); 

You can display an image from the database in a web page with:

$link = mysql_connect("localhost", "username", "password"); mysql_select_db("testblob"); $sql = "SELECT image FROM testblob WHERE image_id=0"; $result = mysql_query("$sql"); header("Content-type: image/jpeg"); echo mysql_result($result, 0); mysql_close($link); 

Answer 2

Instead of storing images in database store them in a folder in your disk and store their location in your data base.