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, Mar 28 '13 at 00: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, Mar 28 '13 at 00: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, Mar 28 '13 at 00: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, Mar 28 '13 at 00: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, Mar 28 '13 at 00:23
@MikeBrant the client will utilize caching, no problem there. having a column with a filesystem string "pointer" is actually more risky: you fragment your data, if you need to migrate it, you'll end up having to move both pieces which incurs more risk. the images SHOULD be included in the backups anyway - what's the point of backing up a database without the actual content? lastly, performance is not an issue on the db side since blobs and text cols are not physically stored in the row (lookups will not be any slower than if the binary was omitted) — zamnuts, Mar 28 '13 at 01:53
http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay — zamnuts, Mar 28 '13 at 01:57

score 6 · Accepted Answer · edited Feb 18 '19 at 15:14

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

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, Mar 28 '13 at 00:52
This example may not be the simplest one to accomplish what I wanted to do, but it has everything that I needed to use. — user937450, Mar 28 '13 at 01:09
@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! — zamnuts, Mar 28 '13 at 01:28
@user937450 Yes it's safe. I uploaded the file and then displayed it correctly. Thank you. — user937450, Mar 28 '13 at 01:45

score 1 · Answer 2 · answered Mar 28 '13 at 00:10

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 '13 at 00:10

Sven

69,403
10
107
109

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. – Sven Mar 28 '13 at 00:12
This example doesn't say much to me, can you add the part where you retrieve the file from the form? – user937450 Mar 28 '13 at 00:35
`$image_file_name = $_FILES['image']['tmp_name'];` – Sven Mar 28 '13 at 00:42

score 1 · Answer 3 · edited May 23 '17 at 11:46

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 '17 at 11:46

Community

1
1

answered Mar 28 '13 at 00:12

Superlandero

692
6
11

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 Mar 28 '13 at 00:14
Im not a pro programmer, what would ne the best way to get an unique ID for multiple files? – user937450 Mar 28 '13 at 00:20
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"? – Sven Mar 28 '13 at 00:22
@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. – user937450 Mar 28 '13 at 00:25

score 0 · Answer 4 · answered Mar 28 '13 at 00:12

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.

score 0 · Answer 5 · answered Mar 28 '13 at 00:18

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 '13 at 00:18

James

3,957
4
37
82

Storing and displaying images from MySQL with PHP

5 Answers5