0

I am trying to write a function to process images in mySQL / PHP but cannot work out how to store the results. I have included a stripped down version of the code to demonstrate the problem.

The blobs in image.image_o are all correctly stored and can be used to output images to the web page. The function runs without error but afterwards the blobs in image.image_r are just a few bytes long and contain text like "Resource id #5"

I am sure I am doing something dumb - just can't see what it is.

function process_images(){
    global $mysql
    $sql = "select id, image_o from image";
    $res = mysqli_query($mysqli, $sql);
    if ($res){
        while ($ay = mysqli_fetch_array($res, MYSQLI_ASSOC)){
             $id = $ay['id'];
             $im = imagecreatefromstring($ay['image_o']);
             // do something useful with the image
             $sql2 = "update image set image_r = '{$im}' where id = $id";
             $res2 = mysqli_query($mysqli, $sql2);
             if ($res2){
                 // blah blah
             }else{
                 echo mysqli_error($mysqli)." in res2";
             }
         }
    }else{
        echo mysqli_error($mysqli)." in res"; 
    }
}
Lee Taylor
  • 7,761
  • 16
  • 33
  • 49
John Buckwell
  • 111
  • 2
  • 9
  • 5
    You know it's not advisible to store images in a database? You are killing performance for the sake of?... – Layke Oct 30 '12 at 21:56
  • Database is not right place for images. Store your images on regular filesystem, and keep reference (filenames + path etc) in DB – Marcin Orlowski Oct 30 '12 at 21:58
  • I generally agree with the above. Just to play Devil's advocate, you might want to store files in the db if you had multiple application servers and a single db, but didn't have a good way to set up a shared file system due to hosting restraints or other limitations. It's certainly not ideal, but that's not the point. – Joshua Kaiser Oct 30 '12 at 22:40
  • I think this old chestnut needs challenging. There are many good reasons for keeping images in a database - security, integrity and convenience among them. Performance is not one-size-fits-all. If I was serving many images in each page and many pages a second I would think long and hard about the speed of image retrieval. Performance optimization is about picking the low hanging fruit. Try actually measuring the time to serve the image, then compare to building the rest of the page and the network latency then judge if the db image overhead is worth addressing. – John Buckwell Oct 30 '12 at 22:42
  • 1
    @JoshuaKaiser - It's blob not blog, also you don't include tags in the title ;-) – Lee Taylor Oct 30 '12 at 22:44
  • 1
    @LeeTaylor Oops. That's totally my bad. Thanks for fixing that. – Joshua Kaiser Oct 30 '12 at 22:46

1 Answers1

0

I agree with the commentary above that this is generally not advisable. However, here is a great article on why you MIGHT do it, and how. It also highlights some of the cons of storing images in the database.

http://forum.codecall.net/topic/40286-tutorial-storing-images-in-mysql-with-php/

You need to make sure you read the data into the field. Not just the file pointer:

// Temporary file name stored on the server
$tmpName  = $_FILES['image']['tmp_name'];  

// Read the file 
$fp     = fopen($tmpName, 'r');
$data = fread($fp, filesize($tmpName));
$data = addslashes($data);
fclose($fp);

// Now take the contents of data and store THAT

In a nutshell, imagecreatefromstring returns "An image resource will be returned on success", not the contents of the file itself. You need to read the contents of that resource before you can store it. Using your code, make the following changes:

function process_images(){
    global $mysql
    $sql = "select id, image_o from image";
    $res = mysqli_query($mysqli, $sql);
    if ($res){
        while ($ay = mysqli_fetch_array($res, MYSQLI_ASSOC)){
             $id = $ay['id'];
             $im = imagecreatefromstring($ay['image_o']);
             $tempFileName = '/tmp/' . $id . 'jpg';
             $imTempFile = imagegd($im, $tempFileName); 
             $fh = fopen($tempFileName, "r");
             $data = fread($fh, filesize($tempFileName));

             // do something useful with the image
             $sql2 = "update image set image_r = '{$data}' where id = $id";
             $res2 = mysqli_query($mysqli, $sql2);
             if ($res2){
                 // blah blah
             }else{
                 echo mysqli_error($mysqli)." in res2";
             }
             //delete the temp file
             unlink($tempFileName);
         }
    }else{
        echo mysqli_error($mysqli)." in res"; 
    }
}
Joshua Kaiser
  • 1,461
  • 9
  • 17
  • Hi Joshua, thanks for your reply but you seem to be saying my blob is just a reference to the original file while actually it is a coded string that contains the complete image. The code you suggest produces an "fread(): supplied resource is not a valid stream resource in" . – John Buckwell Oct 31 '12 at 07:43
  • Yes, upon further inspection it looks like you are attempting to pull the image out of the database in one column and then put it back into another one? I'm not sure why you would do this. At the line that says "$im = imagecreatefromstring($ay['image_o']);", try doing a var dump on $im. I'm curious what you are getting back. It should be a file pointer, but will be an error if $ay['image_o'] can't be interpreted as an image. You may need to start over at the point where the images are being stored. – Joshua Kaiser Oct 31 '12 at 15:11
  • You are right, my example code attempts to copy an image from one column to another. The real code would manipulate the image in GD but I removed that to highlight the problem. The images in image.image_o are OK, I know this because I can use them to serve images on a web page. There is something wrong with the way I am trying manipulate the blobs in code - just don't know what. The call to imagecreatefromstring($ay['image_o']) produces an object that GD can manipulate. I suspect I want a function that does the inverse: "stringcreatefromimage", trouble is, it doesn't exist. – John Buckwell Nov 01 '12 at 09:28
  • @JohnBuckwell Ok, so with the code above, what does a var dump on $im produce? – Joshua Kaiser Nov 01 '12 at 17:55
  • Hi Joshua, sorry for the delay - had to work on other projects. Anyway, a var_dump of $im produces output like "resource(3) of type (gd) " – John Buckwell Nov 07 '12 at 08:43
  • There's the issue. It's an image resource, not a file resource. Try dumping the image into a temp file with imagegd. http://www.php.net/manual/en/function.imagegd.php. Fopen the temp file, and fread that into your database. – Joshua Kaiser Nov 08 '12 at 18:16
  • @JohnBuckwell I corrected the code to make it work. Make sure if you use a tmp file then it won't collide with something else in that directory. – Joshua Kaiser Nov 08 '12 at 18:23