Well working with images is quite easy task in MySQL using php code. Some years back managing images in relational database is quite complex task as at those times relational databases are able to store only textual data so file path to the images are stored in database and images are stored and retrieved externally. Also special file functions are necessary for retrieving images this way and this approach is system dependent (because of path names used). Nowadays, almost all major DBMS support storing of images directly in database by storing images in the form of binary data. Here, I am explaining the method of storing and retrieving images in MySQL database using PHP code.
Inserting images in mysql-:
MySQL has a blob data type which can used to store binary data. A blob is a collection of binary data stored as a single entity in a database management system. Blobs are typically images, audio or other multimedia blob objects. MySQL has four BLOB types:
- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB
All these types differ only in their sizes.
For my demonstration, lets us create a test table named test_image in MySQL having 3 columns show below-:
- Id (INT) -Act as primary key for table.
- Name (VARCHAR) – Used to store image name.
- Image (BLOB) – Used to store actual image data.
You can use phpMyAdmin tool to create the above table else use the following MySQL query-:
create table test_image ( id int(10) not null AUTO_INCREMENT PRIMARY KEY, name varchar(25) not null default '', image blob not null );
PHP code to upload image and store in database-:
To upload the image file from client to server and then store image in MySQL database on server, I am posting here the PHP code for our test/sample table (test_image).
Please change the values of variables in file_constants.php file according to your system. Save the following scripts with names as shown in your web directory.
file_constants.php
<?php $host="your_hostname"; $user="your_databaseuser"; $pass="your_database_password"; $db="database_name_to_use"; ?>
file_insert.php
<html>
<head><title>File Insert</title></head>
<body>
<h3>Please Choose a File and click Submit</h3>
<form enctype="multipart/form-data" action=
"<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<input type="hidden" name="MAX_FILE_SIZE" value="10000000" />
<input name="userfile" type="file" />
<input type="submit" value="Submit" />
</form>
<?php
// check if a file was submitted
if(!isset($_FILES['userfile']))
{
echo '<p>Please select a file</p>';
}
else
{
try {
$msg= upload(); //this will upload your image
echo $msg; //Message showing success or failure.
}
catch(Exception $e) {
echo $e->getMessage();
echo 'Sorry, could not upload file';
}
}
// the upload function
function upload() {
include "file_constants.php";
$maxsize = 10000000; //set to approx 10 MB
//check associated error code
if($_FILES['userfile']['error']==UPLOAD_ERR_OK) {
//check whether file is uploaded with HTTP POST
if(is_uploaded_file($_FILES['userfile']['tmp_name'])) {
//checks size of uploaded image on server side
if( $_FILES['userfile']['size'] < $maxsize) {
//checks whether uploaded file is of image type
//if(strpos(mime_content_type($_FILES['userfile']['tmp_name']),"image")===0) {
$finfo = finfo_open(FILEINFO_MIME_TYPE);
if(strpos(finfo_file($finfo, $_FILES['userfile']['tmp_name']),"image")===0) {
// prepare the image for insertion
$imgData =addslashes (file_get_contents($_FILES['userfile']['tmp_name']));
// put the image in the db...
// database connection
mysql_connect($host, $user, $pass) OR DIE (mysql_error());
// select the db
mysql_select_db ($db) OR DIE ("Unable to select db".mysql_error());
// our sql query
$sql = "INSERT INTO test_image
(image, name)
VALUES
('{$imgData}', '{$_FILES['userfile']['name']}');";
// insert the image
mysql_query($sql) or die("Error in Query: " . mysql_error());
$msg='<p>Image successfully saved in database with id ='. mysql_insert_id().' </p>';
}
else
$msg="<p>Uploaded file is not an image.</p>";
}
else {
// if the file is not less than the maximum allowed, print an error
$msg='<div>File exceeds the Maximum File limit</div>
<div>Maximum File limit is '.$maxsize.' bytes</div>
<div>File '.$_FILES['userfile']['name'].' is '.$_FILES['userfile']['size'].
' bytes</div><hr />';
}
}
else
$msg="File not uploaded successfully.";
}
else {
$msg= file_upload_error_message($_FILES['userfile']['error']);
}
return $msg;
}
// Function to return error message based on error code
function file_upload_error_message($error_code) {
switch ($error_code) {
case UPLOAD_ERR_INI_SIZE:
return 'The uploaded file exceeds the upload_max_filesize directive in php.ini';
case UPLOAD_ERR_FORM_SIZE:
return 'The uploaded file exceeds the MAX_FILE_SIZE directive that was specified in the HTML form';
case UPLOAD_ERR_PARTIAL:
return 'The uploaded file was only partially uploaded';
case UPLOAD_ERR_NO_FILE:
return 'No file was uploaded';
case UPLOAD_ERR_NO_TMP_DIR:
return 'Missing a temporary folder';
case UPLOAD_ERR_CANT_WRITE:
return 'Failed to write file to disk';
case UPLOAD_ERR_EXTENSION:
return 'File upload stopped by extension';
default:
return 'Unknown upload error';
}
}
?>
</body>
</html>
Below is screenshot of above web page when executed by browser-:
With this you will be able to upload and store images in MySQL database. Also check for the presence of file in the database using phpMyAdmin or any other tool.
Displaying images stored in MySQL-:
Now we are in a position to write PHP code to see images stored by the above script. For that firstly save the script below with name file_display.php in your web directory.
file_display.php
<?php
include "file_constants.php";
// just so we know it is broken
error_reporting(E_ALL);
// some basic sanity checks
if(isset($_GET['id']) && is_numeric($_GET['id'])) {
//connect to the db
$link = mysql_connect("$host", "$user", "$pass")
or die("Could not connect: " . mysql_error());
// select our database
mysql_select_db("$db") or die(mysql_error());
// get the image from the db
$sql = "SELECT image FROM test_image WHERE id=" .$_GET['id'] . ";";
// the result of the query
$result = mysql_query("$sql") or die("Invalid query: " . mysql_error());
// set the header for the image
header("Content-type: image/jpeg");
echo mysql_result($result, 0);
// close the db link
mysql_close($link);
}
else {
echo 'Please use a real id number';
}
?>
Now you can see the images stored in the database using the following query string-:
http://{path_to_your_web_directory}/file_display.php?id=1
You will see image which is stored in database corresponding to id having value “1” . You can use different numbers to see different images. Below is a sample screenshot-:
Thinking Out of Box 