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 a
NOT IN()clause and use it in a
DELETEstatement like the following. The order doesn't matter.
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)
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.
do something like