gpt4 book ai didi

php - 如何链接并显示来自两个不同表的MySQL数据?

转载 作者:行者123 更新时间:2023-11-29 10:03:41 24 4
gpt4 key购买 nike

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

users 表包含所有典型的用户数据,如 ID、用户名、密码、电子邮件(列)
统计表有 id、攻击、防御、ostats、金币、食物(列)

我想并排显示这两个表中的数据,并通过它们的 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)

然后我希望它们按“ostats”排序(我在任何表中都没有名为“rank”的列,只是不知道如何使用总体统计数据创建排名)

最佳答案

你可以做类似的事情(未经测试)

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/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com