Quite a long question, but hopefully I made every point as clear as possible. I also hope that my question will be interesting and, who knows, challenging to some of you.
I have been asked to create a little app for a friend who wants to upload .pdf files. These files contain stories that he writes. He decided that he didn't want the files to be downloaded by anyone. So he just provides a little synopsis of the story, not the whole text.
Therefore, users will have to submit a form with a few info. He will then review the info and choose to send or not the .pdf file the user is interested in. If he agrees, he will send the file as an attachement to the email addy provided by the user. I obviously tried to tell him that it wouldn't stop people from copying his texts if they wanted to but well... he still wants to go that way.
In order to avoid the files to be downloaded, I thought about placing them above the webdir. Then I realized that it would become quite a hassle to manage all this (add new texts, delete, update, change titles etc.).
That's when I realized that storing the .pdf files in MySql might be a good solution to my problem. I know that usually it is not recommended to store byniary data in DBs but in this case it seems to be the appropriate solution.
So here are a few questions as I am not accustomed to such a process:
1. Is that definitly a bad idea to store the .pdf files in MySql?
2. I thought about designing my table like that:
fileName would be, for exemple, 'the_story_of_blah' and filetitle 'The Story of Blah'.
The fileName field would be used to trace the order (fileName will refer to the name of the file eg 'the_story_of_blah.pdf') and the filetitle field would be the text appearing on the html page (eg. are you interested in reading more about 'The Story of Blah'?).
Does that design look good?
3. About the upload. Should I create a little form to upload the file and choose the title of the file (I will automate the insertion of the data in the fileName field by removing uppercases and replacing ' ' with '_'). Tell me if I am going wrong here.
4. Sending emails. There will be only one file per email sent. So when the 'order' is processed, I will look at which file has been ordered by the user and retrieve it from the table storing all the .pdf files. Now here is a question that I can't solve. How can I link fileName with its actual content (fileBody)? In other words, when retrieving the data, how can I rebuild a .pdf file?
Once that done, I will just send the email with the .pdf file attached (I will take of MIME types and everything, don't worry )
Thanks in advance for your help