这是我的代码来获得他们的个人资料的帖子
<?php
$getposts = mysql_query("SELECT * FROM videos_content WHERE username ='$username' ORDER BY id DESC LIMIT 10") or die(mysql_error());
while ($row = mysql_fetch_assoc($getposts)) {
$id = $row['id'];
$video_title = $row['video_title'];
$video_url = $row['video_url'];
$date_posted = $row['date_posted'];
$added_by = $row['username'];
$get_user_info = mysql_query("SELECT * FROM users WHERE username='$added_by'");
$get_info = mysql_fetch_assoc($get_user_info);
$profilepic_info = $get_info['profile_pic'];
if ($profilepic_info == "") {
$profilepic_info = "./image/default_pic.jpg";
}
else
{
$profilepic_info = "./userdata/profile_pics/".$profilepic_info;
}
?>
我的sql用户表是这样的
[id,username,followers,following,account_active]
->我的following和follower列是用逗号分隔值的数组。
我的sql posts表是这样的
[id,username,video_title,video_url,date_posted,removed]
我想显示用户(我的意思是登录用户)在他/她的主页上跟随的人的帖子。
有谁能给我一些建议吗?
谢谢。
我试过这个代码
<?php
$followingArray = "";
$countFollowing = "";
$followingArray12 = "";
$selectFollowingQuery = mysql_query("SELECT following FROM users WHERE username = '$user' ");
$followingRow = mysql_fetch_assoc($selectFollowingQuery);
$followingArray = $followingRow['following'];
if ($followingArray != "") {
$followingArray = explode(",","$followingArray");
$countFollowing = count($followingArray);
$followingArray12 = array_slice($followingArray, 0, 12);
$getposts = mysql_query("SELECT * FROM videos_content WHERE username ='$username' ORDER BY id DESC LIMIT 10") or die(mysql_error());
while ($row = mysql_fetch_assoc($getposts)) {
$id = $row['id'];
$video_title = $row['video_title'];
$video_url = $row['video_url'];
$date_posted = $row['date_posted'];
$added_by = $row['username'];
$get_user_info = mysql_query("SELECT * FROM users WHERE username='$added_by'");
$get_info = mysql_fetch_assoc($get_user_info);
$profilepic_info = $get_info['profile_pic'];
if ($profilepic_info == "") {
$profilepic_info = "./image/default_pic.jpg";
}
else
{
$profilepic_info = "./userdata/profile_pics/".$profilepic_info;
}
}
if (in_array($username, $followingArray) {
echo '(posts)'; /*html to echo those posts, its a very long chain of divs, that is the reason i didn't include that code.*/
}
else{
echo ''; /*nothing*/
}
}
?>
首先,我建议将您的users
表分成3个表。
users : [id,username,account_active]
followers : [id,followerId]
following : [id,followingId]
这将防止列中有逗号分隔的值,使您的问题更简单。
从那里很容易:
SELECT *
FROM posts
WHERE id IN (
SELECT followerId
FROM followers
WHERE id = $userID
);
等。