storing images in database

[ravi951]

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.

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.....

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...

[mouseinhome]

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.

[JShor]

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

$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:

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

$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.

[JShor]

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

[traq]

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).

[JShor]

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.