Page 1 of 1

help with retrieval of binary data from mysql db

Posted: Thu Jan 11, 2007 7:40 am
by konstandinos
hi all

i am storing binary data in mediumblob columns in my table. the uploaded files can be of any type, including (but not limited to) image/jpg, application/pdf, text/plan, etc.

please bare in mind that i am well aware of the advantages of using the filesystem for file uploads. in this scenario however, i need to store my files in the database. moving on...

when i display the contents of the table, i want to display/make-downloadble the file (blob) in each row. now each row has a column called file_type, which stores the file's Content-type (ie: image/jpg).

now if its an image, i want the image to be displayed right there in the table listing.

if the file is of some other type besides image or plain text, i want the user to just get a link to the file, that allows them to click on it which prompts for a download of the file (for pdf and word documents).

how do i achieve this?

i already have image displaying working with the <img src=... /> tag attribue calling a separate script that sets the header content-type and prints the file data, but this involves dumping the binary data into a file on the server first, which i am trying to avoid.

thanks :)

Posted: Thu Jan 11, 2007 10:23 am
by feyd
You'll have to look at the content type stored.

Images will need you to output an image tag and reference information needed for a script to find the image (previously passed by getimagesize().) Plain text will just be output (after passing it through htmlentities() or htmlspecialchars() and nl2br().) Other document types will be linked to similar to the image tag script.

Posted: Thu Jan 11, 2007 10:53 am
by konstandinos
thanks for the info feyd.

you never fail to help.

cheers

Posted: Thu Jan 11, 2007 11:27 am
by Kieran Huggins
I would second guess your desire to store files in a database. While it is possible (and I've done it in the past) it's not necessarily a good idea.

Why not store your files on the file system instead? Name them according to their sha1 values and then store that sha1 in the database instead. Much easier - I promise!