Advanced Search

Results 1 to 6 of 6

Thread: storing images in database

  1. #1
    Join Date
    Jul 2011
    Location
    hyderabad,India
    Posts
    58
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Default storing images in database

    hi all,
    i want to store an image in MYSQL database.please tell me whether the below sql syntax i have written is correct or not.
    Code:
    CREATE TABLE IF NOT EXISTS `products`(
      `serial` int(11) NOT NULL auto_increment,
      `name` varchar(20) collate latin1_general_ci NOT NULL,
      `description` varchar(255) collate latin1_general_ci NOT NULL,
      `price` float NOT NULL,
      `picture` varchar(80) collate latin1_general_ci NOT NULL,
      PRIMARY KEY(`serial`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7;
    and i have inserted the values for the above table also.
    below is the one.....
    Code:
    INSERT INTO `products` (`serial`, `name`, `description`, `price`, `picture`) VALUES
    (1, 'View Sonic LCD', '19" View Sonic Black LCD, with 10 months warranty', 250, 'images/lcd.jpg'),
    (2, 'IBM CDROM Drive', 'IBM CDROM Drive', 80, 'images/cdrom-drive.jpg'),
    (3, 'Laptop Charger', 'Dell Laptop Charger with 6 months warranty', 50, 'images/charger.jpg'),
    (4, 'Seagate Hard Drive', '80 GB Seagate Hard Drive in 10 months warranty', 40, 'images/hard-drive.jpg'),
    (5, 'Atech Mouse', 'Black colored laser mouse. No warranty', 5, 'images/mouse.jpg'),
    (6, 'Nokia 5800', 'Nokia 5800 XpressMusic is a mobile device with 3.2" widescreen display brings photos, video clips and web content to life', 299, 'images/mobile.jpg');
    the "picture" field stores pictures in database.i have given there VARCHAR(80)
    i know for storing pictures we should give BLOB data type.
    but tell me whether the above syntax i have written will work or not.
    if not what would be the syntax...

  2. #2
    Join Date
    Aug 2011
    Location
    HCM
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    You can store image information in database in varchar type, it will be alright if that information are path of image file on hard disk.

  3. #3
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    No. You need to get the file contents of the image or else it won't work. I also strongly advise you store the MIME type of the image as well if you're going to do this, so you could decode it.. Storing images in the database isn't bad practice unless you have a massive amount of storage data, in which case it is more efficient to store your images in your file system.

    First, you need to get the file contents of the image. Then you need to addslashes so that it doesn't interfere with the single quote character ( ' ) that you have in your SQL query. You should also store the MIME type of the iamge.
    PHP Code:
    <?php

    $file_name 
    "images/mouse.jpg";

    $file file_get_contents($file_name);
    $file addslashes($file);

    $mime_type mime_content_type($file_name);

    ?>
    The contents of file is what you need to store:
    Code:
    INSERT INTO `products` (`serial`, `name`, `description`, `price`, `picture`, `mimeType`) VALUES
    (1, 'View Sonic LCD', '19" View Sonic Black LCD, with 10 months warranty', 250, '$file', '$mime_type'),
    (2, 'IBM CDROM Drive', 'IBM CDROM Drive', 80, '$file', '$mime_type'),
    (3, 'Laptop Charger', 'Dell Laptop Charger with 6 months warranty', 50, '$file', '$mime_type'),
    (4, 'Seagate Hard Drive', '80 GB Seagate Hard Drive in 10 months warranty', 40, '$file', '$mime_type'),
    (5, 'Atech Mouse', 'Black colored laser mouse. No warranty', 5, '$file', '$mime_type'),
    (6, 'Nokia 5800', 'Nokia 5800 XpressMusic is a mobile device with 3.2" widescreen display brings photos, video clips and web content to life', 299, '$file', '$mime_type');

    To decode your image, you need to determine what image you need to recreate. You can do this by using GD library functions. Consider the example below, which selects a product with the serial = 9 (Note: Only select one row for this). First you need to output the mime type to the browser so it knows what it needs to decode. Then output the image data.

    PHP Code:
    <?php

    $details 
    mysql_query("SELECT picture, mimeType FROM `products` WHERE serial = '9'") or die(mysql_error());

    $row mysql_fetch_array($details);

    header("Content-Type: $row['mimeType']");

    echo 
    $row['picture'];

    ?>
    And that's it. That script will decode and show the image. Keep in mind that you can't print out any other content, since the mime type of that page is now an image.
    - Josh

  4. #4
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    Oh, I should mention that it MUST be a BLOB type, so it can store machine language data. Anything else will overload MySQL.
    - Josh

  5. #5
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,627
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    The important distinction here is that your original example does not store the image in the database: it stores the path to the image. jShor's example stores the actual image in the database (which may or may not have been what you actually meant to ask).

    downside of storing the actual image is that it takes a lot of space (as he mentions, BLOB); downside of storing the path is that the path may change and/or you be incorrect depending on where you use it (i.e., "images/pic.jpg" may be correct from the root directory, but it will be incorrect from some other directory).
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  6. #6
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    I've done both techniques and I've found that storing the path to an image is more practical, especially when you want to make backups of your MySQL data.

    The times that I've stored them on a path, I stored them with the web-accessible path (e.g., http://img.website.com/path/image.jpg) as opposed to having the remote directory stored (e.g., /img/path/image.jpg).

    Like Adrian said, you might have a problem with accessing the image depending on where the script is, so you should store the entire URL.
    - Josh

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
  •