Store and Display image from MySQL database
A basic approach to upload and save image to a MySQL database and then display the image from the database.
First you need to create a table in MySQL Database to store the image data. Log into you database and run the following sql command:
CREATE TABLE `images` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) default NULL,
`size` int(11) default NULL,
`type` varchar(20) default NULL,
`content` mediumblob,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
Once the table have been created, we are ready to write codes. Now create a folder in you webserver named rayImg and create image.php and upload.php files inside the folder.
Open the image.php, copy and paste the following code and save it.
File: image.php
<?php
/**
* Display image form database
*
* Retrive an image from mysql database if image id is provided.
*
* @example to display a image with image id 1, place <img src="image.php?id=1" > in your html file.
*
* @author Md. Rayhan Chowdhury
* @copyright www.raynux.com
* @license LGPL
*/
// verify request id.
if (empty($_GET['id']) || !is_numeric($_GET['id'])) {
echo ‘A valid image file id is required to display the image file.’;
exit;
}
$imageId = $_GET['id'];
//connect to mysql database
if ($conn = mysqli_connect(’localhost’, ‘root’, ‘root’, ‘test’)) {
$content = mysqli_real_escape_string($conn, $content);
$sql = “SELECT type, content FROM images where id = {$imageId}”;
if ($rs = mysqli_query($conn, $sql)) {
$imageData = mysqli_fetch_array($rs, MYSQLI_ASSOC);
mysqli_free_result($rs);
} else {
echo “Error: Could not get data from mysql database. Please try again.”;
}
//close mysqli connection
mysqli_close($conn);
} else {
echo “Error: Could not connect to mysql database. Please try again.”;
}
if (!empty($imageData)) {
// show the image.
header(”Content-type: {$imageData['type']}”);
echo $imageData['content'];
}
?>Now open the update.php, copy and paste the following code and save it.
File: upload.php
<?php
/**
* Upload an image to mysql database.
*
*
*
* @author Md. Rayhan Chowdhury
* @copyright www.raynux.com
* @license LGPL
*/
// Check for post data.
if ($_POST && !empty($_FILES)) {
$formOk = true;
//Assign Variables
$path = $_FILES['image']['tmp_name'];
$name = $_FILES['image']['name'];
$size = $_FILES['image']['size'];
$type = $_FILES['image']['type'];
if ($_FILES['image']['error'] || !is_uploaded_file($path)) {
$formOk = false;
echo “Error: Error in uploading file. Please try again.”;
}
//check file extension
if ($formOk && !in_array($type, array(’image/png’, ‘image/x-png’, ‘image/jpeg’, ‘image/pjpeg’, ‘image/gif’))) {
$formOk = false;
echo “Error: Unsupported file extension. Supported extensions are JPG / PNG.”;
}
// check for file size.
if ($formOk && filesize($path) > 500000) {
$formOk = false;
echo “Error: File size must be less than 500 KB.”;
}
if ($formOk) {
// read file contents
$content = file_get_contents($path);
//connect to mysql database
if ($conn = mysqli_connect(’localhost’, ‘root’, ‘root’, ‘test’)) {
$content = mysqli_real_escape_string($conn, $content);
$sql = “insert into images (name, size, type, content) values (’{$name}’, ‘{$size}’, ‘{$type}’, ‘{$content}’)”;
if (mysqli_query($conn, $sql)) {
$uploadOk = true;
$imageId = mysqli_insert_id($conn);
} else {
echo “Error: Could not save the data to mysql database. Please try again.”;
}
mysqli_close($conn);
} else {
echo “Error: Could not connect to mysql database. Please try again.”;
}
}
}
?>
<html>
<head>
<title>Upload image to mysql database.</title>
<style type=”text/css”>
img{
margin: .2em;
border: 1px solid #555;
padding: .2em;
vertical-align: top;
}
</style>
</head>
<body>
<?php if (!empty($uploadOk)): ?>
<div>
<h3>Image Uploaded:</h3>
</div>
<div>
<img src=”image.php?id=<?=$imageId ?>” width=”150px”>
<strong>Embed</strong>: <input size=”25″ value=’<img src=”image.php?id=<?=$imageId ?>”>’>
</div>
<hr>
<? endif; ?>
<form action=”<?=$_SERVER['PHP_SELF']?>” method=”post” enctype=”multipart/form-data” >
<div>
<h3>Image Upload:</h3>
</div>
<div>
<label>Image</label>
<input type=”hidden” name=”MAX_FILE_SIZE” value=”500000″>
<input type=”file” name=”image” />
<input name=”submit” type=”submit” value=”Upload”>
</div>
</form>
</body>
</html>Replace line
mysqli_connect(’localhost’, ‘root’, ‘root’, ‘test’)
with
mysqli_connect(’your host’, ‘your username’, ‘your password’, ‘your database name’)
in both the file and save again.
You are done. Open upload.php from your browser and upload you desired image and view the uploaded image.
