Remove values in comma separated list from database

Question

I have a table in my MySQL database called 'children'. In that table is a row called 'wishes' (a comma separated list of the child's wishlist items). I need to be able to update that list so that it only removes one value. i.e. the list = Size 12 regular jeans, Surfboard, Red Sox Baseball Cap; I want to remove Surfboard.

My query right now looks like this

$select = mysql_query('SELECT * FROM children WHERE caseNumber="'.$caseNum.'" LIMIT 1 ');
    $row = mysql_fetch_array($select);

    foreach ($wish as $w) {
        $allWishes = $row['wishes'];
        $newWishes = str_replace($w, '', $allWishes);
        $update = mysql_query("UPDATE children SET wishes='$newWishes' WHERE caseNum='".$caseNum."'");
}

But the UPDATE query isn't removing anything. How can I do what I need?

Answer 1

Using these user-defined REGEXP_REPLACE() functions, you may be able to replace it with an empty string:

UPDATE children SET wishes = REGEXP_REPLACE(wishes, '(,(\s)?)?Surfboard', '') WHERE caseNum='whatever';

Unfortunately, you cannot just use plain old REPLACE() because you don't know where in the string 'Surfboard' appears. In fact, the regex above would probably need additional tweaking if 'Surfboard' occurs at the beginning or end.

Perhaps you could trim off leading and trailing commas left over like this:

UPDATE children SET wishes = TRIM(BOTH ',' FROM REGEXP_REPLACE(wishes, '(,(\s)?)?Surfboard', '')) WHERE caseNum='whatever';

So what's going on here? The regex removes 'Surfboard' plus an optional comma & space before it. Then the surrounding TRIM() function eliminates a possible leading comma in case 'Surfboard' occurred at the beginning of the string. That could probably be handled by the regex as well, but frankly, I'm too tired to puzzle it out.

Note, I've never used these myself and cannot vouch for their effectiveness or robustness, but it is a place to start. And, as others are mentioning in the comments, you really should have these in a normalized wishlist table, rather than as a comma-separated string.

Update

Thinking about this more, I'm more partial to just forcing the use of built-in REPLACE() and then cleaning out the extra comma where you may get two commas in a row. This is looking for two commas side by side, as though there had been no spaces separating your original list items. If the items had been separated by commas and spaces, change ',,' to ', ,' in the outer REPLACE() call.

UPDATE children SET wishes = TRIM(BOTH ',' FROM REPLACE(REPLACE(wishes, 'Surfboard', ''), ',,', ',')) WHERE caseNum='whatever';

Answer 2

Not exactly a direct answer to your question, but like Daren says it's be better having wishes as its own table. Maybe you could change your database schema so you have 3 tables, for instance:

children
-> caseNum
-> childName

wishes
-> caseNum
-> wishId
-> wishName

childrensWishes
-> caseNum
-> wishId

Then to add or delete a wish for a child, you just add or delete the relevant row from childrensWishes. Your current design makes it difficult to manipulate (as you're finding), plus leaves you at risk for inconsistent data.

As a more direct answer, you could fix your current way by getting the list of wishes, explode() 'ing them, removing the one you don't want from the array and implode() 'ing it back to a string to update the database.

Answer 3

I think the best answer to such issue is here The best way to remove value from SET field?

query should be like this which covers the ,value or value, or only value in the comma separated column

UPDATE yourtable
SET
  categories =
    TRIM(BOTH ',' FROM
      REPLACE(
        REPLACE(CONCAT(',',REPLACE(col, ',', ',,'), ','),',2,', ''), ',,', ',')
    )
WHERE
  FIND_IN_SET('2', categories)

Here you can have your condition in where clause. for more details refer above link.

Answer 4

You can create function like this:

CREATE  FUNCTION `remove_from_set`(v int,lst longtext) RETURNS longtext CHARSET utf8
BEGIN
set @lst=REPLACE(@lst, ',,', ',');
set @lng=LENGTH(@lst) - LENGTH(REPLACE(@lst, ',', ''))+1;
set @p=find_in_set(@v,@lst);
set @l=SUBSTRING_INDEX( @lst, ',', @p-1);
set @r=SUBSTRING_INDEX( @lst, ',', @p-@lng);
IF @l!='' AND @r!='' THEN
    return CONCAT(@l,',',@r);
ELSE
    RETURN CONCAT(@l,'',@r);
END IF;
END

Using:

SELECT remove_from_set('1,,2,3,4,5,6',1)

Answer 5

Make wishes table have this format:

caseNumber,wish

Then you get all of a child's wishes like this:

SELECT * FROM children c left join wishes w on c.caseNumber = w.caseNumber WHERE c.caseNumber= ?

Removing a wish becomes:

DELETE from wishes where caseNumber = ?

Adding a wish becomes:

INSERT into wishes (caseNumber,wish) values (?,?)

Returning one wish becomes:

SELECT * FROM children c left join wishes w on c.caseNumber = w.caseNumber WHERE c.caseNumber= ? LIMIT 1

Answer 6

Having the wishes indexed in an array which is thereafter serialized could be an idea, otherwise you would need to retrieve the string, slice it, remove the part you don't want, then concatenate the remains. This can be done by using the explode() function.

If you were to use an array, you would retrieve the array and then sort through it with a loop like this:

// Wishes array:
// Array (
//      [0] Regular Jeans
//      [1] Surfboard
//      [2] Red Sox Baseball Cap
// )

$wishes = $row['wishes']; // This is a serialized array taken from the database
$wishes = unserialize($wishes);

foreach ($wishes as $key => $value) {
    if ($value == 'Surfboard') {
        unset($wishes[$key]);
        break;
    }
}

$wishes = serialize($wishes);
// Update database

Keep in mind that index [1] now won't exist in the array, so if you wish to have a clean array you should loop through the array and make it create a new array by itself:

foreach ($wishes as $wishes) {
    $newArray[] = $wishes;
}