How to display an BLOB image stored in MySql database?

Question 1

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;

?>

Question 2

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."' />";
}

Question 3

Ok i found out the solution, using data URI. If you are in trouble, go here for more info: php: recreate and display an image from binary data.

Question 4

You only want to store the relative path, not the absolute path, as linking to something like

 <img src="/var/www/vhosts/website.com/images/file.jpg">

would return a 404 on any real website. store your files in the database via a relative path (/images/file.jpg) or by only the filename if they are all in the same directory.

alternatively, you can learn MongoDB and it allows you to actually store files in the database itself.

Question 5

Related question:

How to retrieve data from Firebase using Javascript?

I only get one result from that database:

c20ej76jhb
45.546058,-122.813203

Using this code (uses on rather than once) with a reference to a database I created with multiple locations (and a different format of data):

var locationsRef = firebase.database().ref("locations");
locationsRef.on('child_added', function(snapshot) {
var data = snapshot.val();
var marker = new google.maps.Marker({
  position: {
    lat: data.lat,
    lng: data.lng
  },
  map: map
});
bounds.extend(marker.getPosition());
marker.addListener('click', (function(data) {
  return function(e) {
    infowindow.setContent(data.name + "<br>" + this.getPosition().toUrlValue(6) + "<br>" + data.message);
    infowindow.open(map, this);
  }
}(data)));
map.fitBounds(bounds);

});

proof of concept fiddle

from your database:

from mine (with multiple locations and the modified code above):

code snippet:

function initialize() {
  var infowindow = new google.maps.InfoWindow();
  var map = new google.maps.Map(
    document.getElementById("map_canvas"), {
      center: new google.maps.LatLng(37.4419, -122.1419),
      zoom: 13,
      mapTypeId: google.maps.MapTypeId.ROADMAP
    });
  // Initialize Firebase
  var config = {
    apiKey: "AIzaSyC8HcClvIT_FlG8ZwCji4LG-qNx8D9VCrs",
    authDomain: "geocodeziptest.firebaseapp.com",
    databaseURL: "https://geocodeziptest.firebaseio.com",
    projectId: "geocodeziptest",
    storageBucket: "geocodeziptest.appspot.com",
    messagingSenderId: "1096545848604"
  };
  firebase.initializeApp(config);
  //Create a node at firebase location to add locations as child keys
  var locationsRef = firebase.database().ref("locations");
  var bounds = new google.maps.LatLngBounds();
  locationsRef.on('child_added', function(snapshot) {
    var data = snapshot.val();
    console.log(data);
    var marker = new google.maps.Marker({
      position: {
        lat: data.lat,
        lng: data.lng
      },
      map: map
    });
    bounds.extend(marker.getPosition());
    marker.addListener('click', (function(data) {
      return function(e) {
        infowindow.setContent(data.name + "<br>" + this.getPosition().toUrlValue(6) + "<br>" + data.message);
        infowindow.open(map, this);
      }
    }(data)));
    map.fitBounds(bounds);
  });
}
google.maps.event.addDomListener(window, "load", initialize);

html,
body,
#map_canvas {
  height: 100%;
  width: 100%;
  margin: 0px;
  padding: 0px
}

<script src="https://maps.googleapis.com/maps/api/js?key=AIzaSyCkUOdZ5y7hMm0yrcCQoCvLwzdM6M8s5qk"></script>
<script src="https://www.gstatic.com/firebasejs/4.12.1/firebase.js"></script>
<script src="https://cdn.firebase.com/libs/geofire/4.1.2/geofire.min.js"></script>
<div id="map_canvas"></div>

Question 6

A possible way to solve this problem is to have a separate script to dynamically output the contents of the image eg. :

image.php

header('Content-type: image/jpg');

// DataBase query and processing here...

echo $data['myImage'];

and call it whenever you need to show images stored in your DB eg. inside your loop:

echo '<img src="image.php?id=' . $data['id'] . '">';

But storing images in the database will take a toll on your server and unless they're really small or you have a good reason to do so, you should only store their physical location on the disk.

You can also use this approach if you wish to hide image location from your users, or control access, but there are better and faster alternatives for that case.

How to display an BLOB image stored in MySql database?

Answers

php,html,mysql,image,blob