[SOLVED] Handling Images .... OS file or MySQL storage

[PhpTim]

I have the requirement to allow my users the ability to upload a file ... and attach the image to there profile.

 

This is not something I am expeiranced in myself so I have security concerns.

 

Researching on the internet I see some people have

 

allowed a user to just upload an image on the filesystem.

 

or

 

some people seem to encode images and put them into the database.

 

Anyone have any preferences?  Or have any reason to perfer one over another?

 

 

[hitman6003]

File system is usually better.  If the db is on a different server, it must cross the network twice, additionally, db storage is almost always more expensive than web storage.

 

If you use a blob field in you db, you must also be more careful with your SQL...doing

 

SELECT * FROM users ORDER BY username WHERE create_date = TODAY()

 

or any other SELECT that includes the blob field with an ORDER BY will force mysql to do a file sort which is much much slower.

[dbo]

I'd disagree. It's a matter of preference really. The one valid comment is that it could be slower if on a different server. As far as the SELECT * stuff goes.... really you should never do this, regardless of scenario.

 

If you choose the filesystem method you can secure things as well... but most of which requires additional configuration through .htaccess files and what not. I'd much rather just manage everything for code, otherwise when you transfer hosts you have to remember to go through extra configuration steps to keep things secure, etc.

 

But anyways, you'll get a lot of arguments both ways. Try them both out... do some reading... and ultimately make your own decision that works best for you.

[jvrothjr]

In this case of files linked to user ID I would do this in a binary table.

I prefer this to managing the files themselfs, plus you would need to link the file name to the user anyway just add an extra field or in my case another table linked to the user ID table itself.

 

This will also allow multi users to have the same filename or use the same image as well as allowing the user to upload own image or use one from the public list thus not having multi images of the same thing.

 

With that said you could have private and public photos/icon. Private only for the user that uploaded it and pubil open to all users.

 

I use the database option for my photo album and limit the files size to 1MB.  I display mine to the user as thumbnail autocreated on upload. this is also stored in a table. thus I have three tables for my function

1 - information data storage

2 - thumbnail with ID link

3- orig photo with ID link

 

only displaying the thumbnail saving Bandwidth.

[MadTechie]

WooHoo another FS vs. DB war..

 

I'm on the ......erm......  FS (FileSystem) side,

 

upload a folder thats outside the public_html folder and read it in via a php script, of course this script can check the database first including the filename/path.

 

but again personal preferance.. had a few sleepless nights with DBFS (DataBase FileSystem)

 

 

EDIT: one good/bad thing about DBFS is you can do one backup of the database and have all the sites details include photo etc and the static stuff like background and code doesn't change so only need to back that up when you upgrade.. bad news the DB will be much larger

[dbo]

Database being bigger doesn't bother me. That's what they're for And yeah a single backup is nice. If you've got your code files and your DDL you can pick up your site and move it easily. If you're trying to manage a test and a production environment you'll appreciate this.

[MadTechie]

Dang.... i scored and own goal on that one, LOL

[dbo]

Woot! You're the best player on my team, haha.

[jvrothjr]

thats why I have mine set that way for ease of back up nightly. We have 2200+ photos in the DB right now.

 

1 - Data Table - 45mb

2 - Thumbnail table - 115mb

3 - Photo table - 336mb

 

just add them three to the nightly sql dump and there backed up on tape.

 

Now the data files them selfs are stored in a Dir structure of over 2gb (word,exel,etc....)

but that was due to history of the old system. would have been nice to see the impack of storing them in the DB as binary but not going to rebo something thats working right now.

 

if it aint broke dont fix it they always say.

[MadTechie]

But to access the images is quicker when using a file system.. and less stress on the database.. thus making it faster than the DBFS.. why put all your eggs in one basket share the work load.. if you create users folder and have the files stored in their and have a cron to dump the mysql to a folder its just one backup

 

this post could last for days LOL

[dbo]

Argh, the speed won't be that noticable! And and you put your eggs all in one basket so you can manage one basket not 15! But yeah... could definately go on for a long long time.

[MadTechie]

what about debugging.. and filetypes,sizes etc.. the FILEsystem was designed for this very reason, use the correct tool for the correct job.. putting all your eggs in one basket maybe easier to manage, but is it the right way to manage ???

 

OK.. i'm done with the battle (its been fun) .. truly theirs a lot of things to think about your need to weight all the pros and cons..

 

waits for dbo's last battle cry lol

[dbo]

Hah! Whenever inserting/updating to a BLOB you can capture its filetype, its size, filename, etc. Furthermore it's a lot easier to maintain a level of version control with a database. It doesn't take long for a filesystem to grow to a point that it's a headache to maintain.

[PhpTim]

Well see what are the pros and cons?

 

 

[PhpTim]

This has been turned out to be a decent post.

 

Thanks to everyone who added to this post.

 

I still haven't decided which method whether file system or database storage.

 

I feel I am leaning to file system ... originally I was just worried about redundant images sitting on my server.

 

but I think if I keep things really structured with good naming conventions I shouldn't have issues.

 

I will let you guys know what I decide in the end.

 

 

[dbo]

You're leaning towards the dark side young skywalker!

[MadTechie]

More like your learning fast young grass hopper

[dbo]

* sigh... shakes his head *

[PhpTim]

Things wouldn't be so hard if you both didn't make such good arguments ha ha!

 

See either are plausible ... I am fully able to create a new MySQL database and this could solely be used for the purpose of images if desired.

 

Mind you I am not using this for all the images on the site.

 

Only for managing users images.

 

Out of interest I am slightly baffled on the process of extracting the images from the database and showing them on site.

 

It would be for a listings page where 20 items could be listed with 1 image each.

 

What drew me to liking the database method at the beginning of this post was the ease of keeping things tidy where a listing becomes redundant the image will also be removed ensuring efficiency in the long run.

 

 

[MadTechie]

Out of interest I am slightly baffled on the process of extracting the images from the database and showing them on site.

 

you would have a php script that acts as the image, you would then pass the image id to that file ie

<img src='imager.php?id=12'>

 

that script will search the database and extract that blog and using the header and imagejpg funcitons that script will become the image

 

thats a quick summery

[PhpTim]

Thank you I really appreciate this ...

 

So filesystem ... is there any security concerns not addressed within this script?

 

upload form

<html>
<body>
  <form enctype="multipart/form-data" action="upload.php" method="post">
    <input type="hidden" name="MAX_FILE_SIZE" value="1000000" />
    Choose a file to upload: <input name="uploaded_file" type="file" />
    <input type="submit" value="Upload" />
  </form>
</body>
</html>

 

code

<?php
//Сheck that we have a file
if((!empty($_FILES["uploaded_file"])) && ($_FILES['uploaded_file']['error'] == 0)) {
  //Check if the file is JPEG image and it's size is less than 350Kb
  $filename = basename($_FILES['uploaded_file']['name']);
  $ext = substr($filename, strrpos($filename, '.') + 1);
  if (($ext == "jpg") && ($_FILES["uploaded_file"]["type"] == "image/jpeg") && 
    ($_FILES["uploaded_file"]["size"] < 350000)) {
    //Determine the path to which we want to save this file
      $newname = dirname(__FILE__).'/upload/'.$filename;
      //Check if the file with the same name is already exists on the server
      if (!file_exists($newname)) {
        //Attempt to move the uploaded file to it's new place
        if ((move_uploaded_file($_FILES['uploaded_file']['tmp_name'],$newname))) {
           echo "It's done! The file has been saved as: ".$newname;
        } else {
           echo "Error: A problem occurred during file upload!";
        }
      } else {
         echo "Error: File ".$_FILES["uploaded_file"]["name"]." already exists";
      }
  } else {
     echo "Error: Only .jpg images under 350Kb are accepted for upload";
  }
} else {
echo "Error: No file uploaded";
}
?>

[MadTechie]

i would probably change

 $ext = substr($filename, strrpos($filename, '.') + 1);

to

$ext = strtolower(substr($filename, strrpos($filename, '.') + 1));

 

but the rest looks fine

[PhpTim]

Thank you again.

 

I think I will mark this topic as solved.

 

I have decided to start with file system.