MySQL - storing images in a database??

tags:

mysql
php

views:

143

answers:

MySQL - storing images in a database??

Lets say each row of our table contains information about various vehicles for sale. And each user can store several images of each vehicle. What might be a good approach to storing these images?

Duplicate: User Images: Database or filesystem storage?
Duplicate: Storing images in database: Yea or nay?
Duplicate: Should I store my images in the database or folders?
Duplicate: Would you store binary data in database or folders?
Duplicate: Store pictures as files or or the database for a web app?
Duplicate: Storing a small number of images: blob or fs?
Duplicate: store image in filesystem or database?

+1 A:

Sounds like homework. You’d want a one-to-many relationship from the vehicles table to the pictures table. The pictures table would contain a BLOB column with the images. Or it could contain a VARCHAR column with the filenames of the images as stored on disk.

Nate 2009-08-10 22:09:20

IMHO the database is the wrong place to store images. just store the location of the image.

Rufinus 2009-08-10 22:12:06

I agree, most of the time. I think this is homework and the OP needs to be aware of the two common ways of doing this.

Nate 2009-08-10 22:16:55

you can use a single folder, the database should know which files are owned by which user, so its your code who's decide what can be overwritten by a user and what can't.you can store the images with the PK ID + filetype extension, or as a MD5 hash or ..... many many ways, but dont store them with the orginal filename. this makes far more problems (spaces in filename, specialchars.. aso.)

Rufinus 2009-08-10 22:19:46

@DJDonaL3000: Most filesystems will slow down if you store too many images in a single folder. One way to deal with this is to use a hierarchical folder structure. For example, if your images are named 100.jpg, 101.jpg, 225.jpg, and so on, create a folder /images/100 and store images 100-199 there. Create a folder /images/200 etc. Another approach is to use [hash buckets](http://www.mondofacto.com/facts/dictionary?hash+bucket) where each bucket corresponds to one folder in the filesystem.

Nate 2009-08-10 22:21:32

An alternate approach would be to use something like Amazon S3, where you can put an unlimited number of uniquely-named images in a single bucket and then refer to them by URL in your web page.

Nate 2009-08-10 22:22:44

The overhead of storing the image data directly in the database can become an issue, especially for large sites with a lot of traffic.

Usually I have a naming convention for storing the images on the server, and the file names are re-written from whatever the user uploads, but you can retain the original file name from the user if you prefer - just make sure to sanitize them. The database only stores metadata for the images, so that requests for the image files can be handled directly by the webserver.

GApple 2009-08-10 22:20:43

ansaurus

tags:

views:

answers:

MySQL - storing images in a database??

related questions