How to display an BLOB image stored in MySql database?

Question

I am trying to display the last 5 images uploaded to my "store" table in MySql. I'm a complete noob to PHP and databases and i've been reading a lot on how to do this but no luck.

I can store and display pictures one at a time but i'd like to be able to have a gallery of sorts to show the last 5 uploaded.

any advice or help would be greatly appreciated thanks!

p.s. I know it frowned upon to store pictures to a database like this but this project is just for practice.

index.php

<!DOCTYPE html>
<html>
<head>
<title>Project One</title>
</head>

<body>

<form action="index.php" method="POST" enctype="multipart/form-data">
    File:
    <input type="file" name="image"> <input type="submit" value="Upload">
<form>
<p />

<?php

//connect to database
(connect to server)
(select correct DB)

//file properties
$file = $_FILES['image']['tmp_name'];

if (!isset($file))
    echo "please select an image.";
else
  {
  $image = addslashes(file_get_contents($_FILES['image']['tmp_name']));
  $image_name = $_FILES['image']['name'];
  $image_size = getimagesize($_FILES['image']['tmp_name']); 

  if($image_size==FALSE)
    echo "That's not an image.";
  else
  {
    if (!$insert = mysql_query("INSERT INTO store VALUES ('', '$image_name', '$image')"))
        echo "Problem Uploading Image.";
    else
        {

        $lastid = mysql_insert_id();
        echo "Image uploaded. <p />Your image:<p /><img src=get.php?id=$lastid>";

        }
  }
  }

?>

<p />
<p />
<a href="http://WEBSITE.com/gallery.php"> Go to Gallery </a>
</body>

</html>

get.php

<?php

   //connect to database
    (connect to server)
    (select correct DB)

$id = addslashes($_REQUEST['id']);

$image = mysql_query("SELECT * FROM store WHERE id=$id");
$image = mysql_fetch_assoc($image);
$image = $image['image'];

header("Content-type: image/jpeg");

echo $image;

?>

Check this : http://stackoverflow.com/questions/118144/whats-the-most-efficient-way-to-select-the-last-n-rows-in-a-table-without-chang — Vucko, Nov 03 '12 at 23:48

Manatax · Accepted Answer · 2012-11-04T01:13:21.260

7

This is what I used when I wanted to do something like that... a long time ago! =P

$sql = "SELECT image FROM table WHERE cond ORDER BY xxxx DESC LIMIT 5";
$result = mysqli_query($db,$sql);
while($arraySomething = mysqli_fetch_array($result))
{
    echo "<img src='php/imgView.php?imgId=".$arraySomething."' />";
}

edited Nov 04 '12 at 01:13

answered Nov 03 '12 at 23:57

Manatax

4,083
5
30
40

Is there more to it than that? I tried adding this in and it prints a broken image... how should I go about creating a loop that will print each picture counting down from the row's max value? for 1..5 loop 'prints image with max_id' then decrements from max_id... something along those lines – Ktmock13 Nov 04 '12 at 00:21
My example was not for direct copy/paste I was just showing you how to add one. What you can do is basically add a LIMIT of 5 to your query and order it to show the last 5. Then do something like this: $sql = "SELECT * FROM table WHERE cond LIMIT 5"; $result = mysqli_query($db,$sql); while($arraySomething = mysqli_fetch_array($result)) { // echo img here } – Manatax Nov 04 '12 at 01:07

score 7 · Answer 2 · edited May 12 '14 at 08:27

I try the first approach with header('content-type: image/jpeg'); but end up with image not shown. After a few google through website I found the solution which I can display image from database to my page

try this:

mysql_connect("localhost","root","")or die("Cannot connect to database"); //keep your db name
mysql_select_db("example_db") or die("Cannot select database");
$sql = "SELECT * FROM `article` where `id` = 56"; // manipulate id ok 
$sth = mysql_query($sql);
$result=mysql_fetch_array($sth);
// this is code to display 
echo '<img src="data:image/jpeg;base64,'.base64_encode( $result['image_file'] ).'"/>'

Warning mysql_query was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. — Manatax, Nov 25 '17 at 08:21

score 2 · Answer 3 · answered Oct 08 '15 at 06:16

2

mysql_connect("localhost","root","")or die("Cannot connect to database"); 

//keep your db name
mysql_select_db("example_db") or die("Cannot select database");

$sql = "SELECT * FROM `article` where `id` = 56"; 
// manipulate id ok 
$sth = mysql_query($sql);
$result=mysql_fetch_array($sth);
// this is code to display 

echo '<img src="data:image/jpeg;base64,'.base64_encode( $result['image_file'] ).'"/> width="xxxx" height="xxxx"';

Add the height and width also

answered Oct 08 '15 at 06:16

Indeewara Jayalath

21
1

1

Warning mysql_query was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. – Manatax Nov 25 '17 at 08:20

score 1 · Answer 4 · answered Apr 07 '19 at 13:39

You can also use this function

//Retrieve image from database and display it on html webpage    
function displayImageFromDatabase(){    
//use global keyword to declare conn inside a function    
global $conn;    
$sqlselectimageFromDb = "SELECT * FROM `imageuploadphpmysqlblob` ";    
$dataFromDb = mysqli_query($conn, $sqlselectimageFromDb);    
while ($row = mysqli_fetch_assoc($dataFromDb)) {    
echo '<img height="250px" width="250px" src=data:image;base64,'.$row['image'].'/>';    
}

Insert it into mysql database like this :

$image = $_FILES['imagefile']['tmp_name'];
$name = $_FILES['imagefile']['name'];
$image = base64_encode(file_get_contents(addslashes($image)));

references : https://mauricemutetingundi.blogspot.com/2019/04/how-to-upload-blob-image-to-mysql.html

How to display an BLOB image stored in MySql database?

4 Answers4

Linked