UPDATEing an array of rows

[fheppell]

As part of my web app, users need to be able to move multiple images from one album to another. I'm writing an UPDATE function, as the current album is stored with the image (helpfully, the albumid is the only value that needs changing).

Here is the function:

function imageUpdate($image_key_array, $new_album_id)
{
    foreach ($image_key_array as $key => $value) {
        $statement = $db->prepare("UPDATE `images` SET `image_album_id` = ? WHERE `image_key` = ?");
        $statement = bind_param('ss', $new_album_id, $key);
        $statement->execute();
        $statement->bind_result($result);
        return $statement->fetch();
    }
}

This function is fine, unless the user wants to move more than a few images. In my app, the free account can have around 40 images, moving all of them would take a very long time, and use up a ton of server resouces. Is there any way I can make this a more efficent function? Thanks!

[diafol]

you could use the IN clause with WHERE...

$image_keys = implode("','", array_keys($image_key_array));

$statement = $db->prepare("UPDATE `images` SET `image_album_id` = ? WHERE `image_key` IN ('$image_keys')");
$statement = bind_param('s', $new_album_id);
$statement->execute();

I think :)

[fheppell]

Thanks! Presumabley this will work in a similar manner for DELETE?

[diafol]

Presumabley this will work in a similar manner for DELETE?

Try it and see. Always test on a duplicated DB though :)