SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Member
    Join Date
    Jul 2003
    Location
    London, UK
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Uploading Images to a database

    Hiya,

    I purchased Kevin Yank's book on PHP and MySql scripting. And found it really helpful but I'm having problems with one of the sections.

    Everytime I try to get the Storing Binary Data in MySQL section to work it doesn't.

    I've added the database to MySQL using the example in the book.But on page 152 Kevin mentions that the whole code is at the end of the section in the code archive. Unfortunately it doesn't say what the file is called.

    I tried filestore.php but kept getting error messages which I didn't understand. Can anyone help?

    Thanks,
    Tori276

  2. #2
    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)
    I seem to say this a lot, but simply don't store images in a database. Store images in the file system. I'm not a PHPer, but in ASP you'd use the FileSystemObject to place an image in a folder and simply store it's path info in a text field in the database. It's much faster and much more sensible to do it this way.

    Databases are for data.
    File systems are for files.


  3. #3
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Databases are for data. File systems are for files.
    And files are for data

    greg.harvey is right. Reading images from the webserver's file system is much faster than reading them from a database. For larger sites this may save significantly on loading times.

  4. #4
    SitePoint Zealot jinx3's Avatar
    Join Date
    May 2002
    Location
    Vancouver, WA
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with the last two comments. You want to stay away from images in a database. All you need to do is to copy the file to the directory on your server, then store the path to the picture in the database.
    jinx
    superbubba.com
    If you want to learn something . . . start doing it!

  5. #5
    SitePoint Member
    Join Date
    Jul 2003
    Location
    London, UK
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys, I agree totally but in this instance I only needed small thumbnail images of employees for an address book. So I am still opting to store them "in" the database.

    Obviously I wouldn't store large documents or any amount of documents in a databse as that would be silly!

    I've sorted the problem out myself now anyway.

    Tori276

  6. #6
    SitePoint Evangelist
    Join Date
    Nov 2001
    Location
    UK
    Posts
    466
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so you want your application to be non-extensible?
    you require that if access levels blossom then you are going to have to recode the application?
    you want to force the additional overheads and loading that your method dictates?

    alternatively, maybe listen to the posts above (yeah get a voice-web thingy and listen, really listen) and realise that people are not just spouting their minds, they are trying to tell you something.

    If that sounds harsh then maybe you heard it at the level it was meant at.
    teckis - that's news to me.

  7. #7
    Level 8 Chinese guy Archbob's Avatar
    Join Date
    Sep 2001
    Location
    Somewhere in this vast universe
    Posts
    3,741
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And anyways, try to avoid blob fields at all costs.

  8. #8
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, just because you CAN do something, doesn't mean you SHOULD, or that it is a good idea.
    John

  9. #9
    SitePoint Member
    Join Date
    Jul 2003
    Location
    London, UK
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys,

    I appreciate the help but this is really only for a tiny company intranet site. This database is incredibly small and not linked in any other way to anything else on the site. It's sole purpose in life is just to display images for the site address book.

    It's not some mega technical matrix style application!

    I promise hand-on-heart that I would never normally do this!

    pootergeist - cool your boots man! I know you're only trying to help me, but the database is sitting on a local box in-house and this tiny database will not need heavily upgrading!

    I'm working for a small building contractors not a software house!

    Thanks for caring though guys, I do really appreciate the advice.

    Tori276

  10. #10
    SitePoint Member
    Join Date
    Jul 2003
    Location
    A Golden State
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Along a similar line I have been trying to get an HTML form to upload an image to a directory. The thing won't upload the file at all and I was wondering if anyone could suggest anything. My cose looks like this


    <?





    if($HTTP_POST_VARS['upload'])
    {
    $userfile = $HTTP_POST_FILES['userfile']['tmp_name'];
    $userfile_name = $HTTP_POST_FILES['userfile']['name'];
    $userfile_size = $HTTP_POST_FILES['userfile']['size'];
    $userfile_type = $HTTP_POST_FILES['userfile']['type'];

    if($userfile != "" && $userfile_size != 0 && $userfile_type == "image/jpg" && is_uploaded_file($userfile))
    {
    $newfile = "/home/admin/public_html/www.gamercrossfire.net/images/".$userfile_name;
    copy($userfile, $newfile);
    echo "Uploaded";
    }
    }
    ?>

    <html>
    <head>
    <title>Image Upload</title>
    </head>
    <body>

    <h1>Upload Image</h1>

    <form enctype="multipart/form-data" action="<?=$PHP_SELF?>" method="post">
    <input type="hidden" name="MAX_FILE_SIZE" value="20000000">
    Select Your Image: <input name="userfile" type="file"><br />
    <input type="submit" name="upload" value="Upload Image">
    </form>

    </body>
    </html>

    can anyone help me out.
    Thanks a million
    DeMuro1

  11. #11
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    United States
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd like someone to provide some actual papers on why this is a bad practice, all I ever hear is people say "don't do it" or "it's bad practice," but nothing to ever really back it up, and linking to a post where someone else is saying the same thing isn't "evidence." I'd like to get to the bottom of this, because from my understanding the database is just a set of "files" anyways.

    -J

  12. #12
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's simple, really. Look at the overhead of selecting a file from the database:

    - making a database connection
    - parsing a SQL query
    - looking up the data position
    - transferring the data through a socket or TCP/IP connection

    Then look at that of the filesystem:
    - look up data position in FAT table (or ext2 or whatever)
    - pass through data

    I can't provide any benchmarks on this, because I haven't, but it seems pretty darn logical to me that the overhead of the filesystem like described above is a lot smaller than that of a database lookup.

  13. #13
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    United States
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Captain Proton
    It's simple, really. Look at the overhead of selecting a file from the database:

    - making a database connection
    - parsing a SQL query
    - looking up the data position
    - transferring the data through a socket or TCP/IP connection

    Then look at that of the filesystem:
    - look up data position in FAT table (or ext2 or whatever)
    - pass through data

    I can't provide any benchmarks on this, because I haven't, but it seems pretty darn logical to me that the overhead of the filesystem like described above is a lot smaller than that of a database lookup.
    Sounds good in theory but your whole argument just went down the drain, because you forgot to include the steps in the filesystem of retrieving the file.

    Because you still have to A) making a database connection B) parsing the SQL query to get the "location" of the file and then the other two steps you listed, so now it looks like they are the same "length" step wise.

    So my point still stands, do we have hard evidence against this. I've never heard some hard evidence as to why you shouldn't that would hender anything from the performance standpoint.

    I don't like to jump on bandwagons just because "everyone" says it's the "right way" to do it until there is some logical, proven way why you should or shouldn't be doing something, it may be out there I just need to be shown and that is the point of my post. I'm not trying to prove anyone wrong, just want the reasons.

    -J

  14. #14
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like I said, I haven't got any benchmarks on it or hard evidence but if you do some common sense-thinking, I think you will understand.

    You have to see the database system as a layer 'on top of' the file system. Which means that any performance overhead caused by the database consists of overhead by the database system + overhead caused by the file system.

    Using the filesystem only avoids the overhead of the database.

    I do not have a thorough enough understanding of how the database and file systems work, but to me it seems logical that using the latter for uploaded files is faster.

  15. #15
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    United States
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Captain Proton
    Like I said, I haven't got any benchmarks on it or hard evidence but if you do some common sense-thinking, I think you will understand.

    You have to see the database system as a layer 'on top of' the file system. Which means that any performance overhead caused by the database consists of overhead by the database system + overhead caused by the file system.

    Using the filesystem only avoids the overhead of the database.

    I do not have a thorough enough understanding of how the database and file systems work, but to me it seems logical that using the latter for uploaded files is faster.
    The thing is, whether you're using the database or the filesystem to store the actual image, you're still using the database to "determine" where the image is stored in the filesystem so you still have the overhead of connecting to the database, and processing a query, and that was the point I was trying to make in the last post.

    -J

  16. #16
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought that was my point as well? :?

    Now which side are you on?

  17. #17
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    United States
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Captain Proton
    I thought that was my point as well? :?

    Now which side are you on? [img]images/smilies/biggrin.gif[/img]
    Haha, no I think you're confused in what I'm trying to say. Let's say a user is trying to decide whether he should setup his system to upload the image directly into a blob field in the database or if he should have it upload to a folder on the server, and have the image location inserted into the database at the same time. Now when someone is "using" the system and it requests an image, either method is going to have to connect to the database and process a query, so in your original post of these being steps not includes in the filesystem method was erroneous. That is my point, because they are present in both setups.

    -J

  18. #18
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, I see your point.

    Fortunately for me there's a 'but' to that. For database access, there are usually going to be two queries involved. One one the page that displays the <img> tag, to get the IDs of images that need to be displayed. The <img> tag will refer to a file like displayImage.php?id=$id which will send the actual image BLOB contents to the browser. Another query has to be executed for this. (That's two queries .)

    Now if you use the filesystem to store the image file, you only have to query for the location of the image on the filesystem and display a tag like <img src="displayImage.php?file=myimage3.jpg"/>. Now the displayImage.php file does not have to make a second query, all it has to do is pass through the image file to the browser.

    So as you can see, using the filesystem method, there is no additional overhead of the second database query (+ database connection).

  19. #19
    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)
    jturmel has an excellent point here. I'm absolutely sure there is a good technical reason. At least I hope so... I'd hate it to turn into a monkeys in a cage situation! Scouring the Internet now.

  20. #20
    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)
    Ok. I've looked around everywhere and there don't seem to be any performance tests. There are performance tests for different DBMSs against each other for retrieving blobs, but no test I can find for blobs vs files. Perhaps someone like Marco knows more about the behind the scenes operation and can add some details...? I have people I can (and will) ask too.

    Meanwhile, the info I can find is an overwhelming general tendancy towards using files instead of blobs, but not always. The Experts-Exchange guys and gals, for example, all seem to say "files, not blobs!" without exception. But I've found other posts that give some pretty compelling pros to using blobs.

    It seems that blobs are much more difficult to implement properly, which makes them dangerous territory for all but the expert DBA, hence their general bad reputation. (ie: if you, like me, know enough to make a half decent database application but wouldn't consider yourself an expert by any means, then you're probably best off avoiding them as you'll be heading for a fall!)

    That said, until someone finds or does a performance test, or someone posts more detailed technical reasons for using/not using blobs I wouldn't like to say. I for one will stick with what I know -- files in the file system. It works!

    (And don't forget that even a performance test is only really viable for that one individual's machine. The database setup and machine specs make an enormous difference with the database it seems...)

    The best pros and cons list I found is here:
    http://marc.theaimsgroup.com/?l=php-...6728003845&w=2 (the whole thread is worth a look)

    There's another one here. Some similar pros and cons, some contradictory:
    http://www.ultraviolet.org/archive/mysql.2000/1769.html

    And a guy here who seems to be of the opinion that blobs in MySQL are a perfectly acceptable thing since he's suffered no performance problems:
    http://www.geocrawler.com/mail/msg.p...3820962&list=8

    This thread is also quite interesting:
    http://www.geocrawler.com/mail/threa...BLOBs&list=193

    G

  21. #21
    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)
    I seriously doubt anyone's actually reading this thread any more, but a DBA friend of mine sent me this:

    Following is the tip cut from MSDN regarding to the handling of text and
    image :

    It’s from the article Top Ten Tips Accessing SQL Through ADO and ASP

    Tip 3: Avoid Blobs
    Blobs (Binary Large Objects) are stored in SQL Server as either text or
    image datatypes. SQL Server does not store the Blob data on the data page
    along with the other fields in the row. Instead, SQL Server maintains a
    pointer to the Blob data. The data itself is stored in 2KB pages linked
    through 16-bit text pointers, meaning that there are really about 1800 bytes
    available for actual data storage if the column is not NULL. If the column
    is explicitly set to NULL, the storage size will be 0, since there is no
    need for the text pointers. This essentially means that storing data in
    Blobs will increase your storage requirements in 2KB increments.
    While storage size alone may not be a deterrent, there are also functional
    limitations with Blobs. For example, if you use a WHERE clause to search on
    a text column, you are limited to using the LIKE operator. This can be a
    very time consuming and will add a lot of overhead to your application.
    Also, when you’re working with large quantities of data, it may become
    necessary to read the data in chunks, rather than pulling it out of a column
    all at once.
    Before using a Blob field, consider the alternatives. If you want to store
    images in the database, you may find it more appropriate to store the images
    outside of the database, and simply maintain URLs that point to the images
    within the table. If you are storing lots of text data, you may find that
    rather than using a Blob field, you can denormalize the table and break the
    data into varchar(255) fields.
    If you still require a Blob field despite the storage overhead and
    limitations in functionality, there are a few caveats. If you’re using a
    forward-only cursor (the default), you should retrieve the Blob fields from
    left to right, and to the right of any scalar values you include in your
    SELECT statement. If your underlying table definition looked like this
    CREATE TABLE MyTABLE(
    Field1 Identity(0, 1),
    Field2 Text,
    Field3 Int,
    Field4 Image
    )
    retrieve your records using the following SELECT statement:
    SELECT Field3, Field2, Field4 FROM MyTable
    There is another point to be aware of concerning presentation. If you will
    be retrieving images from your database through ASP, you will need to
    manipulate the HTTP header information. If you were to retrieve a GIF image
    from the database, you’d need to first clear out any existing HTTP header
    information, then set the ContentType to Image/Gif. See the article
    “Delivering Web Images from SQL Server,” by Scott Stanfield (MIND, July
    1998), for a complete discussion of how to do this effectively.
    To put additional text on the returned page, you need to create a separate
    page to host the image. Figure 8, RetrieveImage.asp, demonstrates how you
    can retrieve an image from Pubs. Notice how this page doesn’t write any text
    with the Response object. Since I would like to provide some text with the
    image, I created an additional page, ShowImage.asp (see Figure 9). Another
    page is necessary because once you have set Response.ContentType to
    Image/Gif, you cannot write text to the ASP page. ShowImage.asp actually
    displays the image by referring to the RetrieveImage.asp in the IMAGE tag’s
    SRC argument.
    <%
    Option Explicit
    Dim cnnPubs
    Dim rstPub_Info

    ‘ Clear existing HTTP header info
    Response.Expires = 0
    Response.Buffer = TRUE
    Response.Clear

    ‘ Set the HTTP header to an image type.
    Response.ContentType = “image/gif”

    Set cnnPubs = Server.CreateObject(“ADODB.Connection”)

    cnnPubs.Open “pubs”, “sa”

    Set rstPub_Info = cnnPubs.Execute(“SELECT logo FROM pub_info WHERE _
    pub_id=’1389'”)

    Response.BinaryWrite rstPub_Info(“logo”)
    Response.End
    %>
    Figure 8 RetrieveImage.asp
    <HTML>
    <HEAD>
    <TITLE>Show the Image</TITLE>
    </HEAD>
    <BODY>
    <H2>Presenting the Logo/H2>
    <!— This page contains a link to the image so that you can display
    the text. The RetrieveImage.asp page won’t allow you to write out text
    since you have set the ContentType to image/gif” —>

    <IMG SRC=”RetrieveImage.asp”>

    </BODY>
    </HTML>
    Figure 9 ShowImage.asp

    This two-page trick comes in handy for operations such as providing dynamic
    banners while minimizing work for your server. Simply create a static HTML
    page, then reference the ASP page through your IMG tag. The ASP page would
    be responsible for choosing which banner to display. Figure 10 shows the
    output of ShowImage.asp.
    At last, real reasons to avoid BLOBs!

    G

  22. #22
    SitePoint Enthusiast
    Join Date
    Dec 2002
    Location
    United States
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Finally, some actual knowledge.

    So what does he mean by denormalizing it if I want to store a very large amount of text, say for articles in a magazine?

    -J

  23. #23
    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)
    Ugh. I have no idea.... I just ask the questions!


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
  •