Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Jun 2013
    Posts
    28
    Thanks
    2
    Thanked 2 Times in 2 Posts

    how to store Image/Url in mysql using php?

    I am trying to store an image with the following code , but the image data is filled with "Null" insted of [BLOB-3B]...
    PHP Code:
    INSERT INTO `image`(`ID`, `IMAGE`) VALUES ('4',LOAD_FILE('C:\xampp\htdocs\dbtry\images\google.png'));
    ..... At the Same time i want to retrieve the image from the database .

    so please suggest any useful link to Store & Retrieve image from database....

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    I would *NOT* recommend doing this. The resulting web site will be *SLOWER* than if you just leave the images in the file system and store only the URL (or relative URL) in the database.

    Reason: When it is time for your HTML page to display an image, you will have to put something like this in the code:
    Code:
        <img src="getImageFromDatabase.php?imageid=77183" />
    And then the server has to execute *another* PHP page to retrieve that image and send it to the browser. Basically, each image will end up getting buffered *TWO* EXTRA TIMES on the server, for no advantage.

    Are there reasons/times you would want to do this anyway? Yes. If you are on a very very large system with many many servers, you might want to take advantage of the distributed nature of a database (that is, the DB is spread across multiple servers). (You could also build a distributed file system, but that's probably even more work.)

    But if you are creating a typical web site, all on one server (or even on two servers!), you don't need this extra distributional capabilities. Stick to something simple, just putting the file *path* in the database.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    sathish-wd (11-21-2013)

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    If you need to store the images in the database (and there are lots of reasons for doing so - particularly for larger systems) then you use LOAD_FILE to load the image into a BLOB field and DUMPFILE to extract it back into an image. That allows the mySQL to create the image file directly from the database without any PHP involvement at all.

    While smaller systems can get away with keeping the images as files, larger systems wthat use transaction processing where the image is a small part of the treansaction can only maintain integrity if the images are stored in the database itself.

    Another reason for storing them in the database for bigger systems is that the IT support staff may only back up the database because all of the other files are expected to be able to be recreated from the database. In that situation if the only copy of the image is outside the database and the hard drive fails then it may not be possible to recover any of the images.


    Here's some example SQL for saving and restoring image files.

    Code:
    INSERT INTO myimagetable (image_name, myimage) VALUES ('myimage.jpg', LOAD_FILE('images/myimage.jpg'));
    
    UPDATE myimagetable SET myimage = LOAD_FILE('images/myimage.jpg') WHERE image_name = 'myimage.jpg';
    
    SELECT myimage INTO DUMPFILE 'images/myimage.jpg' FROM myimagetable WHERE image_name = 'myimage.jpg';
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Yep, spot on. Agree with Felgall for the most part. I would tend to use files for even medium-size systems, but I'm not fanatic about it.

    But, quite frankly, the very fact that Sathish is asking the question and can't seem to get his code to work indicates to me that he is a relative beginner and is *NOT* working on a large site. So I would vote to keep it simple. And I do want to point out, again, that you will pay a small performance penalty (equivalent to at least on size-of-the-file buffer transfer and more like two of those) for putting the blob in the DB when you then need to retrieve it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Jun 2013
    Posts
    28
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Thanks for your response , it helps a lot to me.I am currently working on single server so i prefer to store the path of the image rather to store the image.
    While storing path , i am going to divide it into two path "Absolute path" & "Relative path".
    So could you please explain
    - how to store relative & absolute path
    - how to display it in a html page
    - Detail the code of "getImageFromDatabase.php"

    Or Refer any useful site ........

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Still wrong.

    There is NO REASON to store absolute path and many reasons NOT to do so.

    One good reason: If you store only relative path, then at a future time you could move the entire images directory to some other location and yet the RELATIVE paths would still be correct!

    Also, if you store paths and not BLOBs then you do *NOT* need or want the silly "getImageFromDatabase.php" page!!

    Let's say you had a PRODUCTS table that looked like this:
    Code:
    prodid -- prodname          -- imagename
        17 -- 4 Slice Toaster   -- toaster4.gif
        32 -- 5 Speed Blender   -- blender5.jpg
        41 -- 2 Burner Hotplate -- hotplate2.png
    Now I don't use PHP, so this code will be both old and possibly have errors, but it will give you the idea:
    Code:
    $sql = "SELECT * FROM products ORDER BY prodid";
    $result = mysql_query( $sql ) or die( mysql_error() );
    
    $imageBase = "/productImages/"; // all images assumed to be in this directory
    
    while( $row = mysql_fetch_assoc( $result )
    {
    ?>
        <tr>
            <td>
                <a href="showProduct.php?id=<?php echo $row["prodid"];?>">
                    <?php echo $row["prodname"]; ?>
                </a>
            </td>
            <td>
                <img src="<?php echo $imageBase . $row["imagename"];?>" width="200"/>
             </td>
        </tr>
    }
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

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