Is there a way to store an Image file ina mysql database

[cboston]

I am trying to make an addition to a contact information database where I work by making a picture available of each person in the database. Is there a way to store an image file such as a gif or jpeg in the database?

If so, how do you insert it?

Thanks,

Chris Boston

[killerbishop]

You could insert any arbitrary data in MySQL (any many other database implementations) by creating a field as type BLOB. Of course, dealing with binary data can be burden, so you might get around that by using base64 encoding. Read the MySQL manual for more information about BLOB fields.

[Wave]

what the point of base64 encoding?
addslashes is enough.
you just need to store mime-type, name and body, and send appropriate headers when outputs this file.

[[deleted]]

Remember two things:

1. The only way to read an image from a database is by running a seperate script. No big deal if you have just one image oper page, but it you decide to do an overview of 40 users, you get 40 of those scripts at the same time, using 40 database connections and running 409 queries at the same time. If 5 users do that at the same time, you get 5*40= 200 concurrent queries ie: your database is flooded.

2. Simple databases like MySQ: store the binary data inside the table itself. If you have 1000 users that all add an image of 100kb, you get a datafile of at least 1000*100kb=100MB. The database will ofcourse try to cache that and waste your ssytem memory with data you only access once a month 🙂

[Wave]

1. demagogy. you'll never get 200 queryes at a time with mid-populat site, even if you does, it will not overheat your base.

2. are you sure that a mysql cashing BLOB?

i think it's stupid. i'll read more about cashing in MYSQL this evening.

[[deleted]]

1. Unfortunately the web is riddled with 'too many connections' errors due to these kind of tricks. "it won't happen" is always wrong, it will happen, murphy says so.

2. I'm not sure, but it probably does (it's acrap DB after all). The fact remains that mysql stores it's blobs inside the table itself, creating a huge table. So if you don't have correct indices you'll get a sequencial scan, forcing the database to read the entire table. And at a 100MB that will take a while.

The simple solution is to store the images as normal fuiles on disk, and storing only the name an location of those files in the database. Then you can pass that location directly to the IMG tag in HTML, and the webserver will take care of the rest.

[Wave]

1. wise. you are right, we all must heavily optimize structure for BIG sites, but if your site have 100-200 visitors daily, it's not actual for you.

2. talking about it is useless, till one of us read mysql man and say - "mysql cashing..." or "mysql don't do it" 🙂

3. the best solution. can i shake your hand? 🙂

[Wave]

PS: wow. 9300 posts. you are monster! :eek:

Congratulations. Round Number 🙂

[[deleted]]

1. The thing is that it becomes 'actual' for you sooner than you'd like. Most sites are hosted on shared servers.
   Shared servers get many times the load that a dedicated server would get. On a dedicated server the system resources are all yours, but on a shared system you have maybe 10% of the resources for your tasks, so it is definately important not to waste too much CPU and memory just because it seems 'convenient' to store the images in the database.

2. MySQL's manual says:

"From version 4.0.1, MySQL server features a Query Cache. When in use, the query cache stores the text of a SELECT query together with the corresponding result that was sent to the client."

And it does not mention any details about blobs.

3. you could if your arm was long enough to reach all the way from where you are to where I am 🙂

[[deleted]]

PS: well I've been 'at it' for quite a while, since before Tim Perdue sold phpbuilder to internet.com (and dropped the phpbuilder community in deep shite because internet.com couldn't care less about the site. All they did was add more advertisements)

[killerbishop]

Hey vincente - your still around? ;-) The point of base64 encoding the data is to make it easier to transfer around - you'll almost never do that as raw binary. In addition - have you ever tried to do a manual SELECT on a binary field - that's probably going to screw up your tty while you are testing it.

I don't know what kind of server he is running his database on, it could be little endian versus big. Binary data is interpretted differently on these platforms. In addition - he didn't say he was going to use it for the web - so I assumed this was already thought out to be the best choice.

Vincente is right if your going to be using this for the web. The MySQL manual even suggests this, see:

http://www.mysql.com/doc/en/Tips.html

Which has a clause that claims, "When using a normal web server setup, images should be stored as files. That is, store only a file reference in the database. The main reason for this is that a normal web server is much better at caching files than database contents. So it it's much easier to get a fast system if you are using files."

[[deleted]]

"Hey vincente - your still around? ;-) The point of base64 encoding the data is to make it easier to transfer around -"

I idn't make the base64 comment 🙂

although I agree it's pretty pointless as you'll never willingly select a blob it anyway, and it adds another step to the already clumsy process of reading the data back from the database.

[greg252]

mySQL manual also says that for a more optimised system, store references to images in the table, not the image themselves.

More efficient.