How to Store Images in MySQL

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.

What is the best way of storing image in mysql db

Don't store your image in a blob or anything.
Save the images somewhere on disk, with a reference to the image location in the database.
This way it is a lot more flexible and your database size stays small.

(Assuming that you're using it for a website) it is also faster, since it reduces database requests. If you store the image as a blob the browser would send a http-request for each image, each resulting in a query to the database. When storing it on disk the webserver handles it directly (as a static file) without the need to request the database, so you'd have the advantage of Nginx (if using it) being able to handle static files fast.

Storing images in MySQL

Is there a particular reason why you can't store a reference to the image, rather than the actual image?

Images are big—they make databases big. SQL can be painful (as it's not object-oriented) and doesn't have an image type.

You could store them as a BLOB... if you want to write all the coding, encoding, checking, etc.

How to store images in Mysql table ?

You can always store the file path for where the image is located, instead of actually storing the image itself.

What is the efficient way to store an image in database?

option 2 for me because, for what I have read from other articles, converting the image to blob would make the process slower. unlike on storing the path you just retrieve it's path string to the database and retrieve much faster.

here are other related articles to that Storing Images in DB - Yea or Nay?

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.

MySQL Server storing images

You could store the images in another location such as your computer or a webserver if its for a website while storing their links in the database.