Can I store images in MySQL [duplicate]

Possible Duplicates:
Images in MySQL
Storing images in MySQL

I'm trying to develop a website where users upload their images as part of registration. I want it that for each image, there should be a thumb created with PHP (which is not that difficult). I want to save the thumbs (since they are very small) in the database and I use MySQL. (I don't want to save the thumbs as physical files on the drive.)
Does MySQL allow saving and retrieving image data and how do I go about it? If it doesn't support image data, is there any free database that does? I will be happy if a link can be provided.
Thanks.

Solution 1:

Yes, you can store images in the database, but it's not advisable in my opinion, and it's not general practice.

A general practice is to store images in directories on the file system and store references to the images in the database. e.g. path to the image,the image name, etc.. Or alternatively, you may even store images on a content delivery network (CDN) or numerous hosts across some great expanse of physical territory, and store references to access those resources in the database.

Images can get quite large, greater than 1MB. And so storing images in a database can potentially put unnecessary load on your database and the network between your database and your web server if they're on different hosts.

I've worked at startups, mid-size companies and large technology companies with 400K+ employees. In my 13 years of professional experience, I've never seen anyone store images in a database. I say this to support the statement it is an uncommon practice.

Solution 2:

You'll need to save as a blob, LONGBLOB datatype in mysql will work.

Ex:

CREATE TABLE 'test'.'pic' (
    'idpic' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    'caption' VARCHAR(45) NOT NULL,
    'img' LONGBLOB NOT NULL,
  PRIMARY KEY ('idpic')
)

As others have said, its a bad practice but it can be done. Not sure if this code would scale well, though.

Solution 3:

You can store images in MySQL as blobs. However, this is problematic for a couple of reasons:

• The images can be harder to manipulate: you must first retrieve them from the database before bulk operations can be performed.
• Except in very rare cases where the entire database is stored in RAM, MySQL databases are ultimately stored on disk. This means that your DB images are converted to blobs, inserted into a database, and then stored on disk; you can save a lot of overhead by simply storing them on disk.

Instead, consider updating your table to add an image_path field. For example:

ALTER TABLE `your_table`
ADD COLUMN `image_path` varchar(1024)

Then store your images on disk, and update the table with the image path. When you need to use the images, retrieve them from disk using the path specified.

An advantageous side-effect of this approach is that the images do not necessarily be stored on disk; you could just as easily store a URL instead of an image path, and retrieve images from any internet-connected location.

Solution 4:

You will need to store the image in the database as a BLOB.

you will want to create a column called PHOTO in your table and set it as a mediumblob.

Then you will want to get it from the form like so:

$data = file_get_contents($_FILES['photo']['tmp_name']);

and then set the column to the value in $data.

Of course, this is bad practice and you would probably want to store the file on the system with a name that corresponds to the users account.