PHP SQL File Exchange Between Clients & Admin

Hi All

I’m struggling to wrap my head around how best to do this…

I’m trying to build a site where members can upload project files to the server and admin can download them on the other side modify them and then assign them to the relevant member and return the amended file. However i need to keep the files related to each member and for other members not to see eachothers files.

The bit i’m struggling to understand is how i would go about this. I have a table of members in my DB. but would i need to setup a separate table for the files?
How would i link this to the member?

Thanks in advance all.

Yes, a separate table for files with a foreign key relating to the file owner.

1 Like

Thanks Sam.

Please can you explain in a bit more detail as to how i would go about that? i.e foreign key relating to file owner.

Cheers

Your members table shold have a unique primary key to ID each menber.
The file table will have the same to ID each file, but the file table can also have an owner_id column which links the file to its owner. The basics of relational databases.

Then for a simple example, when a user goes to a page with a list of available files, you can have a query like:-

SELECT file_id, filename FROM files WHERE owner_id = :member_id

They will only see their own files.

Taking it further, foreign keys wil also allow for Joins in queies.

1 Like

Thanks Sam. That makes sense. I’m familiar with relational databases as i’m using them elsewhere in the site but i just couldn’t think how i would display the files on the member and admin pages.

Thanks for the quick response. Much appreciated.

When you do this though, make sure to mark owner_id as a foreign key. That way if someone tries to remove the member when they still have files, the DELETE query will fail, preventing you to get orphaned files in the database.

Or you could set it up such that if the member is deleted all their files are deleted as well, but that’s not the default behaviour.

1 Like

Thanks rpkamp. :+1:t2: