MySql - 替换“array”-value

I have records like "1,3,6,17" in a column and use the following to delete e.g. "6" from them:

$sql = "SELECT SeId, SeCatId FROM series WHERE FIND_IN_SET(:catid,SeCatId)";
$stmt = $dbh->prepare($sql);
$stmt -> bindParam('catid',$id);
$stmt->execute();
$result = $stmt->FetchAll();
foreach($result as $row){
  $seid = $row['SeId'];
  $ids = explode(",",$row['SeCatId']);
  $i = 0;
  foreach($ids as $catid) {
    if($catid===$id){unset($ids[$i]);}
    $i++;
  }
  $ids = implode(",",$ids);
  $sql = "UPDATE series SET SeCatId = :catid WHERE SeId = :id";
  $stmt = $dbh->prepare($sql);
  $stmt -> bindParam(':catid',$ids);
  $stmt -> bindParam(':id',$seid);
  $stmt->execute();
}

Works fine, but seems to be complicated. Is there an easier way to do the same?

You can use array_diff and do something like

$stmt->bindParam(':catid', implode(',', array_diff(explode(",", $row['SeCatId']), array('6')));

But your problem is not PHP but your SQL schema. You should not have "array" value inside a same field (read database normalization to know why). Normalization is here to avoid such problems. You should have a separate schema having SeId and SeCatId as column, SeId being a foreign key to your series table and SeCatId is a unique integer (not an array).

You should have rows like:

SeId | SeCatId
1    | 1
1    | 3
1    | 6
1    | 7

And then deleting one category is only a matter of simple DELETE statement.

If you used a better database design it would be easy. Instead of storing IDs in comma separated string, create a new table to handle the associations.

series_cat_assoc ( SeId, SeCatId )

Using that, you can insert multiple rows if a series has multiple categories. This makes creating, updating a deleting easy. For example to remove a given category from a given series:

$sql = "SELECT series_cat_assoc WHERE SeCatId = :catid and SeId = :seid)";

I don't know if there's a more efficient way algorithmically, but from what I understand of your code, you can do it cleaner using regular expressions. Replace this:

foreach($result as $row){
$seid = $row['SeId'];
$ids = explode(",",$row['SeCatId']);
$i = 0;
foreach($ids as $catid) {
  if($catid===$id){unset($ids[$i]);}
  $i++;
}
$ids = implode(",",$ids);

With this:

$ids = str_replace(','.$id.',', ',', $row['SeCatId']);
$ids = preg_replace('/(^|,)('.$id.')(,|$)/', "{$0}{$2}", $ids);

I strongly, strongly, strongly recommend that you store your data using an association/junction table. Sometimes, though, you might be stuck with a database that has a bad format, and you have to do operations like this. If so, you can do:

select replace(trim(replace(replace(concat(',', SeCatId, ','), concat(',', 6, ','), ','), ',', ' '), ' ', ',')

The idea is the following:

1. Put commas on the beginning and end of the list, so all elements are surrounded by delimiters
2. Replace the value surrounded by commas (so "16" is not affected)
3. Replace all commas with spaces
4. Use trim() to remove the first and last spaces
5. Replace the spaces with commas

You can do this in a single update:

update table t
    set SeCatId = replace(trim(replace(replace(concat(',', SeCatId, ','), concat(',', 6, ','), ','), ',', ' '), ' ', ',')
    where concat(',', SeCatid, ',') like concat('%,', 6, ',%');