Hey all!
I'm a very novice MYSQL user of the mentality of "get it working, then get
it working the right way," feeling the best way to learn is to just do it.
I got things working but now I'm looking back and trying to get better
efficiency out of my SQL statements.
Database is setup like this:
Have one table using primary keys called "fcsets"
Have a second table called "fcusers" with a field "vsets" which is a comma
separated list of primary keys referring to the first table
Users can dynamically delete entries from the first table, so I want to be
able to remove that entry from all users who have it in their csl vsets in
fcusers. This is a PHP statement but I think you'll get the general idea of
how I'm doing it. Am looking for some suggestions on how to do this more
efficiently:
UPDATE fcusers SET vsets=TRIM(BOTH ',' FROM CONCAT_WS(',',
SUBSTRING_INDEX(`vsets`, ',', FIND_IN_SET('{$setkey}', `vsets`) - 1),
SUBSTRING_INDEX(`vsets`, ',', FIND_IN_SET(REVERSE('{$setkey}'),
REVERSE(`vsets`)) * -1 + 1))) WHERE vsets REGEXP '^.*,?{$setkey},?.*$'
Where $setkey obviously refers to the key of the entry in fcsets being
deleted.
Thanks!
- Jeff
