我用php（pdo）和mysql做了一个简单的追随者系统。我的问题是，
假设有一个用户名 Mike 的 ID 号 99。迈克有两个追随者，格雷格（id = 77）和珍妮（id = 88）
Greg的以下列表如下所示（1，2，3，4，99），Jenny的以下列表如下所示（5，6，99，7）
我想做的是，当 Mike（99） 删除他的帐户时，我想从 Greg（77） 和 Jenny（88） 的以下列表中删除 ID 号 99。
名为"mytable"的表有3个字段：id（INT），following_list（text），follower_list（text）。
几天来我一直在为这个问题而苦苦挣扎。谁能给我一些建议？提前非常感谢您!!
下面是我的更新功能
public function update_following_list(){
// $this->myid is Mike's id number(99) who is about to delete his account
// Selecting the list of people following Mike
$query = "SELECT 'follower_list' FROM 'mytable' WHERE 'id' = $this->myid";
$result = $this->dbc->query($query);
foreach($result as $row){
$this->follower_list = $row['follower_list'];
// When I echo $this->follower_list, I get 77,88
// Now querying Greg(77) and Jenny(88)'s following_lists, which have Mike's id number(99) in them.
$query = "SELECT 'following_list' FROM 'mytable' WHERE 'id' IN ($this->follower_list)";
$result = $this->dbc->query($query);
foreach($result as $row){
$this->following_list = $row['following_list'];
// When I echo $this->following_list, I get both Greg(1,2,3,4,99) and Jenny(5,6,99,7)'s following lists
// Here, I am turning following list into array by using explode
$this->following_array = explode(",", $this->following_list);
foreach($this->following_array as $key => $value){
if($value == $this->myid){
// Removing Mike(99)'s id number from Greg and Jenny's following lists
unset($this->following_array[$key]);
// Add back commas, which will then become string
$this->new_following_list = implode(",", $this->_following_array);
// When I echo $this->new_following_list, I get both Greg(1,2,3,4) and Jenny(5,6,7)'s new list without Mike(99)'s id number
// My problem starts here. I was able to remove Mike's id number from Greg and Jenny's lists. But I am having a trouble updating Greg and Jenny's following lists with new following lists.
// The update query below does not work...
$query = "UPDATE 'mytable' SET 'following_list' = $this->new_following_list WHERE 'id' IN ($this->follower_list)";
$result = $this->dbc->query($query);
} // End of if($value == $this->myid)
} // End of foreach($this->following_array as $key => $value)
}
}
} // End of function
这不会正确缩放。最好像这样规范化数据模型：
以后
user_id | following_user_id
---------------------------
77 | 1
77 | 2
77 | 3
77 | 4
77 | 99
88 | 5
88 | 6
88 | 7
88 | 99
并添加两个索引：
-
UNIQUE(user_id, following_user_id) -
INDEX(following_user_id)
要获得 99 个关注者：
SELECT * FROM following WHERE following_user_id=99;
要看谁 77 关注：
SELECT * FROM following WHERE user_id=77;
我认为您的数据库设置不是最佳的。将逗号分隔的值存储在一个字段中，尖叫绑定表和一些规范化！
您应该将逗号分隔的值列表设置为表/关系，例如：
followTable:/ user_id | follows
删除迈克的ID非常简单：
DELETE FROM followTable WHERE follows = 99 OR user_id = 99
后者确保迈克和他的追随者之间的联系也被删除。