How to Store and Retrieve Images from a MySQL Database Using PHP

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

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);

How to retrieve images from MySQL database and display in an html tag

You can't. You need to create another php script to return the image data, e.g. getImage.php. Change catalog.php to:

<body>
<img src="getImage.php?id=1" width="175" height="200" />
</body>

Then getImage.php is

<?php

$id = $_GET['id'];
// do some validation here to ensure id is safe

$link = mysql_connect("localhost", "root", "");
mysql_select_db("dvddb");
$sql = "SELECT dvdimage FROM dvd WHERE id=$id";
$result = mysql_query("$sql");
$row = mysql_fetch_assoc($result);
mysql_close($link);

header("Content-type: image/jpeg");
echo $row['dvdimage'];
?>

Record and Read an image file in the database . Using PHP and MYSQL

Here goes a very basic example:

MySQL

CREATE TABLE 'picture' (
'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
'image' LONGBLOB NOT NULL,
PRIMARY KEY ('id')
)

PHP

$image = file_get_contents('myimage.jpg');
// your db connection code here..
mysql_query("INSERT INTO picture (image) VALUES ('$image');

Bare in mind storing images in the database is not a very good plan. It is better to keep the images in a directory and just save the path in the database.

Storing and Retrieving image path in database mysql php

Since, you didn't described your question properly or didn't provided any code.
So, i assumed your question in my way and posted my answer.
Follow the step.

1) For Uploading Image

<form method='POST' action='UploadImage.php' enctype="multipart/form-data">
<input type='file' name='UploadImage'>
<input type='submit' value="submit">
</form>

Suppose your Project Folder Name Is : MyProject (Where all project files are present),
Make one folder inside "MyProject" Folder namely "MyUploadImages"
Now,

UploadImage.php

<?php
include('connect.php'); // Do Database Connection in this file (create a file namely connect.php inside MyProject Folder)
extract($_POST);

$UploadedFileName=$_FILES['UploadImage']['name'];
if($UploadedFileName!='')
{
$upload_directory = "MyUploadImages/"; //This is the folder which you created just now
$TargetPath=time().$UploadedFileName;
if(move_uploaded_file($_FILES['files']['tmp_name'], $upload_directory.$TargetPath)){
$QueryInsertFile="INSERT INTO TableName SET ImageColumnName='$TargetPath'";
// Write Mysql Query Here to insert this $QueryInsertFile .
}
}
?>

Now, In your Database Table, you can find ImageColumnName that image path is set as MyUploadImages/1417Flower.jpg

2) Retreiving Image from database

AnyPage.php

<?
$Query="SELECT * FROM TableName";
// Write mysql query to fetch $Query

store that ImageColumnName value to any variable say $MyPhoto.
?>

<img src="<?echo $MyPhoto;?>">

How to store images in mysql database using php

I found the answer, For those who are looking for the same thing here is how I did it.
You should not consider uploading images to the database instead you can store the name of the uploaded file in your database and then retrieve the file name and use it where ever you want to display the image.

HTML CODE

<input type="file" name="imageUpload" id="imageUpload">

PHP CODE

if(isset($_POST['submit'])) {

//Process the image that is uploaded by the user

$target_dir = "uploads/";
$target_file = $target_dir . basename($_FILES["imageUpload"]["name"]);
$uploadOk = 1;
$imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);

if (move_uploaded_file($_FILES["imageUpload"]["tmp_name"], $target_file)) {
echo "The file ". basename( $_FILES["imageUpload"]["name"]). " has been uploaded.";
} else {
echo "Sorry, there was an error uploading your file.";
}

$image=basename( $_FILES["imageUpload"]["name"],".jpg"); // used to store the filename in a variable

//storind the data in your database
$query= "INSERT INTO items VALUES ('$id','$title','$description','$price','$value','$contact','$image')";
mysql_query($query);

require('heading.php');
echo "Your add has been submited, you will be redirected to your account page in 3 seconds....";
header( "Refresh:3; url=account.php", true, 303);
}

CODE TO DISPLAY THE IMAGE

while($row = mysql_fetch_row($result)) {
echo "<tr>";
echo "<td><img src='uploads/$row[6].jpg' height='150px' width='300px'></td>";
echo "</tr>\n";
}

Storing and displaying images from MySQL with PHP

<?php

# getting the uploaded image and storing it
if ( isset($_FILES['image']['tmp_name']) ) {
// open mysqli db connection
$mysqli = new mysqli($mysqliHost,$mysqliUsername,$mysqliPassword,$mysqliDatabase);

// get image data
$binary = file_get_contents($_FILES['image']['tmp_name']);

// get mime type
$finfo = new finfo(FILEINFO_MIME);
$type = $finfo->file($_FILES['image']['tmp_name']);
$mime = substr($type, 0, strpos($type, ';'));

$query = "INSERT INTO `images`
(`data`,`mime`,`name`)
VALUES('".$mysqli->real_escape_string($binary)."',
'".$mysqli->real_escape_string($mime)."',
'".$mysqli->real_escape_string($_FILES['image']['name'])."')";
$mysqli->query($query);
}

# viewing the uploaded image
if ( isset($_GET['imageName']) ) {
// open mysqli db connection
$mysqli = new mysqli($mysqliHost,$mysqliUsername,$mysqliPassword,$mysqliDatabase);

// query for the image in the db
$query = "SELECT `data`,`mime` FROM `images` WHERE `name`='".$mysqli->real_escape_string($_GET['imageName'])."'";
$result = $mysql->query($query);


if ( $result->num_rows ) {
// grab the query result from the db select
$assoc = $result->fetch_assoc();

// let the client browser know what type of data you're sending
header('Content-type: '.$assoc['mime']);

// dump the binary data to the browser
echo $assoc['data'];
exit;
} else {
header('HTTP/1.1 404 Not Found');
exit;
}
}

?>

My script does not account for images with the same name, you can swap out the part where it says $_FILES['image']['name'] to another variable that has/creates a unique name for it, or use the inserted ID (PRIMARY AUTO_INCREMENT MySQL key).

Here is a sample table schema:

CREATE TABLE `images` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`data` longblob NOT NULL,
`mime` varchar(50) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Uploading and retriving image from mysql database

It's not best practice to store an image in a database, as the database will get very big, and slow. It's better if you just store the path to the image file in the server.

You can upload images and save them in your server very easily with PHP. Since it's out of the scope of this question I'll leave you some tutorials:

https://davidwalsh.name/basic-file-uploading-php
http://www.w3schools.com/php/php_file_upload.asp

And to answer your actual question, you're not saving the image, but the image name, you should use $_FILE and not $_POST when we're dealing with file uploads in PHP.



Related Topics



Leave a reply



Submit