php - Remove Database Duplicates with Similar Name -

i have table in database stores name of image files can retrieved on server.

the table description follows:

+------------+-----------------+------+-----+-------------------+-------+ | field      | type            | null | key | default           | | +------------+-----------------+------+-----+-------------------+-------+ | id         | varchar(50)     | no   | pri | null              |       | | userid     | varchar(8)      | no   |     | null              |       | | albumid    | varchar(25)     | no   |     | null              |       | | image_name | varchar(256)    | no   |     | null              |       | | status     | int(1) unsigned | no   |     | null              |       | | comments   | varchar(4000)   | yes  |     | null              |       | | mod_date   | timestamp       | no   |     | current_timestamp |       | +------------+-----------------+------+-----+-------------------+-------+ 

i have separate php script scans image folders new files , adds them database. however, trying update database deleted folders (for every single userid , albumid...) along old images , recreated folder set of new images new names.

this left website displaying broken picture links of old set of images status , comments made on there, while new set of images displays correctly no status , comment information stored.

out of fields in table. fields in table tells old , new apart id, mod_date (which both automatically generated) , image_name.

the image name both share same string pre-pended different paths information.

example:

old image name: xxx_02420624_20100308-00231_a.png

new image name: yyy_02420624_20100308-00231_a.png

notice 02420624_20100308-00231_a.png being shared both old , new different pre-pends.

my question is.. if not albums being updated (i.e. though folders users , albums deleted , added back, not of them have new file names), efficient way of removing these duplicate data , still keep comments , status assigned each image?

i figure can delete new rows of images , rename old image name new 1 points correctly right image , still retains status/comments. however, how write general script search new images automatically stored in db?

edit: newly added image names has format of

yyy_userid_albumid_aaa.png

where yyy pre-pend identical new images, userid , albumid pretty self-explanatory, , aaa unique image name.

since know xxx , yyy both constants, can write script in php automatically looks through database , delete entries has image_name beginning yyy , change xxx yyy image points right path , still retains status , comment. question is: how can write script search within string of 'image_name' pre-pend until hit 8 digit userid (that starts 0)?

seems need link new names old images' records.

the sql below should update image names of old 'xxx' images new image names. have delete new records added (ones prefixed yyy)

(untested)

update the_table t  set t.image_name =  (select coalesce(t2.image_name,t.image_name)  the_table t2  = t2.image_name = 'yyy' || substring(t.image_name,<size of xxx>))