PDF Microsoft Word File storage

[mickallen]

Can any one please give me any advice as to the best way to store files such as PDF or Microsoft Word files, so they can be downloaded from my web site.
Would it be better to store the files on the filesystem with a reference to the file within the MySQL database or would it be better to store the files within the database.

 

[Hokey]

I have a intranet web server that stores pictures for our PR department. I ran across the same questions while building a indexing database for these files and found that storing the files in the MySQL database had much slower response times than just storing the files on the file system and then refrencing them from within the database. The response time is slower because you still have to save the blob out of the database into a web accessable folder and then the browser downloads that temp file. (you also want to have a cleanup routine that will delete these temp files)

There are trade-offs for each method:
Storing the files into a database as blobs will probably be eaiser file management especially with a large amount of files. But this way will be slower file access times (we are talking a second or two longer with light access loads) and will take up more drive space with having to store temp files.
Storing the files into the file system with a refrence in a database will have much faster access times especially with a high server load, but the file management will not be as easy (and could get messy with tens of thousands of files or files that change versions often).

I have currently opted to save the files into the database just because of the volume of pictures that they have and that there is only three people accessing these files on a limited basis. They can handle that extra second per picture transfer time to save me the heartache of cleaning up after them. If this was for a busy Web server with a only a few hundred files, then I would just save them on the file system.

 

[mickallen]

Hokey

Thanks for the advice, initially there will only be a few hundred files, so it seems as though my best option will be to store these in the file system.

Thanks

 

[doddo]

Hi,
I am glad that I have found this thread. Very useful. I am a newbie to mySQL, so 2 silly questions. How do you reference files from the database and how do you store the files in the database?

thanks in advance
Rich

 

[Hokey]

I use MySQL only with Apache/PHP ... so I am not sure on the exact way to perform this outside PHP scripting, but here is how I have done it.

SQL statement to insert a file:
"INSERT INTO tablename SET blob_column=LOAD_FILE "/tmp/file.name&quot WHERE IDkey = 'idnum';"

SQL statement to extract a file:
"SELECT blob_column INTO DUMPFILE "file.name" FROM tablename WHERE IDkey = 'idnum';"

 

[doddo]

Hokey, thanks for that. Sounds like you did a similar thing to what i'm trying to do. I would like to open the files from the browser. So what you've described above is how to store and retreive the files when stored in the database.
The alternative is referencing. Could you explain how you would reference it? would this just be the path to the file stored as a field in the db?

thanks
rich

 

[Hokey]

The file refrence field would be a text string that contains the web path to the file. If your apache htdocs folder is /usr/local/apache/htdocs and the path to your files is /usr/local/apache/htdocs/images

Then the refrence field in the database would contain:
"/images/pictureXXXX.jpg".

<IMG SRC=&quot;<?=$file_refrence?>&quot;>

 

[doddo]

Yes, thought so, thanks.
I have been able to load and then dump word files however there are other application files that i cannot get in and retrieve. Are there restrictions on the types of files. The entry is not going in as NULL it is just blank

cheers
Rich

 

[Hokey]

There shouldnt be any file type restrictions. Are the files that do not dump in have any other characteristics that are different than the files that do? Size, permissions, long file names, etc...