function for deleting a comma separated value from a column in database using php

Question

I've a table in my data-base which contains two-fields like UserID & MovieIDs. I written a function in php for inserting and updating the table where the MovieIDs is the field that may contain many values which are separated by comma's, similarly a need a function for deleting a value from the table, the function i used for inserting and updating the value in the table looks as follows:

<?php

addmovietowatchlist(2,36546436);

function addmovietowatchlist($userid, $movieid)
{
  $con=mysql_connect('localhost','username','password');
if(!$con)
{
    die('could nt connect 2 the server');
}
mysql_select_db("database", $con);


$useriddb = mysql_query("select MovieIDs from tbl_user_watchlist WHERE UserID = ".$userid);
$count = mysql_num_rows($useriddb);
if($count>0)
{
$mids=mysql_fetch_assoc($useriddb);
$listarr=explode(",",$mids['MovieIDs']);

array_push($listarr, $movieid);
$finallist=implode(",",$listarr);

//update
$result = mysql_query("UPDATE tbl_user_watchlist SET MovieIDs = '".$finallist."' WHERE  UserID = ".$userid);
  if($result)
  echo "updated sucessfully";
}
else{
//insert
$result = mysql_query("INSERT INTO tbl_user_watchlist (UserID,MovieIDs) values (".$userid.",'".$movieid."')");
  echo "inserted successfully";
}

}
?>

Now, i need to write a function for deleting the value from the table along with the comma if already a value is inserted in that row...can anyone help me in writing the code... I tried by writing the below code but it doesn't worked....please help me in getting out of this..

function deletemovietowatchlist($userid, $movieid)
{
  $con=mysql_connect('localhost','username','password');
if(!$con)
{
    die('could nt connect 2 the server');
}
mysql_select_db("database", $con);


$useriddb = mysql_query("select MovieIDs from tbl_user_watchlist WHERE UserID = ".$userid);
$count = mysql_num_rows($useriddb);
if($count>0){
$mids=mysql_fetch_assoc($useriddb);
$listarr=implode(",",$mids);
//echo $listarr;
//array_pop($listarr, $movieid);
$finallist=explode(",",$listarr);
echo $finallist;
$result = mysql_query("DELETE MovieIDs FROM tbl_user_watchlist WHERE UserID=".$userid);
  if($result)
  echo "deleted  successfully";
}


}

Do not use CSV for many-to-many relationships, use another table - trying to use CSV causes the exact problems you have. Create a table called `UserMovies` with a field `UserID` and a field `MovieID`. You can then store each relationship in a separate row, and when you need to delete them you can just `DELETE FROM UserMovies WHERE UserID = x AND MovieID = y`. If you still need to get the CSV list, you can `JOIN`, `GROUP BY` and `GROUP_CONCAT()` — DaveRandom, May 14 '12 at 16:26
Also note that `DELETE MovieIDs FROM tbl_user_watchlist WHERE UserID = x` is not valid - you cannot delete a single column, only a whole row. If you need to delete the value of a column, `UPDATE tbl_user_watchlist SET MovieIDs = NULL WHERE UserID = x` — DaveRandom, May 14 '12 at 16:28
thanks for the suggestion @DaveRandom i tried by using the given query..but in this case all the values in the row are getting deleted..i want to delete only the specified value from the MovieIDs row... — Srinivas V., May 14 '12 at 16:40
@DaveRandom: That comment should be an answer. You're absolutely correct. — Jeffrey Blake, May 14 '12 at 17:19
@abc123: Read Dave's first comment. Your database scheme does not support doing what you are trying to do in even a remotely efficient way. If you separate this into two tables, it becomes trivial. — Jeffrey Blake, May 14 '12 at 17:20
@JeffreyBlake then how come it is possible to add a value into this table by using the above add function??? please tell me how to delete the value along with the comma as how i did for inserting the value into the table..... — Srinivas V., May 14 '12 at 17:27
Yes, it's possible. It's just ugly and inefficient... I don't like it, but I'll throw some code together as an answer for how you could make your current setup function — Jeffrey Blake, May 14 '12 at 17:34
@JeffreyBlake let it be ugly....i just want the functionality...it seems quite well wrking..so am using it...now the similar way i tried to code fr delete functionality but i failed in that.... — Srinivas V., May 14 '12 at 17:39
I believe something along the lines of my answer below should work. — Jeffrey Blake, May 14 '12 at 17:47

Jeffrey Blake · Accepted Answer · 2012-05-14T17:52:20.670

The right way to do this is to separate things out into two tables. One would hold all of the information in the current table, except it would not hold the comma-separated list. Instead, the other table would have two columns, one designating which list it belonged to (e.g. the ID of the row in the first table...or you could just use userid if there's never more than one list for a user), and then a column for the movie.

That said, if it is too much work to change your current system and/or you are bound and determined to stick with an inferior and inefficient database structure, it is possible to make this function in your current setup. Basically, you need to find all references to the movieid that you are concerned with, and then manually adjust the lists corresponding to those matches, and then update all of those rows with new lists. It's a lot of extra code and wastes a lot of processing time (to the point that it could become a major bottleneck in your app as it grows), but it is possible.

The simplest way to make the adjustment will be to do a string replacement on the existing list. So once you have the current list selected, do something like this:

$newList = str_replace($movie_id_to_delete.",", "", $oldList);

$result = mysql_query("UPDATE tbl_user_watchlist SET MovieIDs = '".$newList."' WHERE MovieIDs ='".$oldList."' AND UserID = ".$userid);

Depending on your implementation, it might make sense to check to see if the $newList is empty and delete the entire row (instead of updating the MovieIDs as I do above) if it is empty.

yes...the entire row should not be deleted even there $newList is empty the userID row should be there itself...and thanks for the help... — Srinivas V., May 14 '12 at 17:57

score 1 · Answer 2 · edited May 23 '17 at 11:59

Using php or any other language it can be done with single query, you just have to run the single query as mentioned below, the detailed reference of the query is in the link The best way to remove value from SET field?

query should be like this which covers the ,value or value, or only value in the comma separated column

UPDATE yourtable
SET
  categories =
    TRIM(BOTH ',' FROM
      REPLACE(
        REPLACE(CONCAT(',',REPLACE(col, ',', ',,'), ','),',2,', ''), ',,', ',')
    )
WHERE
  FIND_IN_SET('2', categories)

Here you can have your condition in where clause. for more details refer above link.

function for deleting a comma separated value from a column in database using php

2 Answers2