我有两个名为“ stats”和“ users”的表

users表具有所有典型的用户数据,例如id,username,password,email(columns)
统计表具有ID,攻击,防御,统计,黄金,食品(列)

我想并排显示这两个表中的数据,并通过其IDS链接数据
例如,

 Rank   user_uid   ostats     attack    defense    gold
   1    Test        10          5         5        100
   2    Test2       8           2         6        60
   3    Test3       6           5         1        40


用户名来自表“ users”,其余用户来自表“ stats”
所以首先我想知道如何链接和显示来自同一ID的数据,例如Username(user_id = 1)和ostats,attack,defense,gold,food(id = 1)

然后,我希望它们按其“状态”排序(我在任何表中都没有名为“等级”的列,只是不知道如何使用总体状态来创建等级)

最佳答案

您可以做类似(未经测试)的操作

SELECT u.username, s.overall, s.attack, s.defense, s.gold
FROM stats s JOIN users u on s.user_uid = u.id
ORDER BY s.overall;


排名的可能解决方案:

set @row_number=0;
SELECT (@row_number:=@row_number+1) as rank, u.username, s.overall, s.attack, s.defense, s.gold
FROM stats s JOIN users u on s.user_uid = u.id
ORDER BY s.overall;


另一种恐怖的尝试:

set @row_number = (select count(*) from users) + 1;
select (@row_number:=@row_number-1) as rank,  u.username, s.overall from
stats s join users u on s.user_uid = u.id order by s.overall desc;
set @row_number = 0;


在PHP代码中,您必须将其作为两个查询运行以设置变量,然后运行实际的排名查询。这样,运行时rank变量始终设置为0。请注意,我使用了不同的表名和列名,只是为了简化一点。请记住要适应您的特定需求。

// connect to database
$conn = mysqli_connect("localhost", "user", "password", "database");
// this query will set a variable to 0.
$setSql = "SET @row_number = 0;";
// run the query. This will return a boolean - true or false, depending on whether or not the query ran successfully
$variableSet = mysqli_query($conn, $setSql);
// if the query ran successfully
if($variableSet){
    // setup the actual ranking query
    $statsSql = "select
                     (@row_number:=@row_number+1) as rank,
                     u.id,
                     u.username,
                     s.overall
                 from
                     mstats s
                 join
                     musers u
                 on
                     s.muser = u.id
                 order by
                     s.overall desc;";
    $ranks = mysqli_query($conn, $statsSql);
    if(!$ranks){
        // dump error from rank query
        var_dump($conn->error);
    } else {
        // dump results as associative array
        var_dump($ranks->fetch_all(MYSQLI_ASSOC));
    }
} else {
    // dump errors from setting variable
    var_dump($conn->error);
}


对我来说,结果转储如下所示:

array (size=3)
  0 =>
    array (size=4)
      'rank' => string '1' (length=1)
      'id' => string '2' (length=1)
      'username' => string 'Bar' (length=3)
      'overall' => string '1000' (length=4)
  1 =>
    array (size=4)
      'rank' => string '2' (length=1)
      'id' => string '6' (length=1)
      'username' => string 'Tom' (length=3)
      'overall' => string '7' (length=1)
  2 =>
    array (size=4)
      'rank' => string '3' (length=1)
      'id' => string '1' (length=1)
      'username' => string 'Foo' (length=3)
      'overall' => string '3' (length=1)

关于php - 如何链接和显示来自两个不同表的MySQL数据?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/52406881/

10-15 04:27
查看更多