Geek Answers Handbook

Delete values ​​in a comma-separated list from the database

I have a table in my MySQL database called "children". This table contains a row with the name "wishes" (a list of items in the list of desired items, separated by commas). I need to update this list so that it only removes one value. that is, list = size of 12 regular jeans, surfboards, Red Sox baseball cap; I want to remove Surfboard.

Now my query 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 does not delete anything. How can I do what I need?

+5
source share
6 answers

REGEXP_REPLACE(), :

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

, REPLACE(), , "Surfboard". , , , , "" .

, , :

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

, ? "Surfboard" . TRIM() , "" . , , , , , .

. , . , , , .

, , REPLACE(), , . , , . , ',,' ', ,' REPLACE().

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

, , , . , , 3 , :

children
-> caseNum
-> childName

wishes
-> caseNum
-> wishId
-> wishName

childrensWishes
-> caseNum
-> wishId

, , childrensWishes. ( ), .

, , (), , , (), string .

+1

:

caseNumber,wish

:

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

:

DELETE from wishes where caseNumber = ?

:

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

:

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

, , , , , , , , . , explode().

, , :

// 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

, [1] , , , :

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

, SET?

, , ,

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

where. . .

0

​​:

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

:

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

More articles:


All Articles