I’m looking for suggestions (and some help) on the best way to handle file handling with PHP & MySQL.
The site requires projects to be uploaded by specific logged in users and submitted to the admin dept. The admin dept to then see the results on a table showing who’s uploaded what file, for admin to then download the file, review, re-upload the file & assign it to a specific user.
I was thinking, on the front end, the user fills a form, attaches the file and submits the form & file (using longblob for file?) to a table in the db.
On the admin side, they can then see the user by ID & name (SQL inner join required) and what file they have uploaded. Admin then download the file, review & adjust and then re upload the file and assign it to that specific user. Is there a better way of doing this?
Its imperative that the users files aren’t seen by other users.
Well for one I wouldn’t use php. I would use a more modern set of technologies like node js and express. I also wouldn’t store those files on app server or in the database. I would store them in the cloud on a cdn like s3. The meta data and relation / ownership of files I would either use a relational database or NoSQL like mongo which performs and scales much better / easier than mysql. Also I would built it using micro-service, cloud architecture. Create the front-end using modern JavaScript MVVM framework such as; Angular, React, or Vue. Also cloud environments such as AWS provide authentication and authorization services themselves. So if you go that route you might be able to handle ownership and permissions through the services provided by the cloud vendor.
Whilst that is a great suggestion Zee, unfortunately I haven’t yet dabbled in any of those frameworks. And to be honest it sounds as though I would end up having to re build most of the site just for the file system. They’re definitely on my list to learn though.
Surely this can be done with PHP? Oh and just to throw a spanner in the works, the user will need to pay for the amended file via a virtual terminal before they can download the file.
In terms of qty of users, currently there are around 15-20 but this will grow and grow as more users join the service.
Those are two separate problems, don’t try to solve them as one. First implement what you’ve described in your OP, and only once that working add payments to it as an additional step. When you try to build both at once it will be a lot harder because concepts will get conflated.
PHP can easily handle that.
It sounds like that assignment could be done automatically; if you know which user the downloaded file belonged to, you can assign it to that user on upload, saves the admin a manual action.
As for the approach, what you’ve described in your first post sounds fine, although BLOB should be enough to store files instead of LONGBLOB.
Great point on the payment side. I’ll make sure the file exchange is working as it should first.
What’s the best way for admin to assign directly to the user? Currently I have setup a form from the front end that the user inputs a few fields and uploads a file. This then goes onto a table in the db.
On the admin side the data in the db table is then displayed on a html table. Are you thinking having another column for admin to the upload the file back to that table? I’m not sure on the best way to handle this?
