user3304303 user3304303 - 3 years ago 173
MySQL Question

For image upload, should I add a field to the MYSQL database to check against, or simply use PHP to check if the image exists?

I have a simple image upload form. When someone uploads an image, it is for a football pool, so there always is a

$poolid
that goes with the image they upload.

Right now, I am naming the uploaded image using the poolid. So for example, if someone uploads an image, it might get named P0714TYER7EN.png.

All the app will ever do is, when it outputs the football pool's page, it will check to see if an image exists for that pool and if so, it will show it. It checks like this:

if (file_exists("uploads/".$poolid.".png")) { //code to show it }


My first thought when planning this was to add a field called "image" in my MYSQL database's table for all the pool information (called
pools
) and I would store a value of either the image name (P0714TYER7EN.png) or empty if there wasn't one uploaded. Then I would check that field in the database to determine if an image exists or not.

But I realized I don't really need to store anything in the database because I can simply use the PHP
file_exists
check above to know if there is an image or not.

In other words, it would seem redundant to have a field in the database.

Everything works doing it this way (i.e. NOT having a field in the database) but I'm wondering if this is bad practice for any reason?

If anyone feels that I should absolutely still have a field in the database, please share your thoughts. I just want to do it the proper way.

Thank you.

Answer Source

The approach could depend a lot on what exactly you're trying to do. Seems like the options you would have is:

File System Only

Benefits would be the speed of accessing static files of an image and use of it in your HTML directly which makes it a more simple solution. Also if you're comfortable with using these functions it will be faster to finish.

Drawbacks would be that you're limited to using file_exists and similar. Any code to manage files this way has to be very specific and static. You also can not search or perform operations efficiently on this. In general relying on the file system alone is not a best practice from my experience.

Database Only

Benefits, you can use Blob type as a column with meta data like owner, uploader, timestamp, etc. in the same row. This makes checking for existing files faster as well as any searching or other operations fast and efficient.

Drawbacks, you can't serve files statically using a CDN or even a cookie-less subdomain or other strategies for page performance. You also have to use PHP and MySQL to generate then serve any images via code rather than just referring to the image file directly.

Hybrid

Benefits, basically the same benefits as both above. You can have your metadata in MySQL with a MD5 hash and location of the file available as well. Your PHP then renders the page with a direct link to the file rather than processing the Blob to an image. You could use this in conjunction with a CDN by prefixing or storing the CDN location as well.

Drawbacks, if you manually changed names of files on the server you'd have to rely on a function matching hashes to detect this, though this would also affect a File System Only that needs to detect a duplicate file potentially.


TLDR; the Hybrid approach is what you'll see most software use like WordPress or others and I believe would be considered a best practice while file system only is a bit of a hack.

Note: Database only could be a best approach in specific situations where you want database clustering and replication of images directly in your database rather than to a file system (especially if the file system is restricted access or unable to be modified for any reason, then you have full flexibility on the DB).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download