Logging downloads into user accounts in MySQL database

[divingdave]

Hi there,

 

This is my first post and I'm a relative newcomer to PHP and MySQL so please excuse any mistakes I might make! Hope someone can help with some advice here please...

 

I've built a user registration system (in PHP) that allows people to create an account, log-in, and then download PDF files that are not available to unregistered users. I've now been asked to add a download logging feature that will record which PDF file has been downloaded together with the date and the users IP address each time they download a PDF. This information shouldn't just be a separate long list of all the downloads that have taken place by all users - instead it should store this information directly in each users account inside the database, so it is part of the account information. For example, in my MySQL database I already have:

 

Forename: John

Surname: Smith

Company: John Smith Ltd

Username: johnsmith

Password: 123456

 

I now want to add a new column called 'Downloads'. And inside this column I want to add the following sort of information:

 

Filename.pdf,DD/MM/YYYY,255.255.255.255

 

This is basically the name of the PDF file, the date it was downloaded and the users IP address. I don't just want to store this once and keep overwriting it with the most recent download - I want it to be a complete list of all downloads made by that user since their account was created. So in theory I may have a list of 20 downloads they've made, storing the same information about the download in the same way every time. I'm planning to use commas as the delimiter to separate each bit of data, and then I'll use a different delimiter (such as a hash or vertical pipe) to separate each log. Like this:

 

Filename.pdf,01/01/2008,255.255.255.255#Filename2.pdf,02/01/2008,255.255.255.255#Filename3.pdf,03/01/2008,255.255.255.255

 

And so on.

 

So my question is - what is the best MySQL data type (e.g. TEXT, LONGTEXT, etc.) to hold this kind of information (as it could potentially be quite a long list if the user has downloaded lots of files). And how should I store it in the MySQL database? Should I be separating each download with commas or some other delimiting character so they can be nicely separated into individual records when I display them later? Or is there a better way of setting this up?

 

A bit more info: I already have the PHP forced download script working so this ensures I can capture the PDF filename as part of my script, rather than just supplying a direct URL to the PDF (which wouldn't be as easy to capture without building some sort of forwarding script).

 

Any help, suggestions or guidance would be greatly appreacited.

 

Thanks,

Dave

[fenway]

Add another table, called downloads... store the user id, the file id, and the date/time.  Don't store delmited values in the database!  You may want to read up on DB normalization, see the board stickies.