仅显示表格中的5个

Question

我正在尝试让你可以看到你的朋友的最新活动，但一次只能显示5个。在这5个朋友中，如果你拥有的时间比其他任何朋友都多，你可以不止一次拥有一个人。就像它会说"friend Like skillName date“，然后显示最近的五个日期。我的代码是这样的：

function recentActivityF($user){
include "mysql.ws";
$query = mysql_query("SELECT u.* FROM friends uf inner join users u on uf.friend = u.username WHERE uf.user = '$user' ORDER BY u.username") or die(mysql_error());
while($row = mysql_fetch_array($query)){
    $friends = $row['username'];    
            //$query2 = mysql_query("SELECT * FROM recentactivity WHERE user LIKE '".$friends."' ORDER BY time DESC LIMIT 1") or die(mysql_error());
        $query2 = mysql_query("SELECT u.* FROM recentactivity uf inner join friends u on uf.user = u.friend WHERE uf.user LIKE '".$friends."' ORDER BY uf.time DESC LIMIT 1") or die(mysql_error());
            $query3 = mysql_query("SELECT * FROM skills WHERE playerName LIKE '".$friends."' LIMIT 1") or die(mysql_error());
        while($row3 = mysql_fetch_array($query3)){
         while($row2 = mysql_fetch_array($query2)){
        $date = $row2['time'];
        $skillRow = $row2['skill']+1;
        $skill = findType($skillRow)."xp";
        $skillName = findType($skillRow);
                echo'Friend: '.$friends.' Levelled Up '.$skillName.' Date: '.$date.'';
              }
            }
         }
       }

结果应该是：

1.)Friend: mark Levelled Up Attack date: 28-Aug-2012 09:48
2.)Friend: matt Levelled Up Strength date: 28-Aug-2012 09:47
3.)Friend: kevin Levelled Up Attack date: 28-Aug-2012 09:46
4.)Friend: mark Levelled Up Strength date: 28-Aug-2012 09:45
5.)Friend: april Levelled Up Magic date: 28-Aug-2012 09:44

我的结果是：

1.)Friend: mark Levelled Up Attack date: 28-Aug-2012 09:48
2.)Friend: mark Levelled Up Strength date: 28-Aug-2012 09:45
3.)Friend: matt Levelled Up Strength date: 28-Aug-2012 09:47
4.)Friend: kevin Levelled Up Attack date: 28-Aug-2012 09:46
5.)Friend: april Levelled Up Magic date: 28-Aug-2012 09:44
6.)Friend: april Levelled Up Strength date: 28-Aug-2012 09:45

SQL：

    CREATE TABLE `friends` (
  `user` varchar(32) NOT NULL,
  `friend` varchar(32) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `friends` (`user`, `friend`) VALUES
('Cls Prod', 'pro skiller'),
('Cls Prod', 'polo303'),
('Cls Prod', 'pjtips123'),
('Cls Prod', 'tommy0581'),
('Cls Prod', 'sageofmali');

CREATE TABLE `users` (
  `username` varchar(255) NOT NULL DEFAULT '',
  `password` varchar(255) DEFAULT NULL,
  `rights` varchar(255) DEFAULT NULL,
  `online` tinyint(1) NOT NULL,
  `friends` text,
  `donator` double DEFAULT NULL,
  `time` bigint(20) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `users` (`username`, `password`, `rights`, `online`, `friends`, `donator`, `time`) VALUES
('Cls Prod', 'password', '0', 0, NULL, NULL, 0);

CREATE TABLE `recentactivity` (
  `user` varchar(255) NOT NULL,
  `skill` int(11) NOT NULL,
  `time` varchar(255) NOT NULL,
  `killed` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `recentactivity` (`user`, `skill`, `time`, `killed`) VALUES
('Cls Prod', 2, '27-Aug-2012 16:06:40', '');

所以评论的$query2显示来自每个朋友的1个，但将显示超过5个，我想限制它只显示最近的5个朋友的活动。有什么帮助吗？

Answer 1

我注意到您自己正在进行技能到技能名称的转换，所以这应该足够了：

SELECT f.user AS username, a.time, a.skill
FROM
  friends f
  JOIN recentactivity a ON(a.user = f.user)
WHERE
 f.friend = $username
GROUP BY a.user -- only one event per friend
ORDER BY a.time DESC
LIMIT 5

请在此处查看演示：http://sqlfiddle.com/#!2/66f53/6

编辑:这就是你所需要做的：

function recentActivityF($user){
  include "mysql.ws";
  $query = <<<EOD
SELECT f.user AS username, a.time, a.skill
FROM
  friends f
  JOIN recentactivity a ON(a.user = f.user)
WHERE
 f.friend = $user
GROUP BY a.user -- only one event per friend
ORDER BY a.time DESC
LIMIT 5
EOD;

  mysql_query($query);
  while($row = mysql_fetch_array($query)) {
    $friend_name = $row['username'];
    $date = $row['time'];
    $skillRow = $row['skill']+1;
    $skillName = findType($skillRow);
    echo'Friend: '.$friend_name.' Levelled Up '.$skillName.' Date: '.$date.'';
  }

附注: Standard Stack-Overflow免责声明: mysql扩展已弃用，请使用PDO或mysqli。

Answer 2

使用sql limit。

基本用法如下：

SELECT * FROM table LIMIT 5

如果您想返回6-10条记录：

SELECT * FROM table LIMIT 5,5

给我一点时间来看一下你的代码，给出一个很好的例子。在你的代码中，你需要使用它的地方并不明显。

看了一下你的代码，你似乎不需要第一个查询。您应该能够执行以下操作：

SELECT u.*
FROM recentactivity uf
INNER JOIN friends u ON uf.user = u.friend
WHERE u.user = $user
ORDER BY uf.time DESC
LIMIT 5

您可能需要选择更多字段才能获得所需的所有信息，但这应该会获得给定用户在recentactivity上的所有记录，按最新排序，并限制在前5位。