>我有以下三个表 - users_thoughts
,users
和post_favourites
。我的社交网站中有一个功能,允许用户收藏帖子(帖子存储在user_thoughts
中,当帖子被收藏时,即当调用favourite_post.php
时,它会将收藏夹存储在post_favourites
表中)。
如果登录用户没有收藏帖子,它将显示Glyphicon heart-empty
。但是,如果用户收藏了某个帖子,则会显示Glyphicon-heart
。
假设我的表中有以下行:
users
表:
id: 1
first_name: conor
id: 2
first_name: Alice
id: 3
first_name: Anderson
user_thoughts
表:
id: 100
message: This is a post by Alice.
added_by: Alice
id: 101
message: This is a post by Anderson
added_by: Anderson
post_favourites
表:
id: 1
user_id: 1 (This is the id of the user who has favourited the post, see users table)
thought_id: 101
假设我以 Conor
身份登录。如您所见,Conor
收藏了Anderson
的帖子,因此应该会出现Glyphicon-heart
,因为登录用户已经收藏了安德森的帖子。但Glyphicon-heart-empty
出现了,尽管我的数据库说康纳喜欢安德森的帖子。
以下是我的查询:
注意:$username
是为登录用户创建的会话变量。
$count = mysqli_query ($connect, "SELECT * FROM user_thoughts");
while ($row = mysqli_fetch_assoc($get_thoughts_from_db)) {
$thought_id = $row['id'];
}
// Get all user_ids attachted to a thought ($thought_id)
$get_user_id = mysqli_query ($connect, "SELECT * FROM post_favourites WHERE thought_id = '$thought_id'");
$id_fetch = mysqli_fetch_assoc ($get_user_id);
$all_user_id = $id_fetch ['user_id'];
$post_id = $id_fetch ['thought_id'];
// get id of users from users table
$get__id = mysqli_query ($connect, "SELECT id FROM users WHERE username = '$username'");
$id_fetch2 = mysqli_fetch_assoc ($get__id);
$logged_in_user = $id_fetch2 ['id'];
if ($post_id == $thought_id){
// If the post has already been favourited by the username, then display this icon with funtionality.
if ($all_user_id == $logged_in_user){
echo "$get_num_of_favs
<a href='/inc/unfavourite_post.php?id=";?><?php echo $thought_id;?><?php echo "'>
<span class='glyphicon glyphicon-heart' aria-hidden='true' style='padding-right: 5px;'></span>
</a>";
}
// if the post hasn't been favourited by the username, display this icon.
else {
echo "$get_num_of_favs
<a href='/inc/favourite_post.php?id=";?><?php echo $thought_id;?><?php echo "'>
<span class='glyphicon glyphicon-heart-empty' aria-hidden='true' style='padding-right: 5px;'></span>
</a>";
}
}
我需要它,因此登录用户已经收藏的帖子会用heart
图标显示,即执行 if 语句。但目前,安德森的This is a post by Anderson
帖子 - 康纳最喜欢的,显示heart-empty
- 这意味着else声明正在执行,我不知道为什么?
如果没有您的数据,我无法确定,但我会把钱花在不止一个用户喜欢这篇文章上。然后,返回的第一个结果由
$get_user_id = mysqli_query ($connect, "SELECT * FROM post_favourites WHERE thought_id = '$thought_id'");
将没有当前登录用户的user_id。您应该将 $logged_in_user 添加到 WHERE 子句中:
$get_user_id = mysqli_query ($connect, "SELECT * FROM post_favourites WHERE thought_id = '$thought_id' AND user_id = '$logged_in_user'");
(查询前不要忘记转义任何数据!
php 中的整个复杂逻辑可以通过在 sql 查询中使用连接来替换,以合并所有相关表中的数据:
select *
from user_thoughts u
left join post_favourites p on u.id=p.thought_id and u.user_id=$_SESSION['userid']
$_SESSION['userid']
是当前登录用户的用户 ID。将其与用户名一起存储在会话中。
当你在 php 中循环访问结果集时,如果 thought_id==''(空字符串),那么你就知道当前用户还不喜欢这篇文章。