0

I have a comma separated list stored in one row in a column in a mysql database (eg. phrase1, phrase2, phrase3, phrase4). I want to write a query that will deleted one of the values from that comma separated list, say 'phrase3', and its comma, making the list (phrase1, phrase2, phrase4). Ideas?

The list is generated with the following code if that helps:

$q6 = "UPDATE post SET denied_user = CONCAT_WS(', ',denied_user,'$claimer_id'), denied_time = CONCAT_WS(', ',denied_time,NOW()) WHERE  post_id = '$post_id' AND user_id='$user_id' AND denied_user<>'' AND denied_user NOT LIKE '%$claimer_id%' AND post_status='active";
Daniel Widdis
  • 8,424
  • 13
  • 41
  • 63
user3745602
  • 315
  • 1
  • 3
  • 12
  • 10
    "I have a comma separated list stored in one row in a column in a mysql database ". Well, fix your data structure so you have a junction table instead. Then you can perform the set-based operations that relational databases are designed for. – Gordon Linoff Jun 24 '14 at 14:29
  • 2
    @Gordon is 100% correct. You will spend more time writing kludges to get around the bad data than actually working with it. Never store multiple values in one cell. You will regret it later. – durbnpoisn Jun 24 '14 at 14:31
  • 1
    You can use simple REPLACE function from mysql, http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace – kidz Jun 24 '14 at 14:31
  • 1
    http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Andy Lester Jun 24 '14 at 14:40
  • @Gordon, user3745602 first needs to learn renaming ;) - on the constructive side: http://en.wikipedia.org/wiki/Database_normalization – Quicker Jun 25 '14 at 14:51

0 Answers0