Which datatype is used to store picture and how?

I greatly appreciate you for coming to this guide on “datatype is used to store picture” can be uploaded and retrieved. Yet I would discourage this store picture in a database because traditional RDBMS are not always designed in order to store such datatype. I am sure you have your own motives to want to preserve photos in the database.

If you want to save images that you download somewhere, simply use the regular file system. When privacy issues exist, place the image data in a small repository that can not be accessed widely–then using PHP to pick up photos.

Which datatype is used to store pictures?

Blob is used to store images.

A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.

The length is given in bytes for BLOB unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively.

Note: Length is specified in bytes for the BLOB.

Types of Blob

  1. BLOB will only hold up to 64 KB of data.
  2. MEDIUMBLOB holds up to 16 MB of data.
  3. LONGBLOB up to 4 GB of data.

Quick Start

  • Clone this Github repo, https://github.com/shankha96/Storing-image-in-Mysql-PHP
  • Create a dummy database and import create-database.sql
  • Update the database settings in config.php
  • Change the ?f= parameter in show.html to the file name of the image that you want to upload.
  • Launch upload.html in your web browser, and you can start tracing how it works from there.

Database Setup

Let us first lay some basis in the database before we go into this code–please ensure there is a table with a column set to “BLOB” data type.

CREATE TABLE `upload`(
     `NAME` varchar(255) NOT NULL,
     `data` longblob NOT NULL
)engine=innodb DEFAULT charset=utf8;
ALTER TABLE `upload` ADD UNIQUE KEY `NAME` (`NAME`);

Yet what does BLOB actually mean? It’s certainly not the gritty, even though it sounds like one thing. Blob serves as a’ binary large entity,’ perfect to store the picture files we uploaded.

Upload and store images

Now that the database is set, it is time to build the individual file upload scripts and then place them in the folder.

HTML

<!DOCTYPE html>
<html>
<head>
    <title>Image Upload</title>
</head>
<body>
   <form action="upload.php" method="post" enctype="multipart/form-data">
   <input type="file" name="upFile" id="upFile" accept=".png,.gif,.jpg,.webp" required>
   <input type="submit" name="submit" value="Upload Image"> </form>
</body>
</html>

Start by creating an HTML form to handle the uploading of images. The above is a very simple one, but it has some links to file upload plugins that you can use in the extras section.

Config File

<?php
// DATABASE SETTINGS // CHANGE THESE TO YOUR OWN !! 
define('DB_HOST', 'localhost');
define('DB_NAME', 'computopedia_test_db');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'computopedia');
define('DB_PASSWORD', '');
?>

This is a basic configuration file that includes database settings–don’t miss to modify them yourself.

PHP Upload Handler

<?php 
  /* [INIT] */ 
  require "config.php"; 
  /* [ERROR CHECKING] */ 
  if ($_FILES['upFile']['size']==0) { 
       die("No file selected"); 
  } 
  if (exif_imagetype($_FILES['upFile']['tmp_name'])===false) { 
       die("Not an image"); 
  } 
  /* [CONNECT TO DB] */ 
  $pdo = new PDO( "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET, 
                   DB_USER, 
                   DB_PASSWORD, 
                   [
                     PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                     PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                     PDO::ATTR_EMULATE_PREPARES => false
                   ]
       );

/* [INSERT IMAGE] */
// DO MORE ERROR CHECKING & HANDLING HERE IF YOU WANT
try {
	$stmt = $pdo->prepare("INSERT INTO `upload` (`name`, `data`) VALUES (?, ?)");
	$stmt->execute([$_FILES["upFile"]["name"], file_get_contents($_FILES['upFile']['tmp_name'])]);
} catch (Exception $ex) {
	echo "ERROR - " . $ex->getMessage();
	die();
}

/* [ON COMPLETE] */
// DO SOMETHING, MAYBE REDIRECT THE USER TO ANOTHER PAGE
// header("Location: http://example.com/abc.php");
echo "success";

Finally, we shall create the corresponding image upload handler in PHP. This upload handler is pretty straightforward:

  • Verify an image that is properly uploaded and avoid invalid uploading files.
  • Enable the database link. If you are not happy with the PDO, feel free to use MySQLi.
  • Read the image file saved as a BLOB into the database.

Retrieve Images

In order to complete the puzzle, we must just read the picture from the database and use it in this last phase in our HTML (or document).

HTML

<!DOCTYPE html>
<html>
<head>
    <title>Show Image</title>
</head>
<body>
    <!-- !! REMEMBER TO CHANGE ?f=IMAGE TO YOUR UPLOADED IMAGE FILE NAME !! -->
    <img src="fetch.php?f=image.jpg"> </body>
</html>

It’s just as easy to retrieve the image from the database. In your Markup, begin with a “Standard” tag <img> but guide it to a $GET[‘f’] script that defines the picture you wish to load.

PHP Script

<?php
/* [INIT] */
require "config.php";

/* [CONNECT TO DB] */
$pdo = new PDO(
  "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET, 
  DB_USER, DB_PASSWORD, 
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false]
);
 
/* [FETCH IMAGE] */
$stmt = $pdo->prepare("SELECT `data` FROM `upload` WHERE `name`=?");
$stmt->execute(array($_GET['f']));
$img = $stmt->fetch();

/* [OUTPUT] */
// DO MORE ERROR CHECKING & HANDLING HERE IF YOU WANT
// NOT FOUND - SHOW BLANK IMAGE
if ($img==false) { 
  require "404.jpg";
} else {
  echo $img['data'];
}
?>

This PHP script basically extracts and outputs the BLOB details from the database. Please apply the above code if protection and privacy are your concern.

4 COMMENTS

Leave a Reply