我有两个名为“ 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/