MySql - 替换“array”-value

Question

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?

Answer 1

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.