SitePoint Sponsor

User Tag List

View Poll Results: Do you store images in your sql database?

Voters
71. You may not vote on this poll
  • Yes, always

    1 1.41%
  • Sometimes

    11 15.49%
  • No, never

    48 67.61%
  • I would, but I don't know how

    11 15.49%
  • what ist a mysql database?

    0 0%
Page 4 of 4 FirstFirst 1234
Results 76 to 87 of 87
  1. #76
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Having said that, disk space is cheap these days, so it's not a massive problem. Just useful to bear in mind if you have limited disk space though.

  2. #77
    SitePoint Member
    Join Date
    Apr 2004
    Location
    portland
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Just use a regular php image upload into a server DIR and use a .js to list the images for clients to use in a CMS if that's what you're doing.

    example:

    PHP Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <html>

    <head>
     <title>Image Upload</title>
    </head>

    <body>
     <form name="form1" method="post" action="" enctype="multipart/form-data">

     File name (has to be a gif file):
     <input type="file" name="imagefile"> 
     <br><br>
     <input type="submit" name="Submit" value="Submit">

     <?php 
     
    if (isset($_POST['Submit'])) //not everybody uses globals-on AND WITH REASON!
     

     echo 
    "<br><br>n"
     if (
    $_FILES['imagefile']['type'] == "image/gif")
     { 
     
    copy ($_FILES['imagefile']['tmp_name'], "files/".$_FILES['imagefile']['name']) or die ("Could not copy"); 
     echo 
    "Name: ".$_FILES['imagefile']['name']."<br>n"
     echo 
    "Size: ".$_FILES['imagefile']['size']."<br>n"
     echo 
    "Type: ".$_FILES['imagefile']['type']."<br>n"
     echo 
    "Copy Done....n"
     } 
     else 
     { 
     echo 
    "Could Not Copy, Wrong Filetype (".$_FILES['imagefile']['name'].")n"
     } 
     } 
     
    ?>

     </form> 
    </body>

    </html>

  3. #78
    SitePoint Zealot colinr's Avatar
    Join Date
    Aug 2003
    Location
    san francisco, ca
    Posts
    198
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Floezen
    So I better not use a database at all?
    Why is the risk screwing up the database when write blobs higher than with text??
    Flözen
    If text is one bit off you may get something like "Welcome to our &orum", if an image is one bit off, you won't (neccesarily) get ANYTHING. you get a nice box with the red X.

    Although both look very unprofessional in an active, live website, the text is much easier to fix... (if the image goes bad, can you replace it with the same exact image if the only place you HAD that image was in the db?)

    Aside from that, I mainly don't do it because its:
    A. Putting all your eggs (files, content, etc) into one big basket (the db)
    B. Just one more request to the db.
    and C. most the sites I have done have been very image intensive, hense I would have a huge DB and its much easier to maintain images as files, you can just drag and drop, people can download them easier (you can give it a real file name), caching, etc.

  4. #79
    SitePoint Member
    Join Date
    Mar 2003
    Location
    Bergen, Norway
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Images in mysql database

    Quote Originally Posted by Floezen
    Hi,

    I've read a couple of threads here where people tried to find out how to store image files in a mysql database.
    The answer was always: Don't do it.

    Now I have two questions:

    1) Why not?


    2) What do I have to make different to queries?

    Flözen
    1) There are several very good reasons why you should not do this, and several good reasons why you should do so anyway

    Some reasons not to store images in the database:
    * When you actually want to use the images, getting the images from the database is inefficient - since the image usually is sendt to the client one by one. Imagine having, for instance, an web based image gallery. If the images are stored as files, displaying 20 images is as simple as including img src="path_to_image" 20 times in your html page, and the browser will request the images from the web server. Having the images in a database requires some logic to select the binary data, and sending it to the browser. This probably means having a script (php?) doing this for you, and your html file will include 20 img src="imagehandler.php?imageid=xxx". This will result in at least 20 extra queries...
    * Manipulating images are far harder. With text, most databases can update the strings directly via an SQL update/insert command (update stringcol set str='habba' where str='dabba'). If you, for instance, want to resize all your images, you have to create some logic to export the images, resize them, and import them back into the base.
    * If you have huge amounts of images, doing incremental backups of the filesystem is usually more practical (in regards to storage needs, speed and so on) than doing full backups of the database.
    * In total, this means your overall program design will be more easy (at least, that is my experience...)

    Some reasons to store the images in the database:
    * Everyting is in one place.
    * Nobody will accidentially delete a file and forget to update the path in the database
    * Nobody will accidentially put a new file in the filesystem and forget to update the database

    Some databases, like Oracle, actually has wide support for different kind of media files, and supports metadata for these files.

    2) Take a look at http://www.onlamp.com/pub/a/onlamp/2...09/webdb2.html


    Jo H

  5. #80
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johavik
    Some reasons to store the images in the database:
    * Everyting is in one place.
    * Nobody will accidentially delete a file and forget to update the path in the database
    * Nobody will accidentially put a new file in the filesystem and forget to update the database
    not to be overly picky here, but those reasons don't make sense at all

    everything in one place is not a reason -- you have to say why
    this is better, otherwise it is begging the question

    not having files outside the database because somebody might accidentally
    delete one or add one without updating the database is ludicrous, and if
    such a person were turned loose on a system where the images are stored
    in a database, guess what they would be able to do there...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #81
    SitePoint Member
    Join Date
    Mar 2003
    Location
    Bergen, Norway
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    not to be overly picky here, but those reasons don't make sense at all

    everything in one place is not a reason -- you have to say why
    this is better, otherwise it is begging the question

    not having files outside the database because somebody might accidentally
    delete one or add one without updating the database is ludicrous, and if
    such a person were turned loose on a system where the images are stored
    in a database, guess what they would be able to do there...
    Hm. Sometimes it is nice to have everything in once place..

    Deploying and/or development. When deploying the database on a new system you dont have to think about updating paths to images (for instance, I'm developing the same system on a Linux server (when at work) and on a Windows laptop (when at home)). Just moving the database, instead of database + images + updating paths, would save time and hinder bugs.

    Easy-to-understand data architecture. Data that is logically connected is stored in the same structure. You have one set of sematics for creating/manipulating your data structure.

    "Hidden" architecture. Deploy your solution on a customers server with a nice interface for updating the database, and user Joe X can't change your logo on disk And no, I don't think of this as security through obscurity - it is not a security measure, just practical.

    All data is covered by the same backup.

    Triggers/foreign keys to automatically clean binary data when related data is updated/deleted.



    Of course deleting files by mistake happens. Your new clumsy developer might do so. Even your sysadmin might do so on a bad day. If accidential file deletion never occured, why keep backups for more than a day? The difference is, if you delete by mistake in the database, the data is probably still referentially OK.

    That said, I would put the binary data in the file system in most scenarioes. If storing binary data in the database I would use a database system with build-in capabilities for manipulating the data I wanted to store. I still think, though, to say "never images in the database, whatever the situation might be", is a bit to rigid.

    Jo H

  7. #82
    SitePoint Evangelist Daijoubu's Avatar
    Join Date
    Oct 2002
    Location
    Canada QC
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Everyone is talking about how a DBMS handle blob field, but did any of you think about the other overheads?

    Imagine having 40 posts per page in a forum and assuming that all the posters have an avatar
    That would require re-parsing the avatar script 40 times! (unless you use a opcache)

    Example with PHP:
    Receive HTTP request
    Start PHP interpreter
    Load the script, the SQL class/driver, parse them
    Connect to the DB
    Send a query
    Return data

    VS

    Receive HTTP request
    Return data

    Using flat file also have additional benefits:
    Linux filesystem cache
    Ability to use Squid
    Gain even more speed with lighter httpd using select instead of Apache pre-fork, such as thttpd, Boa, etc.
    Speed & scalability in mind...
    If you find my reply helpful, fell free to give me a point

  8. #83
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but did any of you think about the other overheads
    most of us did , except some of the more 'experienced' DB dudes who for some reason can't admit that if it looks smells and tastes like a fish.. , it is , in all probablity, a fish.

    & that despite the confusion it caused in this thread ! (made obvious by the constant re-asking of the same original question).

    I know I said I was giving up .. this time I mean it

  9. #84
    SitePoint Member
    Join Date
    Apr 2004
    Location
    UK
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've never tried it myself for some of the reasons posted here however I'm sure that a well optimized database could handle efficient I/O of image data just as it could any other data, binary or otherwise.

    The main reason I've never been inclined to store image data in a database is the fact that I would need to write a utility to view my images and of course this is all done seemlessly when they're stored in the file system.

  10. #85
    SitePoint Member
    Join Date
    Mar 2004
    Location
    Juneau, Alaska
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am going to try to synthesize some of what has been posted, and then add my own preference.

    A file system is a type of database.

    The databases called "file systems" are designed to handle large data structures. The internal structure of the data in not assessed or manipulated by the database. File systems are easy and cheap to implement.

    The databases called "databases" are designed to handle "small" strings of text, and numerical data. Facilities are provided for manipulating, indexing, and searching strings of text and numbers. The engine software is optimized for reading and writing small strings and numeric data.

    There actually are "database engines" that depend on the file system for storing data. There are also SQL databases that will store BLOBs. There must be uses for these facilities, or they wouldn’t have been created, and/or wouldn’t persist.

    However, using a tool as it was intended is usually the best approach. Driving screws with a hammer makes sense in some situations, but is not the usual approach for very good reasons.

    I have built a couple of projects that store large quantities of images. For example: a museum catalog. Why not store the images in a database (Win 2k file system) that is optimized for BLOBs? There is no problem storing the URL and properties of the image in a database record, and then retrieving the image from the file system as needed.

    Geographic images are vector data, and belong in an SQL database where the data can be indexed, searched, and manipulated. This is irrelevant to the issue of BLOBs.

  11. #86
    Back in Action Winged Spider's Avatar
    Join Date
    Jun 2001
    Location
    outside my mind
    Posts
    900
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hate to open up dead threads...

    Does anybody know of a gallery script that stores it's images in a database?

    We've decided to start storing all of our images in a database instead of the file system. We have many content editors and we think that this way will provide a much easier way to include images in their content. We have an editize liscence and you can literaly drag and drop images into the content areas. Having a internal gallery of images from the database will take the guessing work that a lot of our content people do.


  12. #87
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hate to open up dead threads...
    Then please don't do it. Especially not with questions irrelevant to the thread. Start a new one.

    Closing thread.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com


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
  •