php - 查询只显示一个结果

Question

我有以下查询，我一直认为我的 while 循环不起作用，但事实证明我的查询只输出一条记录。

SELECT up.ordering, u.id, u.username, up.playername 
FROM users AS u 
INNER JOIN playersByUser AS up ON u.id = up.userid 
WHERE u.group IN (3,4,5)
ORDER BY up.ordering

它应该输出几个，但我不确定为什么它只输出一个。我试图从 users 和playersByUser 表中选择id，其中users 表中的组为5。然后输出用户名。

玩家按用户

CREATE TABLE `playersByUser` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `userid` int(11) DEFAULT NULL,
 `playername` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `ordering` tinyint(4) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `userid` (`userid`),
 CONSTRAINT `playersByUser_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

用户

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `firstname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `lastname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `phone_number` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `password` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `salt` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `joined` datetime NOT NULL,
 `group` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_c

可以使用的数据是这样的:

用户

id  firstname lastname   email            phone_number username  password salt   joined    group
1   name1     lastname1  email@email.com  555-555-5555 testSQL   fdfdf     acc     9-3-15   5
2   name2     lastname2  email2@email.com 555-555-5556 testSQL2  fdfdf     dcc     9-3-15   5

玩家按用户

id userid playername ordering
1    1        Bob       1
2    2        Dave      1

Answer 1

您是否尝试过重命名您的 users.group 字段？您是否还尝试过左连接以防连接表中不存在数据？

SELECT playersByUser.ordering, users.id, users.username, playersByUser.playername 
   FROM users, playersByUser where users.id = playersByUser.userid 
   AND `users.group` IN (3,4,5)
   ORDER BY playersByUser.ordering

在 phpMyAdmin 中对我来说工作正常