Insert and Update An Image to MySQL Server Field with Blob Data Type in PHP

Normally the images are stored in a file directory, not in the database. It is because that storing image data in database will make database bulky. In addition, there is some techniques to take care of. How to store and retrieve an image from MySQL server? Here is how:

1. Blob data type in MySQL server

First, create a blob type field in MySQL server:

mysql> describe products;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| prod_id         | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| prod_pic        | mediumblob            | YES  |     | NULL    |                |
| prod_pic_type   | varchar(11)           | YES  |     | NULL    |                |
+-----------------+-----------------------+------+-----+---------+----------------+

2. Insert into MySQL server:

Add enctype="multipart/form-data" in the <form> tag in order to enable the file upload functionality.
Set the type to ‘file’ for <input> tag.

Upload image file form:

<form action="index.php" method="post" enctype="multipart/form-data">
  <input type="file" name="prod_pic" accept=".jpg, .jpeg, .png">
  <button type="submit">Submit</button>  
</form>

Insert image into MySQL server in index.php

$prod_pic = $mysqli->real_escape_string(file_get_contents($_FILES['prod_pic']['tmp_name']));
$prod_pic_type = $_FILES['prod_pic']['type'];
 :
 :
$sql = "UPDATE products 
        SET prod_pic      = '{$prod_pic}',
            prod_pic_type = '{$prod_pic_type}'
        WHERE prod_id = {$prod_id}";
$mysqli->query($sql) or die($mysqli->connect_error);
$mysqli->close(); 

As you can see, the key part is:

file_get_contents($_FILES['prod_pic']['tmp_name'])

and

$mysqli->real_escape_string(......)

3. Retrieve an image from MySQL server:

There are two methods to retrieve images from MySQL server:

A. Get image with a function.

Somewhere in the page: (using Smarty template engine)

 <img src="get_image.php?prod_id={$product.prod_id}" />

get_image.php

<?php
  $mysqli = new mysqli( _DBHOST, _DBUSER, _DBPASSWD, _DBNAME);
  if ($mysqli->connect_error){
    die ('DB Error: ('.$mysqli->connect_errno.' )'.$mysqli->connect_error);
  } else {
    $mysqli->set_charset('utf8');
  }

  $prod_id = isset($_REQUEST['prod_id']) ? filter_var($_REQUEST['prod_id'], FILTER_SANITIZE_NUMBER_INT) : 0;

  $sql = "SELECT prod_pic, prod_pic_type FROM products WHERE prod_id = '{$prod_id}'";
  $result = $mysqli->query($sql) or die($mysqli->connect_error);
  $product = $result->fetch_assoc();
  $mysqli->close();

  header("Content-type: ".$product['prod_pic_type']);
  echo $product['prod_pic'];
?>

The last two lines of code is the key part.

header("Content-type: ".$product['prod_pic_type']);  
echo  $product['prod_pic'];

B. Using base64 encode to show the image

<img src="data:image/{$product.prod_pic_type};base64,{base64_encode($product.prod_pic)}" />

( using Smarty template engine )

4. Conclusion

Method B seems to be a better choice since it does not need to retrieve data from server twice as method A does. Also, method B is more concise and cleaner. It’s only one line of code and an extra PHP function is not necessary.