快速总结：我有一个社交网站,用户可以关注/发布/投票等.我正在为每个用户个人资料添加一个类似Facebook的新活动墙功能.

我已经在这个位上工作了几天,似乎无法让它正常工作.我想要的是,就像我提到的Facebook墙一样……当访问者访问用户的个人资料时,该用户的活动将显示,他们最近是否“投票”在帖子上,评论或发布了自己的内容,我希望它显示在他们的个人资料上,当然,最新的条目在顶部.

我现在遇到的问题是：它显示了两次事情并且没有按最近的顺序排序.

我在这里使用3个表(我只发布了我正在使用的列)：

Table structure for table votes_posts
Field   Type    Null    Default
indexer int(11) No  
post_id int(11) No  
vote    int(11) No  
user_name   varchar(250)    No  
date    varchar(255)    No  

Table structure for table posts
Field   Type    Null    Default
id  int(11) No  
name    varchar(30) No  
content text    No  
datetimerss varchar(255)    No  
category    int(11) No  

Table structure for table comments
Field   Type    Null    Default
id  int(12) No  
post_id int(12) No  
name    varchar(30) No  
content text    No  
type    varchar(30) No  
datetimerss varchar(255)    No

$users只是$_GET [‘user’]
timeAgo()函数只显示非常受欢迎的“posted xx ago”日期时间.

<ul class="streamActivity">
<?php
$checkActivity = "SELECT votes_posts.post_id, votes_posts.user_name, votes_posts.vote, votes_posts.date, 
                        posts.id, posts.content, posts.name, posts.datetimerss, categories.category, 
                        comments.post_id AS comm_postid, comments.content AS comm_content, comments.name AS comm_name, comments.datetimerss AS comm_date, comments.type 
                        FROM `votes_posts` 
                        LEFT OUTER JOIN `posts` ON votes_posts.post_id = posts.id 
                        LEFT OUTER JOIN `comments` ON posts.id = comments.post_id 
                        LEFT OUTER JOIN `categories` ON posts.category = categories.cat_id  
                        WHERE (votes_posts.post_id = posts.id AND user_name = '$user')
                        OR (comments.post_id = posts.id AND comments.name = '$user')
                        OR (posts.name = '$user') ORDER BY votes_posts.date, posts.datetimerss, comments.datetimerss ASC";
$checkActivityResult = mysql_query($checkActivity);

if (mysql_num_rows($checkActivityResult) > 0) {
    while ($sessionAct = mysql_fetch_array($checkActivityResult)) {
        $category = strtolower($sessionAct['category']);
        $dateVote = timeAgo($sessionAct['date']);
        $datePost = timeAgo($sessionAct['datetimerss']);
        $dateComm = timeAgo($sessionAct['comm_date']);

        $namePost = $sessionAct['name'];
        $nameComm = $sessionAct['comm_name'];
        $nameVote = $sessionAct['user_name'];

        $idVote = $sessionAct['post_id'];
        $idPost = $sessionAct['id'];
        $idComm = $sessionAct['comm_postid'];

        $contentPost = $sessionAct['content'];
        $contentComm = $sessionAct['comm_content'];
        $typeComm = $sessionAct['type'];

        if ($namePost == $user) {
            $classList = 'storyPost';
            $classIcon = 'iconMuttr';

            $name = $namePost;
            $content = $contentPost .' ... read more';
            $datetime = $datePost;
        }

        elseif ($nameComm == $user && $typeComm == "Comment") {
            $classList = 'actionPost';
            $classIcon = 'iconComment';

            $name = $nameComm;
            $content = 'Commented "'. $contentComm .'"';
            $datetime = $dateComm;
        } elseif ($nameComm == $user && $typeComm == "Advice") {
            $classList = 'actionPost';
            $classIcon = 'iconAdvice';

            $name = $nameComm;
            $content = 'gave Advice "'. $contentComm .'"';
            $datetime = $dateComm;
        }

        elseif ($nameVote == $user) {
            if ($sessionAct['vote'] == "1") {
                $classList = 'actionPost';
                $classIcon = 'iconLaughed';

                $name = $nameVote;
                $content = 'Laughed at "'. $contentPost .'"';
                $datetime = $dateVote;
            } elseif ($sessionAct['vote'] == "2") {
                $classList = 'actionPost';
                $classIcon = 'iconLoved';

                $name = $nameVote;
                $content = 'Showed Love on "'. $contentPost .'"';
                $datetime = $dateVote;
            } elseif ($sessionAct['vote'] == "3") {
                $classList = 'actionPost';
                $classIcon = 'iconIdiot';

                $name = $nameVote;
                $content = 'called '. $namePost .' an Idiot for "'. $contentPost .'"';
                $datetime = $dateVote;
            }
        }
?>
    <li class="row-activity <?php echo $classList; ?>">
        <a href="" class="avatar"><img src="/images/avatars/default_male.jpg" /></a>
        <div class="info">
            <div class="userName">
                <a href=""><?php echo $name; ?></a>
            </div>
            <span class="msgBody"><?php echo $content; ?></span>
            <div class="imgTimeStream">
                <span class="img <?php echo $classIcon; ?>"></span>
                <span class="source"><?php echo $datetime; ?></span>
            </div>
        </div>
    </li>
<?php
    }
} else {
    echo '<p>This user has no recent activity.</p>';
}
?>
</ul>

我绝不是一个专业的程序员…我知道的一切我在过去的一两年里自学了很多,所以我只能想象这真的是多么丑陋大笑.我虽然每天都在学习,但我希望继续:)

此外,在每种类型的活动中,某些内容上的CSS类略有不同.
例：
如果结果行是一个帖子,那么列表项的css类将是“post”
如果结果行是一个投票,那么列表项的css类将是“投票”
等等

任何帮助,将不胜感激！

更新：好多个查询似乎工作正常……没有更多的重复.我甚至只展示过去几个月的活动(现在无论如何).现在我需要弄清楚如何混合结果,所以最新的一个(无论是投票还是发布等)都在顶部.

附：我可能应该提一下,很多数据都会通过这个(想想一个facebook墙上的推文lol)

解决方法:

您应该将查询转换为返回同一列列的3个语句(投票,帖子,注释)的并集.您的查询中发生的事情是多个投票,帖子或评论正在乘以查询生成的行.

另外,如果可能,您应该转换为使用参数化SQL,以消除潜在的SQL注入漏洞.