PHP to store images in mysql or not?

Question

I have built a small web application in PHP where users must first log in. Once they have logged in, I intend on showing a small thumbnail as part of their "profile".

I will have to ensure the image is below a particular size to conserve space, or ensure it is a particular resolution, or both, or even perhaps use something like image magick to scale it down.
Not sure what the best approach for that is yet, any ideas welcome.

Also, I have been trying to work out if it is better to store the image in the "users" table of mysql as a blob, or maybe a separate "images" table with a unique id, and just store the appropriate image id in the "users" table, or simply save the uploaded file on the server (via an upload page as well) and save the file as theUsersUniqueUsername.jpg Best option?

I found a tutorial on saving images to mysql here: http://www.phpriot.com/articles/images-in-mysql

I am only a hobby programmer, and haven't ever done anything like this before, so examples, and/or alot of detail is greatly appreciated.

Thanks, occhiso

Answer 1

I would recommend storing the image as a file and then have the file URI in the database. If you store all the images in the database, you might have some problems with scaling at a later date.

Check out this answer too:

Microsoft's advice for SQL Server used to be, for speed and size, store images in the file system, with links in the database. I think they've softened their preference a bit, but I still consider it a better idea certainly for size, since it will take up no space in the database.

Answer 2

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.

Answer 3

I recently saw this tip's list: http://www.ajaxline.com/32-tips-to-speed-up-your-mysql-queries

Tip 17: For your web application, images and other binary assets should normally be stored as files. That is, store only a reference to the file rather than the file itself in the database.

So just save the file path to the image :)

Answer 4

I think that most database engines are so advanced already that storing BLOB's of data does not produce any disadvantages (bloated db etc). One advantage is that you don't have any broken links when the image is in the database already. That being said, I have myself always done so that I store the file on disk and give the URI to the database. It depends on the usage. It may be easier to handle img-in-db if the page is very dynamic and changes often - no namespace -problems. I have to say that it ends down to what you prefer.

Answer 5

We created a shop that stored images in the DB. It worked great during development but once we tested it on the production servers the page load time was far too high, and it added unneccessary load to the DB servers.

While it seems attractive to store binary files in the DB, fetching and manipulating them adds extra complexity that can be avoided by just keeping files on the file system and storing paths / metadata in the DB.

This is one of those eternal debates, with excellent arguments on both sides, but for my money I would keep images away from the DB.

Answer 6

I would suggest you do not store the image in your db. Instead since every user will be having a unique id associated with his/her profile in the db, use that id to store the image physically on the server.

e.g. if a user has id 23, you can store an image in www.yourname.com/users/profile_images/23.jpg. Then to display, you can check if the image exists, and display it accordingly else display your generic icon.

Answer 7

As the others suggested:

• Store the images in the filesystem
• Do not bother to store the filename, just use the user id (or anything else that "you already know")
• Put static data on a different server (even if you just use "static.yourdomain.com" as an alias to your normal server)

Why ?

The bigger your database gets the slower it will get. Storing your image in the database will increase your database size. Storing the filename will increase your database size.

Putting static data on a different server (alias):

• Makes scaling up a lot easier
• Most browsers will not send more than two requests to the same server, by putting static data on a "second" server you speed up the loading

Answer 8

As everybody else told you, never store images in a database. A filesystem is used to store files -> images are files -> store them in filesystem :-)

Answer 9

1. What's the blob datatype for anyway, if not for storing files?
2. If your application involves authorisation prior to accessing the files, the changes are that you're a) storing the files outside of DOCUMENT_ROOT (so they can't be accessed directly; and possibly b) sending the entire contents of the files through the application (of course, maybe you're doing some sort of temporarilly-move-to-hashed-but-publicly-accessible-filename thing). So the memory overhead is there anyway, and you might as well be retrieving the data from the database.
3. If you must store files in a filesystem, do as Andreas suggested above, and name them using something you already know (i.e. the primary key of the relevant table).

Answer 10

The overhead using BLOB is a lot less than most people would have you believe, especially if you set it up right. If you use a separate server just running the DB to store binary files then you can in fact use no file-system at all and avoid any overhead from the file-system

That said the easiest/best way unless you have a couple of servers to yourself is storing them in the filesystem

• Do not store the absolute URL of the file in your DB, just the unique part (and possibly a folder or two), e.g. '2009/uniqueImageName.jpg' or just 'uniqueImageName.jpg' Then in your pages just add the host and other folders onto the front, that way you have some flexibility in moving your images - all you'll need to change is a line or two in your PHP/ASP.net page

• There is no need to store outside the document root for security - a .htaccess file with DENY FROM ALL will work the same and provide more flexibility

• No need to 'shunt' images so much for security, just have a getImage.php page or something, and then instead of inserting the actual URL in the src of the image, use something like getImage.php?file=uniqueImageName.jpg Then the getImage.php file can check if the user is authorised and grab the image (or not)

• Use a name which is guaranteed to be unique (preferably an integer i.e. primary key) when storing, some file-system (i.e. Windows) are case-insensitive, so JoeBloggs.jpg and joebloggs.jpg are unique for the database, but not for the file-system so one will overwrite another

• Use a separate table for the images, and store the primary key of the image in the users table. If you ever want to add more fields or make changes in future it will be easier - it's also good practice

If you are worried about SEO and things like that, store the image's original file name in another field when you are uploading, you can then use this in your output (such as in the alt tag)