Jump to content


Photo

In what ways is saving file names to MySQL better?


  • Please log in to reply
5 replies to this topic

#1 alwaysinit

alwaysinit
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 19 October 2006 - 08:05 AM

Hi, Im new to PHP. While scripting thumbnail generation of user's photos in the photo upload script I was wondering.

Why is it better practice to save the name of the file into MySQL as well as saving it to a folder?
Would'nt the data be retrieved just as quickly and easily just using the folder?
What advantages does saving the file name to MySQL give a programmer in the long run?

Thanks, to all you coding gurus giving your time and expertise :-*

#2 alwaysinit

alwaysinit
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 19 October 2006 - 08:59 AM

I'll post same question in MySQL forum here

#3 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 19 October 2006 - 09:13 AM

first off, definitely not a good idea to double post.

second, as for your question, there are a few 'proper' reasons (inefficiency, etc) for not storing binary data in a database, but i have a few guidelines for myself:
1, when it comes to backing up my database, it's much quicker/easier to do it when the output is purely text.

2, if you ever need access to the files, it's easier to get the file from a directory. to get these files in the event of them being stored in a DB, you'd need a script to extract them.

3, (IMO) it's faster - can't say why exactly, but i'd hazard a guess that it spreads the load off the mysql database and uses the filesystem. also the filesystem is better designed for handling files. and the DB will naturally slow down when it starts to get messy.

4, any future operations on an image (cropping, resizing, etc) would involve extracting the image first, applying your operations, and putting the file back. in the event of using the filesystem, you'd just load the image with imagecreatefromjpeg/imagecreatefromgif, etc, do your stuff and thats it.

5, you cut out the need for the DB altogether. in the event of you ever using a different type of DB other than MySQL, the port across would be easier. also you can reference the file directly in your <img> tags wheras a script would be required otherwise.

6, general organisation much easier.

i've probably only skimmed on the benefits, TBH, but should give you an idea.

cheers
Mark
"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#4 alwaysinit

alwaysinit
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 19 October 2006 - 09:47 AM

Hi thanks, I was never pondering putting the actual binary data there, just name references to the files.

What I gathered from your post is that:

Being new, it would be easier for me to code storing and retrieval of pics, MP3s, images just using the file system instead of DB.

Be easier for me to resize, thumb, crop, and manipulate without DB storage(just using the file system)

If I can keep things organized without need of putting these name references into the database it would still run efficiently.

I seem to be missing how the name references being in the database make for a better operation in the scripts.
Being a beginner, it seems like redundant coding. Why reference it in the database, when the image is sitting with name in the file system?
Sorry I'm very new and fail to understand the speed benefits

#5 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 19 October 2006 - 10:52 AM

oops seem to have misunderstood your original post, sorry!

storing filenames in the database allows for quick retrieval regardless of how you use the file, as it allows you to associate an image with whatever sort of record you like - be it an article, a user, or anything else you like. having a database record for each file (call it an index/table of contents, if you like) allows you to keep track on other information that may be relevent - comments, tags, associations, etc - and generally allows you more flexibility in terms of filenames when storing many images in one directory - consider this (assuming you DONT use a db) - how will you know what user the file is associated with? sure, you could make a directory for each user, but again that can start getting messy.
someone else may have some more valid reasons, but the ones i suggested are the ones i consider.

cheers
"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#6 alwaysinit

alwaysinit
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 19 October 2006 - 12:05 PM

Ah yes, that would make sense.
thanks for the info redbullmarky, I will stick with sending file names to mysql.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users