Store images in MySQL as BLOB and show in PHP as filename.jpg

Posted under » PHP » MySQL on 30 May 2017

Sometimes we need to look for pictures but it's kinda hard to find. So it wouldn't be good to store it in a database?

The binaries can be stored in a blob. To put in the form,

<input name="pix" type="file" />

On the PHP side.

if(is_uploaded_file($_FILES['pix']['tmp_name'])) {
$imgData =addslashes(file_get_contents($_FILES['pix']['tmp_name']));
$sql = "INSERT INTO images(pix) VALUES('{$imgData}')";

Now lets see it ... but first you must create the image. Because it is not jpg, you must translate the binary into jpg so that your browser can see it. Lets call it gambo.php

$result = mysqli_query($dbhandle,"SELECT pix,did FROM images where did = $id"); 
header('Content-Type: image/jpeg');
while ($baongs = mysqli_fetch_object($result)) { echo $baongs->pix; }

That if you just want to see one only. But what if you want to see it in a html page?

while ($ba0ngs = mysqli_fetch_object($result)) {
   $html .= "<br><img src=gambo.php?p=".$ba0ngs->did.">" ; }
echo "<p>".$html;

So the url in src doesn't appear like an image and it is making a direct query to the db which using valueable resources. It is more efficient to save it to a file and then display it. Or you can have several servers as load balancing but the source storage is to the MySQL table.

$url_to_img = '';
$my_save_dir = 'downloaded-images/';
$filename = "filename.jpg";
$complete_save_loc = $my_save_dir . $filename;
file_put_contents($complete_save_loc, file_get_contents($url_to_img));

Just make sure you have already created the "downloaded-images" folder and the owner is www-data or if not, group can write to the folder.

See also file uploader.

Note : It is not recommended to store large images (mediumblob < 16mb). Video (longblob < 4gb) is also not practical.

web security linux ubuntu GIT svn Raspberry apache mysql php drupal cake symfony javascript Ajax css