IMAGE in DATABASE

nicdp

Is it possible to store images directly in a database (MySQL)? How?

OR... do is putting the link in the database really the only solution?

lawnmowerman

One way that I have done it is to have a column in your table of datatype "mediumtext" (a max of 16MB of data), read your image into a variable, use addslashes() to escape any needed characters, and simply insert into the database.

Hope this helps!

-Rich

blue928

you have to do this by using the column type BLOB. I'm not an expert on the BLOB type, but the MYSQL documentation is really good as it helped me to store images in the database when i was playing around with it.

HOWEVER

there are some drawbacks. a mysql table can only hold about 4 gigs of data, and if you are storing a lot of files then this may be inconvenient for you.. as it was for me. to solve the problem, I found it much easier (and a whole lot faster in query time) to insert a hyperlink pointing to the picture on my harddrive (ie /images/pic.jpg) i have a classifieds section of my site where people can upload pictures, and at 1k pics a day, u run outa space quickly. hope this helps.

lawnmowerman

Actually, mediumtext is a essentially a blob type. A tinyblob/tinytext type has a max of 255 bytes. A blob/text type has a max of 65535 bytes (not large enough in case someone uploads an image larger than 64k). Mediumblob/mediumtext maxes out at 16MB (why I choose to use it). And, finally, longblob/longtext maxes out at 4.2GB (way too large for most images).

The only difference between blob and text is the case sensitivity used when sorting (text types are case INsensistive)

Also, table sizes in MySQL are limited by the filesystem. On my Linux box, I have a filesystem max file size of 2GB. Therefore, my tables have a max size of 2GB. From what I've heard, ReiserFS with Linux kernel 2.4 will allow you have files much larger than 2GB, so the barrier would be broken.

All of this information is presenting in the MySQL docs.

This all being said, I agree that if you plan to have many, MANY images, you would probably be better off storing the images in the filesystem and not in the DB...
but who am I to tell our good friend Nico that I won't help him since I don't think he should be doing what he wants to do laugh

Hope this helps!

-Rich