Comparing comma separated list to MySQL Table using PHP


I have a HTML form that accepts a comma separated list of tags, which works just like the one here on stackoverflow. I also have a MySQL table that contains lists of tags that belong to each user. The user can update their tags in their profile, so what I would like to do is automatically remove any tags from the MySQL table if it no longer exists in the list

For example, the user enters the following on the web form:

trance, house, electronica, dubstep, club

The MySQL table however, has this

userid    tag
1         trance
1         house
1         electronica
1         dubstep
1         garage

I would like to remove the garage tag from MySQL and add "club" instead.

It is also worth noting that while these tags appear in order in my example above, this won't always be the case, so I can't rely on the array index position in both, for example.

I realise I could probably loop through the comma separated list with a foreach and generate a delete statement, and re-insert all the tags from scratch but I feel that there is probably a better way to do this.

Thanks in advance for your guidance.





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');

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.





  • 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";

foreach($s as $ss) {
  //Escape the string according to your DB framework
  //e.g. $ss=mysql_escape_string($ss);

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

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

People are also looking for solutions to the problem: php - Insert values into two tables that are linked (1:M)


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.