使用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";
}


}

Answer 1

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.