PHP / MySQL：从表中选择相同的列但具有不同的主ID

I'm a student in web developing and I've encountered a problem with a MySQL statement. I was asked by a game community to create some sort of little website where they could see which people they banned.

我是网络开发的学生,我遇到了MySQL语句的问题。游戏社区要求我创建某种小网站,以便他们可以看到他们禁止的人。

There are 2 tables which I use, 'accounts' and 'bans'. The layout of accounts is somewhat like this

我使用了2个表,“帐户”和“禁令”。帐户的布局有点像这样

account_id  username  (more columns)
----        ----      ----
1           hey       ...
2           hdf       ...
3           sdf       ...
4           admin     ...

The layout of 'bans' is:

'禁令'的布局是:

ban_id      account_id    bannedby_id    (more columns)
----        ----          ----           ----
1           1             4              ...
2           3             4              ...

In this case an admin with ID 4 has banned the accounts with ID 1 and 3. I want to create a query where I have both the name of the banned person, and the name of the admin who banned him in a single result.

在这种情况下,ID为4的管理员已禁止ID为1和3的帐户。我想创建一个查询,其中包含被禁人员的姓名,以及在单个结果中禁止他的管理员姓名。

I do this now by first doing:

我现在首先这样做:

SELECT bans.*, accounts.username FROM bans, accounts WHERE accounts.account_id = bans.account_id

Then looping over all results and selecting the admin name from the result i got:

然后循环遍历所有结果并从我得到的结果中选择管理员名称:

while($baninfo = mysql_fetch_assoc($sqlBans))
{
    mysql_query("SELECT username FROM accounts WHERE account_id = '" . $baninfo['bannedby_id'] . "'");
}

This worked, but I don't think this is the correct way to do it. I think there must be a MySQL function which could do such a query but I have searched on Google for it and I couldn't find it.

这有效,但我不认为这是正确的方法。我认为必须有一个MySQL功能可以做这样的查询,但我在Google上搜索它,我找不到它。

Just to be clear, here's how I would like to get the result from the query:

为了清楚起见,我希望从查询中获得结果:

ban_id   account_id   account_username   bannedby_id   admin_username   (other columns from Bans table)
----     ----         ----               ----          ----             ----
1        1            hey                4             admin            ...
2        3            sdf                4             admin            ...

I'm sorry if this is a very stupid question but I could solve it. I hope you guys have a solution for me. Im using this website a lot as I encounter small problems and it always have great answers.

如果这是一个非常愚蠢的问题,我很抱歉,但我可以解决它。我希望你们有一个解决方案。我使用这个网站很多,因为我遇到小问题,它总是有很好的答案。

Regards, Mark

4 个解决方案

#1

select b.ban_id,
    b.account_id,
    a.username as account_username,
    b.bannedby_id,
    a2.username as admin_username,
from bans b
inner join accounts a on b.account_id = a.account_id
inner join accounts a2 on b.bannedby_id = a2.account_id

#2

use the accounts table twice - each as an inline view and join seperately.

使用帐户表两次 - 每个作为内联视图并单独加入。

select a.username as banned, b.username as by_who
from accounts a, accounts b, ban
where a.account_id = ban.account_id
and b.account_id = ban.account_id

#3

What you want is a JOIN operation:

你想要的是一个JOIN操作:

SELECT ban_id, banned.account_id, banned.username AS account_username,
    bannedby_id, admin.username AS admin_username, etc....
FROM bans
LEFT JOIN accounts AS banned ON banned.account_id = bans.account_id
LEFT JOIN accounts AS admin ON banned.banned_by = admin.account_id

#4

Try

SELECT b.ban_id, ac.account_id, ac.username AS banned, b.bannedby_id AS mod FROM accounts ac, bans b WHERE ac.account_id = b.account_id ORDER BY b.bannedby_id DESC

#1

select b.ban_id,
    b.account_id,
    a.username as account_username,
    b.bannedby_id,
    a2.username as admin_username,
from bans b
inner join accounts a on b.account_id = a.account_id
inner join accounts a2 on b.bannedby_id = a2.account_id

#2