Extracting all images from mysql database blob to directory

PHP
1

I received a project to fix a broken eccomerce site. The only thing i was given was a mysql dump file.

It seams to have been a custom job, where all the product images are stored in a database blob field. There are literally over 3000 products in this database.

I am battling my brains about the best course of action would be.

  1. Find an open source solution that uses blob image storing
  • great, however, i think there are way to many images here (correct me if I’m wrong or if you know one. It would be really easy to mess around with this dump to get it into another table.
  1. Extract all the images to a directory, while creating a new table with the image details (ie, id, name, image path, etc…)
  • this would be the best solution, however, I have never dealt with blobs and images in mysql. I could use a cart like zencart.

Number 2 is my favorite solution, however I have searched this forum and google and can’t seem to find a solution. Any ideas?

Basic idea (create new table products2)…

  1. select * from products
  2. save product.image to directory images/products/
  3. create 250px w thumb - th_image.jpg
  4. update products2 - image_name, image_cat, image_path
2

I personally cant stand actually storing images in a DB. I would extract them, and then save them to a directory, and then replace the images in the database with the image path.

I don’t think it would be extremely difficult to do, but I cant think of a script to do it off the top of my head.

3

I’ve learned the hard way about storing images in the DB. Go for number 2 (sounds gross huh?) and store the images in a Directory and use a new table to reference those.

In the end - you’ll have a happy a happy customer who will have to pay less to fix the problems caused by the other progammer who stored those imagesin the DB.

4

Interesting, number 2 is the best solution, obviously, but I have never dealt with the blob issue before, what happens when you extract the blob data, can you put it into a file object and does it have all the file properties?

If so, then just write a loop, grab the files from the blob and insert them into a new table.

5

The blob is just the raw data - all you need to do is extract the blob and save it as img.jpg (assuming jpg was the origional format)

Here’s what to do

SELECT * FROM products
foreach image:
generate new id - $id = uniqid(rand(), true);
save image -> images/products/$id.jpg
insert into db -> product name … , … , … , imageid, …

6

thanks for the advice. mrwooster, the question really then is how do I use php to save the blob text as a file as described in your process?

7

Thats the part you are suppose to come up with. Not that hard simple stuff.

8

It might seem simple but really hard to me. I’m pretty sure that the imagecreatefromstring() function is involved, however, using this function to save a file is the hard part. There are no real examples of saving a file using this function in the manual or in the forums…

9

just save the raw binary data to a file using fwrite()

for example
fwrite($fp, $row[‘blob’]);

10

I always prefer to store the images in a directory and use a database table to store information about the images.

To me it seems like a waste of database space to store images.

11

I agree lionheart. The database, as I said, was passed on to me and now I’m trying to extract the information. I am going to try clamcrusher’s approach. however, a few questions.

  1. fwrite() - if file doesn’t exist does it create it? Checked with php manual, no answer.

  2. does all the information of the jpg get saved? ie… how do I tell that it was a jpg, gif, etc. Can I extract that info?

  3. either way does this look right?

$query = mysql_query ("SELECT * FROM products");
while ($row = mysql_fetch_object ($query)) {
    $file = 'images/products/'.$row->id."jpg";
    fwrite($file, $row->blob);

    $SQL="INSERT INTO products2 (id, file, entered) VALUES ('','$file',NOW())";
    $result=mysql_query($SQL) or die(mysql_error());
}
12

You have to use fopen before fwrite, and fopen creates the file if it doesn’t exsit.