[RESOLVED] Storing Images in Database vs. Storing filepaths

AnarchistX

Wasn't sure where to post this question, so I put it in the newbie section.

I'm working on a project where I created a dynamic website, and give control of the website to a person who knows nothing about html and websites etc.. to edit as they please.

I basically created two sites, one for a viewer and one for an editor. The editor would login to the website, which was basically the orginal except forms were placed in where text and images would be.

They would type in whatever text they want, upload any pictures they want, and click submit and it would show up on the viewer side.

ANYWAys....

After going through the forums, I've come to realizing that uploading the images to a database as blogs are generally bad practice.

I'm not sure why though? The reason I read was that it was more efficient to store filepaths because when opening the page the browser would have to do less work.

Now I understand this for the viewer section of the website.

But for the editor side, they would have to upload an image from their computer and store it into the database. How would you store the image other then a blog onto the database. Filepaths lead to actual files stored somewhere. Soo. those files would have to be stored in a database.

I dunno, I feel like I'm asking a question that has an obvious answer. This is why I'm posting in the newbie section. Thanks to anyway for a reply, sorry for the newbish question.

Houdini

It is easier to merely store the filepath to a binary large object (BLO😎 and also when storing a binary you MUST use myql_real_escape_string() when storing them. Plus you have to retrieve them from the database and then present them by using just a filepath the file will be read faster from a folder just for them. Using folder is is easier to organize the images in to various categories, and why place any more load on the database than is necessary.

MarkR

I think it's easier to store BLOBs in the database.

My main argument is about ease of management. If your images are in the database, it's easier to backup / restore them in sync with the data, harder to have "orphaned" images, and requires less sys admin work generally.

Decisions should never be taken based on runtime performance unless:
- It is a real problem in a production environment
- You have benchmarked it in your real system and there is a significant difference between the approaches

Someone posting in the forums "It is more efficient if..." is not a reason to do it that way. Half the time they will be wrong anyway, and most of the rest, it doesn't matter or is insignificant.

As far as using "mysql_real_escape..." when storing, yes, but
- Only if you don't use a prepared query via (e.g.) mysqli, PDO, PEAR:😃B etc
- Surely you have to do that on all other data anyway?

Storing large images (too big to fit in memory, 10megs +) in BLOBs is inconvenient - if you plan to store them, I'd recommend using files instead.

Mark

leatherback

But for the editor side, they would have to upload an image from their computer and store it into the database. How would you store the image other then a blog onto the database. Filepaths lead to actual files stored somewhere. Soo. those files would have to be stored in a database.

If you use a filepath, it will lead to the filepath ON THE SERVER, so you upload the file, store it in a folder on the webserver, not in a database; not on the clients machine.

If you store it in the database, you generally link to a php file, which will pull the image from the folder, generate an appropriate image header, and spit out the image, so it can be viewed on the clients machine.

In principle both sides are valid, one is easier to manage full-site backups etc (storing in a database). However, you will be working with bulky databases. I generally get issues when trying to restore databases which are 10Mb++, as my provider does not allow a lot of server memory. I store files on the system, and use filepaths.

AnarchistX

Thanks a lot everyone for the replies, I think I will try to do what you said leatherback. Have them upload it into a directory on the server rather then the database. Eventually I will have to allow them to upload movies which will have the potential of being quite large.

As of right now though, I'm not clear on how to allow the user to upload into a directory vs a database. Yes, another noob question. I'm going to do some research on it, but if anyone can provide an example, that would save me some time. Thanks a lot.

AnarchistX

I did some quick searching on this site and I think I found what I needed:

http://phpbuilder.com/board/showthread.php?t=10322467&highlight=user+upload+directory

Thanks everyone for their help.

Sayian

If your satisfied, Mark this thread as resolved.

BTW; My personal advice to you is not to store files in the database.

Store a ref. to them in the database, and put the actual file in the filesystem.

Hence the name FILEsystem, heh.

Cheers

Rodney_H_

May I please tag on a little off-shoot question here? Hopefully AnarchistX won't mind...

My question is: When allowing uploaded files to a directory, and you are expecting images, what is the SAFEST approach to prevent users from taking advantage of this feature?

I heard that using File Type is not security enough. Can anyone shed light on this sub-topic for me? (Again, Sorry if this is innappropriate. I can always start another thread. It just seemed relevant here...)

AnarchistX

No problem Rodney, hopefully you'll get the response you need.