CODEWITHSUNDEEP
phpmysqlsql
Paolo Porcellato
Paolo Porcellato

Reputation: 51

What is the best method to manage the image saved in SQL for a website?

I have to manage many photo of user's cooking recipes, profile picture etc...

At the moment I'm working on the website using a local XAMPP server: the image are storage into a folder on the directory of the project and the referenced link is insert with SQL into the DB. When needed, I do a query to obtained the URL of the photo.

If, in the future, I decide to buy a web hosting, where I can save the user's image that they upload?

Upvotes: 4

Views: 107

Answers (1)

Blag
Blag

Reputation: 5894

What do I get when I buy a "server"

As said in comment, if you take a "real hosting" (not a packed "wordpress solution") it'll be the same as your local development machine.

You'll have :

  • a folder /www/ you can access through FTP (sometime SSH),
  • a phpMyAdmin web interface enabled at a given URL,
  • a mySql database you can access from the phpMyAdmin or from your code with the credential given.

How to handle picture size ?

Pictures are usually big, so how can I guess the size I'll need to store them ?

You're not the first one who have to store user picture : think of Facebook, Reddit and lot of other services.

The key point is they don't store pictures "as it", they use two factor to keep the size under control :

  • the actual size, in pixel, of the picture. (You usually don't need a 5'000 x 3'500px picture -apart for printing A0 posters- but that what camera will give you)
  • the format and compression factor.

For the first point, a 2'000px is already around a 4K display height (~2,2Kpx). For web purpose, 500px was used since age, but as screen got better, the 1Kpx and 2Kpx height seem to be the standard. Chose what fit your use.

For the second, you probably know PNG and JPG format. The first is a "lossless" compression and the second is a "lossy" one. This mean if you want to save space, you'll have to trade off quality of your picture, and go for a more or less compressed JPG.

After these, you should be under 500Ko per picture, so for your use case about recipes, that mean at least 2'000 recipes with a picture each before reaching the 1Go milestone...

File storage / DB storage

Sometime you may hear of "storing picture in database". It's an other solution than keeping a name or id-based path to a storage folder for your picture. With this, you can directly store the binary data of the picture in a column of your DB (see how to store / how to display).

So, what the good solution ? I'll say both :

  • On the good side, storing in the DB is the easiest way to deal with picture, as they are literally part of the line that keep user info. You can retrieve them without bothering about folder and path, they'll be in your DB backup too and will be delete along the user line in the DB.
  • On the down side, it's not really what DB are for. Picture are usually really big (DSLR can go up to 10Mo : size of 10'000'000 characters) and it'll slow down your query. It may be poorly cached if you're not careful.

Upvotes: 3

Related Questions