使用php从数据库中的列中删除逗号分隔值的函数
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";
}
}
我的数据库中有一个表，其中包含两个字段，如UserID＆amp; MovieIDs。 我在php中编写了一个函数用于插入和更新表，其中MovieIDs是可能包含许多值的字段，这些值由逗号分隔，类似地需要一个从表中删除值的函数，我用于插入和更新的函数 表中的值如下所示： p>
＆lt;？php
addmovietowatchlist（2,36546436）;
function addmovietowatchlist（$ userid，$ movieid）\ n {
$ con = mysql_connect（'localhost'，'username'，'password'）;
if（！$ con）
{
die（'can nt connect 2 the server'）;
} \ nmysql_select_db（“database”，$ con）;
$ useriddb = mysql_query（“从tbl_user_watchlist选择MovieIDs，用户ID =”。$ userid）;
$ count = mysql_num_rows（$ useriddb）;
if（$ count＆gt; 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 =”。$ u serid）;
if（$ result）
echo“updated sucessfully”;
}
else {
// insert
$ result = mysql_query（“INSERT INTO tbl_user_watchlist（UserID，MovieIDs）”value（“。$ userid。“，'”。$ movieid。“'）”）;
echo“插入成功”;
}
}
？＆gt;
code> pre>
\ n 现在，我需要编写一个函数来删除表中的值以及逗号（如果已经在该行中插入了值）...任何人都可以帮我编写代码...
我试过了 通过编写下面的代码，但它没有用....请帮助我摆脱这个.. p>
function deletemovietowatchlist（$ userid，$ movieid）
{
$ con = mysql_connect（'localhost'，'username'，'password'）;
if（！$ con）
{
die（'无法连接2服务器'）;
}
mysql_select_db （“database”，$ con）;
$ useriddb = mysql_query（“从tbl_user_watchlist选择MovieIDs，用户ID =”。$ userid）;
$ count = mysql_num_rows（$ useriddb）;
if（$ count＆gt ; 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“已成功删除”;
}
}
code> pre>
div>
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.
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.