file system based references

[zerostar]

Heya peeps

kind of a noobDev here so please bear with me.

I'm working on a system where we have many files in various directories and sub directories that we want to make available to users as lists and links to download the files. (WAMP/Symfony mixture on the build, btw)

I have setup some tables in our DB and I was wondering a couple of things:

1. How do I reference the files location on the drive from within the database? I am guessing that I should copy the physical location (i.e. C:\wamp\docs\marketing\peeps\ etc.) to a LONGTEXT or LONGVHARCHAR column, but I want to make sure I format it correctly
2. There are many various document types and many different file sizes involved (ranging from around 100k to 200mb). What is the best way to set these as downloadable links? Do I have to include a column for mime_type and/or file_size? If so, what are the attributes for those columns and can those attributes be automatically pulled from the files themselves, or do I have to manually enter that data in the database?
3. My idea is to have all the docs referenced in one table, with a foreign-key pointing to a media_category table which references the category (or path attributes i.e. c:\wamp\docs\marketing\peeps\old versions would be input as peeps, old versions into the media_category table. The Marketing tag would come from a separate foreign-key referencing the dept_name table).Is this the most efficient way to do this, or am I prepping overKill?
4. If I do enter all of the paths manually into the 'location' column, do I need to input %20's for spaces in the DIR names, or will that be parsed correctly through the front end?

Any other thoughts on setting this type of system up would be greatly appreciated as well. Thanks so much for your time!

[zerostar]

bumpsies?

[orjan]

The most important thing is to escape the backslashes when you store the path to the database.

about substituting paths, you should url-encode your paths and filenames
but then there is the question if it's better to encode it to the db or when presenting it to the user. it depends on how you will treat the filepaths later in your code.

i hope for your security reasons that you don't think about doing like this:

"download.php?file=c:\wamp\docs\marketing\peeps\ol d\file.mp3"

but instead "download.php?fileid=23" where 23 is the id in your database reffering to the file, as in the first case, someone could rewrite the address to "download.php?file=c:\my\secret\files\passwords.tx t" and get whatever file they want from the server.

[zerostar]

thank you for the tip Orjan

I will employ your ideas into my process for sure.