Comparing comma separated list to MySQL Table using PHP

Solution:

You need only to place (after properly escaping and quoting) your comma separated list into aNOT IN() clause and use it in aDELETE statement like the following. The order doesn't matter.

DELETE FROM tags WHERE userid = 1 AND tag NOT IN ('trance', 'house', 'electronica', 'dubstep', 'club');
Copy code

How you go about escaping and quoting the comma-separated values will depend on the database API you are using, be it PDO, MySQLi, or the old (and soon to be deprecated)mysql_*() functions.

I'll note that you've done the right thing by storing tags one per row, rather than storing them as the CSV list in a single column.

Solution:

Assuming

• there are noc commas allowed in an interest name,
• and there is a UNIQUE INDEX(userid,tag) on the table

do something like

$s="trance, house, electronica, dubstep, club";
$userid=1;

$s=explode(',',$s);
$i=0;
foreach($s as $ss) {
  $ss=trim($ss);
  //Escape the string according to your DB framework
  //e.g. $ss=mysql_escape_string($ss);
  $s[$i++]=$ss;
}

$ss=implode("','",$s);
$sql="DELETE FROM tablename WHERE userid=$userid AND tag NOT IN('$ss')";
//Run $sql accodring to your DB framework

if (sizeof($s)>0) {
  $i=0;
  foreach($s as $ss) $s[$i++]="'$userid','$ss'";
  $ss=implode('),(',$s);
  $sql="INSERT IGNORE INTO tablename VALUES ($ss)";
  //Run $sql accodring to your DB framework
}
Copy code