Storing and displaying images from MySQL with PHP

Question

How can I store a image file in MySQL with PHP by sending the image from a HTML form? I only know the MySQL and HTML part of the stuff.

Here's the HTML form:

<form method="post" enctype="multipart/form-data" action="insert_image.php">
    <input type="file" name="image" />
    <input type="submit" />
</form>

I know how to connect to the database and store normal information, but how can I parse the data correctly to store the image file to a MySQL BLOB field? And also how can I display it from MySQL?

ps: Im using PDO to do the database connections.

It's usually easier to store the file in the filesystem and simply store the path in the DB. — Phil
– Phil, Commented Mar 28, 2013 at 0:11
Do you have a compelling use case for actually storing the image in the database? Probably 99/100 times it is better to store the images in the filesystem. — Mike Brant
– Mike Brant, Commented Mar 28, 2013 at 0:14
@Mike Bran Yes I do, performance is not an issue at all and I won't have to write code to organize the files and folders, since the database already has it's relationships and stuff. — user937450
– user937450, Commented Mar 28, 2013 at 0:17
Performance is always an issue. Think about it when you are forced to refactor your original design back to store into the filesystem again... :) — Sven
– Sven, Commented Mar 28, 2013 at 0:19
@user937450 That doesn't sound compelling to me, it sounds lazy. You will be hating this decision once your database reaches a certain size and you need to create database backups. It also means you are likely to not be able to provide any form of caching to your browser, forcing database calls everything a user tries to view an image. To me unless you are doing things like binary searches on the image content, you would always be better off using file references in the database. — Mike Brant
– Mike Brant, Commented Mar 28, 2013 at 0:23

RiggsFolly · Accepted Answer · 2019-02-18 15:14:50Z

6

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

edited Feb 18, 2019 at 15:14

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

answered Mar 28, 2013 at 0:34

zamnuts

9,6123 gold badges42 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user937450 Over a year ago

Im checking your example right now, I was able to succesfuly upload my image, now I will check if I can view the image.

user937450 Over a year ago

This example may not be the simplest one to accomplish what I wanted to do, but it has everything that I needed to use.

zamnuts Over a year ago

@user937450 i actually left things out for the sake of simplicity :( make sure you're checking that the queries are executed successfully, and i'd split it up into two files. for the most part, this should be pretty safe. let me know if you require further explanation, glad you could figure it out!

user937450 Over a year ago

@user937450 Yes it's safe. I uploaded the file and then displayed it correctly. Thank you.

Sven · Accepted Answer · 2013-03-28 00:10:21Z

1

Its the same for anything you store in the database: Grab the bytes you want to store and escape them when adding them to the query string.

This is like:

$image = file_get_contents($image_file_name);
$query = "INSERT INTO img SET image = \"".mysqli_real_escape_string($image)."\"";

answered Mar 28, 2013 at 0:10

Sven

71.2k10 gold badges113 silver badges113 bronze badges

3 Comments

Sven Over a year ago

And please consider saving it into the local filesystem because that is way easier. The file system is a specialized database for storing large binary blobs. The path is the unique ID to access them.

user937450 Over a year ago

This example doesn't say much to me, can you add the part where you retrieve the file from the form?

Sven Over a year ago

$image_file_name = $_FILES['image']['tmp_name'];

Community · Accepted Answer · 2017-05-23 11:46:02Z

1

Really the best practices says than you need store the image in some folder and save the path or the name in the database.

this could help you: http://www.htmlgoodies.com/beyond/php/article.php/3877766/Web-Developer-How-To-Upload-Images-Using-PHP.htm

Saving image from PHP URL

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Mar 28, 2013 at 0:12

Superlandero

6926 silver badges11 bronze badges

4 Comments

user937450 Over a year ago

I did some research about that. Im developing a small system, so in my case it's worth it to lose some performance to have more organization.

user937450 Over a year ago

Im not a pro programmer, what would ne the best way to get an unique ID for multiple files?

Sven Over a year ago

Every pro programmer is telling you to use the filesystem. :) The path and filename is the unique id for a file. How would you store multiple images in your database? Have a multi-image-table like "ID, header-image, footer-image, content-image"?

user937450 Over a year ago

@Sven Instead of placing the file path will just store the file itself, with relationships I will store multiple files for a single company for example.

Explosion Pills · Accepted Answer · 2013-03-28 00:12:19Z

0

You can actually get the raw contents of the uploaded image file with file_get_contents, escape it, and then store it in the DB as a blob. To display it you can actually just echo out the blob after setting the appropriate content-type.

However, I wouldn't do it that way. Instead I would store the image on the disk and store the location of the image file in MySQL.

answered Mar 28, 2013 at 0:12

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Comments

James · Accepted Answer · 2013-03-28 00:18:49Z

0

Personally when I made a site that needed something like this (this site) I stored the files in the file system and added the directory to the database, ensuring that each jpg had a unique filename.

answered Mar 28, 2013 at 0:18

James

4,0704 gold badges44 silver badges88 bronze badges

Collectives™ on Stack Overflow

Storing and displaying images from MySQL with PHP

5 Answers 5

4 Comments

3 Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

3 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related