php find some text in a string (if it's there)

Question

Hi All,

I have a MYSQL database of photo galleries; each row contains a field with the list of images included in that gallery, eg:

1,5,134,13,5

these are the IDs of the photos. The same image_id could be included in other galleries.

When I delete a photo, I need to remove the corresponding id from the galleries that contain it. What's the best way to do this in PHP?

I thought of EXPLODEing the field into an array, remove the value and then IMPLODE back and update the DB, but I'm pretty sure there's a better way?

thanks, Patrick

THANKS Galen & all.

I'm a newbie and don't know much (=anything) about normalization. if I understand correctly, you're suggesting to have 2 tables: 1 with all the info about the photo (eg, image_id, name, caption, etc..) and another table with just a list of galleries that use that photo, eg:

gallery_id | image_id 
     1          3  
     1          7  
     1          5  
     2          3  
     2          8

so by deleting from this table WHERE image_id=3, i would remove that photo from two galleries. In this case, however, I would I manage the order of photos? Having a string allowed me to have an ordered list of photos.

Answer 1

This is the exact reason why you normalize your tables. Then you could just run the query

delete from images where image_id=5;
delete from imageXgallery where image_id=5;

Where the images table contains all the image info. The imageXgallery table just contains image to gallery references.

I suggest you read up on normalization and update your tables.

EDIT: To allow for image ordering add an order field in your imageXgallery table. When you retrieve your images from the table you can order by that column.

Answer 2

While there may be slightly faster and more elegant solutions, exploding, cutting out and gluing together again is a perfectly acceptable way in my opinion.

Edit: Of course, Galen is right. If you have the chance, change the structure.

Answer 3

As Galen said, you need to normalize. Instead of a tuple (row) like ("gallery_id", "photo_id_1, photo_id_2, ...") you will have multiple tuples each having one gallery_id and photo_id. Then by deleting the photo_id from that table will take care of your problem.

Answer 4

If you don't want to change your table structure, it's probably less expensive to do string operations than to convert the strings into an array and back again. Look into using either str_replace() or preg_replace() for that.

Answer 5

If you can change the database layout, I would do the following:

As each gallery can have multiple images and as each image can be in multiple gallery, you have a many-to-many relationship. So you have 3 tables, the first one to hold the gallery, with a galleryId primary key and additional fields for gallery info (if galleries have names, for instance, a name field), then you have an image table, with an imageId and all the image information, and then you have third table with just two fields, galleryId and imageId.

So if image 5 has to go to gallery 7, you'd enter 7 and 5 into the relationship table.

to get all the images for gallery 7, you'd run something like

SELECT * FROM images i LEFT JOIN galleryImages gi ON gi.imageId = i.imageId WHERE gi.galleryId=7

with galleryImages being the relationship table.

Likewhise, to delete an image from a gallery, just delete the row in the relationship table. One thing you might want to pay attention to is to check if there are still entries for an image in the relationship table when you remove it from a gallery. or in other words, check if the images is being used in any galleries, if not, remove the entry from the images table as well, otherwhise you might end up with a lot of garbage image entries that aren't even needed anymore.

hope this helps to clear some things up.