SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 66
  1. #1
    Founder of Primal Skill Ltd. feketegy's Avatar
    Join Date
    Aug 2006
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Storing images in a database

    I think that the file system functions PHP are primitive. So many things could go wrong.

    But what do you think?

    Is it a bad idea to store images in a database?
    Considering performance and disk space...

  2. #2
    SitePoint Zealot adam.jimenez's Avatar
    Join Date
    May 2009
    Location
    Ware, UK
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by feketegy View Post
    I think that the file system functions PHP are primitive. So many things could go wrong.

    But what do you think?

    Is it a bad idea to store images in a database?
    Considering performance and disk space...
    when storing images in mysql db inevitably u need to increase max_allowed_packet which you might not be able to do on shared hosting.

    Also exporting massive databases can sometimes be more hassle than ftp.

    So I prefer to use the file system.

    If the files need to have restricted access then I will store the file in a protected folder and call it {$id}.jpg. The id will refer to a database id that has the file name/ owner etc. This also prevents issues like 2 files having the same name.
    I will then have a script that acts as a wrapper to display the image.

    I haven't noticed much of a difference between performance and disk space in either approach.

  3. #3
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    data base is a bad way to store images. It reduces teh search time. Just store the URL in a db.

  4. #4
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    data base is a bad way to store images. the look up time on the DB is fricking ridiculous. And how often are oyu moving images that the storage file functions of PHP matter. Upload and move there you go.. Just store the URL in a db.

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    India
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never save images in database. Serving static files much easier than serving with php script.

  6. #6
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    The file system of the chosen operating system is optimized to its fullest to handle files, it is designed to do that task extremely well. A Database is not designed to handle files but data. While a file could be considered data there is a fundamental difference in usage.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there are some good reasons for storing images in the database

    see Images in a database

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot adam.jimenez's Avatar
    Join Date
    May 2009
    Location
    Ware, UK
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there are some good reasons for storing images in the database

    see Images in a database

    the number 1 pro for database use:

    - you have data integrity and consistency. If you delete a record you can cascade that delete down to the image. You cannot do a JOIN of an OS
    and a db.
    what's so hard about deleting a record and a file at the same time?
    This seems like a very weak argument compared to all the cons.

    and the other pro:

    - databases are really really good at storage and retrieval. It can be argued that filesystems are really really good at storage and retrieval too, but for high activity only certain types of filesystems are, and if you do not have a ReiserFS or MogileFS expert, you may end up with more
    than you bargained for. Very likely, you have someone already knowledgeable about databases.
    this seems to contradict what most peeps have been saying about filesystem efficiency.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by adam.jimenez View Post
    this seems to contradict what most peeps have been saying about filesystem efficiency.
    which just reinforces the following concept --
    if you're already leaning one way or the other, you tend to hear what you want to hear
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by feketegy View Post
    Is it a bad idea to store images in a database?
    Considering performance and disk space...
    Since you brought up performance:
    File Operations vs. Database Operations (Simplified)

    PHP -> I/O -> File System
    PHP -> Database -> I/O -> File System

    There are a lot more layers to go thought with the DB. However, if you are collecting meta data on an image from the DB, you could get the image from the DB just the same. Then you only have one I/O operation instead of two.

    For example, getting meta data from the DB, image from file system:

    PHP -> Database [name, author, location] -> I/O -> File System
    -> PHP -> I/O [db:location] -> File System

    The alternative:
    PHP -> Database [name, author, image] -> I/O -> File System

    Plan and evaluate which method will best work for your application. Remember to embed an image in an HTML page, you must make a separated call. In this regard the Database loses its advantage of a single I/O operation.

    ------------------------------------------------------------------------------------

    - databases are really really good at storage and retrieval. It can be argued that filesystems are really really good at storage and retrieval too, but for high activity only certain types of filesystems are, and if you do not have a ReiserFS or MogileFS expert, you may end up with more
    than you bargained for. Very likely, you have someone already knowledgeable about databases.
    this seems to contradict what most peeps have been saying about filesystem efficiency.
    I'll just like to point out, file systems are databases, very special purpose databases. And yes, while it does depend on the file system, they are very efficient at storage and retrieval.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  11. #11
    Founder of Primal Skill Ltd. feketegy's Avatar
    Join Date
    Aug 2006
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies!
    I will use the filesystem instead to store the images in a db.

    - you have data integrity and consistency. If you delete a record you can cascade that delete down to the image. You cannot do a JOIN of an OS
    and a db.
    This is only good when the database and the actual image are on 2 separate servers.
    In my experience when the database is on the same server as the image / php script it tends to be very consistent and reliable, I can't recall it when I got an error of not connecting properly on localhost

  12. #12
    SitePoint Zealot adam.jimenez's Avatar
    Join Date
    May 2009
    Location
    Ware, UK
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    which just reinforces the following concept --
    if you're already leaning one way or the other, you tend to hear what you want to hear
    or that the argument isn't very compelling.

  13. #13
    I <3 Internet Tekime's Avatar
    Join Date
    Dec 2003
    Location
    Maine
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In all my years, I've never had any compelling reason to save images to a database.

    Until I can do something like this, I don't see any need to:

    SELECT * FROM tbl_photos WHERE photo LOOKS LIKE "%cameron diaz%"

    Scriptalicious SEO Scripts
    Save 20% with coupon code SPROCKS


  14. #14
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I'm a big fan of storing every bit of content to do with a site in the database. Mainly for deployment/backup/restore/paving purposes. Now, I will agree that filesystems and static file handlers are much better at serving binary files efficently. But no one says that just because the backing store is a database that you have to serve it out of the database--you can always cache requested images on disk and actually serve the files from there, giving one the best of both worlds.

  15. #15
    Founder of Primal Skill Ltd. feketegy's Avatar
    Join Date
    Aug 2006
    Posts
    482
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    I'm a big fan of storing every bit of content to do with a site in the database. Mainly for deployment/backup/restore/paving purposes. Now, I will agree that filesystems and static file handlers are much better at serving binary files efficently. But no one says that just because the backing store is a database that you have to serve it out of the database--you can always cache requested images on disk and actually serve the files from there, giving one the best of both worlds.
    Hmm... interesting approach

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    Mainly for deployment/backup/restore/paving purposes.
    paving?

    da SQL superhighway!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    We use paving as a technical term here. IE, the MSBuild script for the current project:

    Code:
    	<Target Name="PaveTestDb">
    		<Message Text="Starting to pave the test DB at $(MSBuildProjectDirectory)\TestDb" />
    		<MakeDir Directories="$(MSBuildProjectDirectory)\TestDb" Condition="!Exists('$(MSBuildProjectDirectory)\TestDb')" />
    		<Exec Command="&quot;$(MSBuildProjectDirectory)\Common\Db\SSEUtil&quot; -d name=PageMotion_DbTests" />
    		<Delete Files="$(SolutionDir)\TestDb\PageMotion*.*df" />
    		<Exec Command="&quot;$(MSBuildProjectDirectory)\Common\Db\SSEUtil&quot; -create &quot;$(MSBuildProjectDirectory)\TestDb\PageMotion.mdf&quot; PageMotion_DbTests" />
    		<Message Text="Completed paving the test DB." />
    	</Target>

  18. #18
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For a small number of images, you can use the file system.
    But when you get allot, the database will be a much better solution.

    (ever opened up a folder with 500k images in it?)

    When you use a database, you can easily add more servers to share the load (after you have allot of data), and your code will stay the same (insert in master).

    Only "problem" with it is that you need a "proxy" type server, so people with "slow" connections won't keep your DB connection opened for 30 min. (you upload the image first, without any DB connections, and then repost the image to the script that inserts it in the db).

  19. #19
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Only "problem" with it is that you need a "proxy" type server, so people with "slow" connections won't keep your DB connection opened for 30 min. (you upload the image first, without any DB connections, and then repost the image to the script that inserts it in the db).
    Not really. Most server-side scripting platforms won't even start processing anything until the form is posted, so the database connection won't open until after the slow connection angle has played out.

    Now, the flip side (downloading) is where one needs to be a bit more concered by this but if you are following reasonably modern software engineering principals it shouldn't be much of a problem. For those who don't want to get that deep, remember to pull the bytes out of the database and then send it to the client rather than just dumping the DB pointer directly to the client.

  20. #20
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had problems with the uploading, downloading, not so much since the images used to be cached in a squid proxy first, and then a delivery network after (then the user pc).

    But you are correct, on most smaller sites, downloading can be more of a pain than uploading.

  21. #21
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by adam.jimenez View Post
    when storing images in mysql db inevitably u need to increase max_allowed_packet which you might not be able to do on shared hosting.
    Or you can split the file into bite-sized "chunks" that the DB can handle.

    I did this for a client once who was storing and serving ebooks from his online database. (He wanted the ebooks in the database to protect their integrity and restrict access to paying customers. The mechanism worked very well).
    Ian Anderson
    www.siteguru.co.uk

  22. #22
    SitePoint Zealot adam.jimenez's Avatar
    Join Date
    May 2009
    Location
    Ware, UK
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru View Post
    Or you can split the file into bite-sized "chunks" that the DB can handle.

    I did this for a client once who was storing and serving ebooks from his online database. (He wanted the ebooks in the database to protect their integrity and restrict access to paying customers. The mechanism worked very well).
    sound a bit clunky (or chunky). u can still restrict access by using the database and file system together as I outlined earlier.

  23. #23
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    You can definitely handle the security angle, but let me know when your filesystem ROLLBACKs with your database transaction.

    PS: I forgot to mention, you can go MSSQL 2008 and get the best of both worlds for your trouble.

  24. #24
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I always advise against storing images in the db. I suppose that it is possible to carefully design a system to do it, provided you had complete control over hardware and software and plenty of such resources, but for a normal web site or even most web applications, I can't imagine any advantages. If the same db server is also handling normal data, I would think that would make more common database tasks slower. Not everyone has their own datacenter to play with ya know.
    Visit my blog
    PHP && Life
    for technology articles and musings.

  25. #25
    SitePoint Zealot Dorsey's Avatar
    Join Date
    Feb 2004
    Location
    NJ
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there are some good reasons for storing images in the database

    see Images in a database

    Please don't just give us a link without a synopsis of what we might expect to find. Most of us don't have the time or the desire to follow every link without confidence that it will be of interest or even relevant.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •