html - php从mysql中获取多行

Question

我想成为直播前 6 位用户

我想从 mysql 中提取每行的最高值及其用户名。

<?php  
    include_once 'config.php';  

    $query = $config -> prepare("
SELECT `UserID`
    , `Humanity`
    , `Headshots`
    , `Murders`
    , `BanditsKilled`
    , `ZombiesKilled`
    , `pAliveTime` 
 FROM `users` 
 ORDER 
      BY `UserID` ASC 
   LIMIT 10
 ");  
    if($query -> execute())  
    {  
        $query_results = $query->fetchAll();

    }  
?>

然后在html中

<div class="colw_3 spec-l border-right">
          <p></p><p><strong><em><font color="white">Humanity</font></em></strong><br>
          </p>
          <p><strong><em><font color="white">Headshots:</font></em></strong><br>
          </p>
          <p><strong><em><font color="white">Bandits Killed:</font></em></strong><br>
          </p>
          <p><strong><em><font color="white">Murders</font></em></strong><br>
          </p>
          <p><strong><em><font color="white">Zombies Killed</font></em></strong><br>
          </p>
          <p><em><strong><font color="white">Alive Time:</font></strong></em><br>
          </p>
        </div>
        <!-- END col_6 -->

        <div class="colw_3 paddbott100 spec-r">
          <p></p><p><strong><font color="white"> <?php echo $query_result["Humanity"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["Headshots"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["BanditsKilled"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["Murders"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["ZombiesKilled"]; ?></font></strong><br>
          </p>
          <p><strong><font color="white"> <?php echo $query_result["pAliveTime"]; ?></font></strong><br>
          </p>
        </div>

但我不知道应该如何编写查询，以便获取值和这些值的用户名

它应该看起来像这样

人类:5000 - 用户名的值。 (没有 的值)Bandits Killed: 4 - 用户名的值(另一个用户名或相同的用户名，取决于谁在 BanditsKilled 列中具有最高值)

目前我的代码按用户 ID 升序排列..

Answer 1

您可以对列进行循环并执行按列排序的查询，仅限于一行:

$top_users = [];
$columns = ['Humanity', 'Headshots', 'Murders', 'BanditsKilled', 'ZombiesKilled', 'pAliveTime'];
foreach ($columns as $column) {
    $query = $config->prepare("SELECT UserID, UserName, $column as num FROM users ORDER BY $column DESC LIMIT 1");
    if($query->execute()) {
        $top_users[$column] = $query->fetch();
    }
}

print_r($top_users);

可能的(理论)输出:

Array(
    [Humanity]      => Array([UserId] => 21, [UserName] => foo1, [num] => 5)
    [Headshots]     => Array([UserId] => 22, [UserName] => foo2, [num] => 4)
    [Murders]       => Array([UserId] => 23, [UserName] => foo3, [num] => 8)
    [BanditsKilled] => Array([UserId] => 24, [UserName] => foo4, [num] => 7)
    [ZombiesKilled] => Array([UserId] => 25, [UserName] => foo5, [num] => 5)
    [pAliveTime]    => Array([UserId] => 26, [UserName] => foo6, [num] => 2)
)

这样你就可以获得值:

foreach ($top_users as $column => $data) {
    echo $data['UserName'] . ' : ' . $data['num'] ;
}