PHP to Store Images in MySQL or Not

PHP to store images in MySQL or not?

Always depends of context, but usually, I store a user image on the filesystem in a folder called /content/user/{user_id}.jpg and try to bother the database as little as possible.

Can I store images in MySQL

Yes, you can store images in the database, but it's not advisable in my opinion, and it's not general practice.

A general practice is to store images in directories on the file system and store references to the images in the database. e.g. path to the image,the image name, etc.. Or alternatively, you may even store images on a content delivery network (CDN) or numerous hosts across some great expanse of physical territory, and store references to access those resources in the database.

Images can get quite large, greater than 1MB. And so storing images in a database can potentially put unnecessary load on your database and the network between your database and your web server if they're on different hosts.

I've worked at startups, mid-size companies and large technology companies with 400K+ employees. In my 13 years of professional experience, I've never seen anyone store images in a database. I say this to support the statement it is an uncommon practice.

Saving images to MySQL Database? Yes? Or not?

A few comments:

You seem to be using date('d.m.Y') I guess to get the current date. You're using it as a PHP function, but it's inside an SQL statement. I suggest you use CURDATE() if you put it inside the SQL statement. If you use the PHP function, do it outside the SQL statement and bind the result as a parameter.

Your code stores both the img content and the path to the tmpfile for the upload. I don't see the need for this. Pick one or the other. An uploaded tmpfile is removed at the end of the PHP request, so I don't know why you would store that path.

Don't use addslashes() for content you're binding to a query parameter. This will store the literal slashes in your content. Since your image file is not text, it will add slashes before binary bytes that happen to be the same as ASCII character \ (0x5C). You don't need slashes anyway when using bound parameters, even if it's for text.

See https://www.php.net/manual/en/mysqli-stmt.send-long-data.php for the right way to store larger data content via bound parameter. This allows files that are larger than MySQL's max_allowed_packet buffer, because it sends the content in chunks.

$query = "INSERT INTO `produkt` (`id`, `date`, `img`)
          VALUES('', CURDATE(), ?)";

// prepare query 
$stmt = $conn->prepare($query);

//  bind  params
$null = NULL;
$stmt->bind_param("b", $null);
$fp = fopen($_FILES['img']['tmp_name'], "r");
while (!feof($fp)) {
    $stmt->send_long_data(0, fread($fp, 8192));
}
fclose($fp);

// execution of the prepared statement
$stmt->execute();

Are you asking whether it's better to store the image or the path? There is no universal rule about this. Many developers would say, "never store big images and other files in the database" because it makes the database larger.

But there are good reasons to do that sometimes, such as:

If you delete a row, it will atomically delete the image stored on that row. Whereas if you store it outside the database, then you need to remember to delete any orphaned images when you delete a row in the database.
Data in the database obeys transaction semantics. If you insert/update/delete a row that references an image, the change is isolated until you commit. You may also choose to rollback the transaction. You can't do that with files on the filesystem.
A database backup will collect all the image data along with row data, and this will be atomic. If you keep image files outside the database, a data backup must be done in two steps, and you always worry if you got the images that are true for that database snapshot.

How to store images in mysql database using php

I found the answer, For those who are looking for the same thing here is how I did it.
You should not consider uploading images to the database instead you can store the name of the uploaded file in your database and then retrieve the file name and use it where ever you want to display the image.

HTML CODE

<input type="file" name="imageUpload" id="imageUpload">

PHP CODE

if(isset($_POST['submit'])) {

    //Process the image that is uploaded by the user

    $target_dir = "uploads/";
    $target_file = $target_dir . basename($_FILES["imageUpload"]["name"]);
    $uploadOk = 1;
    $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION);

    if (move_uploaded_file($_FILES["imageUpload"]["tmp_name"], $target_file)) {
        echo "The file ". basename( $_FILES["imageUpload"]["name"]). " has been uploaded.";
    } else {
        echo "Sorry, there was an error uploading your file.";
    }

    $image=basename( $_FILES["imageUpload"]["name"],".jpg"); // used to store the filename in a variable

    //storind the data in your database
    $query= "INSERT INTO items VALUES ('$id','$title','$description','$price','$value','$contact','$image')";
    mysql_query($query);

    require('heading.php');
    echo "Your add has been submited, you will be redirected to your account page in 3 seconds....";
    header( "Refresh:3; url=account.php", true, 303);
}

CODE TO DISPLAY THE IMAGE

while($row = mysql_fetch_row($result)) {
    echo "<tr>";
    echo "<td><img src='uploads/$row[6].jpg' height='150px' width='300px'></td>";
    echo "</tr>\n";
}

When to store images in a database(mySQL) and when not? (Linking image to item in database)

Advantages of storing images/blobs in the database

Storing the images is automatically part of your transactions
No need to cleanup the file system and sync it with the database
Access to the images is controlled through the same means as access to the rest of the data
Only one single backup to care about
A database is usually better suited to stored million (if not billions) of records. In the filesystem you have to find a clever distribution over several directories. A single directory will not be able to handle millions of files efficiently.
For some workloads storing the images in the database could actually be faster.
For SQL Server, Microsoft has actually tested this:
http://research.microsoft.com/apps/pubs/default.aspx?id=64525

I wouldn't be surprised if other databases behaved in a similar way.

Disadvantages:

Makes your database and thus your backups a lot bigger (think of the time it takes to restore it).
filesystems are usually better with incremental backups (unless you have Oracle's RMAN that is)
the images cannot be access by other applications (e.g. a WebServer, image resizing tools, FTP Server)
Images can't be spread across a content distribution network for load balancing in a web application (to take load off the application server)
common belief is, that the retrieval from the database will be slower.
for some databases retrieving the blobs might actually decrease the efficiency of the database cache (not so for Oracle, SQL Server and PostgreSQL. I don't know for MySQL though)

php:Store image into Mysql blob, Good or bad?

I have often built systems to store images in the database, there are pros and cons to doing this.

Pros:

All your data is kept in one place, if you migrate your website/database the images will just be there
Its easier to sort/delete/etc...
Since you have to serve it via a PHP script, you can perform additional things such as security if required, or image processing (obviously you can do this with flat file too, but you have to make sure the security cant be bypassed by leaving the images in a public directory).

Cons:

Its slower then serving a flat file from the webserver as a PHP script needs to retrieve it, and MySQL needs to return the data.
Your database will become large very fast and not all web hosts take too kindly to this.
The file system is faster for flat file storage and retrieval as thats exactly what a file system is designed for.

Storing and displaying images from MySQL with PHP

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

PHP to Store Images in MySQL or Not