How to update serialize data in MySQL

Question

I have this serialize data in my mySQL database

a:4:{i:0;s:7:"bvl.png";i:1;s:8:"ccop.jpg";i:2;s:11:"reyborn.png";i:3;s:13:"swopgroup.jpg";}

How can I update this data, for example I want to delete ccop.jpg?

Answer 1

Do not store serialized data in database.

Create a linked table consists of main_id and picture and store your image names in it.
So, you will have distinct access to them.

Answer 2

You have to fetch the value from the database, unserialize it, remove the element, serialize it and save again.

$remove = "ccop.jpg";

//
// get the string from the database
//

$arr = unserialize($str);

foreach($arr as $key => $value)
  if ($value == $remove) unset($arr[$key]);

$str = serialize($arr);

//
// save the string back to the database
//

---

Instead of saving serialized list of values, it's better to have a normalized database and just do a simple DELETE FROM images WHERE object_id = ....

Answer 3

Ideally you need to

• extract it
• deserialize it
• modify it
• serialize it
• write it back to the database.