php - 用于简单数字贴纸相册的 mysql 查询和 php 脚本

Question

我正在尝试制作一个功能有点像数字贴纸相册的页面。我的 SQL 知识不是很好，所以我需要一些帮助来完成我目前所掌握的知识。

我想显示所有可用贴纸的列表，但也显示用户是否有贴纸。因此，我们的想法是显示一个空框(项目)列表，然后如果用户拥有该贴纸，则在框内显示图像。

我有两个相关的表称为“items”和“inventory”。项目包含所有可用的贴纸，库存包含用户拥有的贴纸。

以下是可用的列:

CREATE TABLE items (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
) Engine=InnoDB;

CREATE TABLE inventory (
    userid INT UNSIGNED,
    itemid INT UNSIGNED,
    -- FOREIGN KEY (userid) REFERENCES users (id),
    FOREIGN KEY (itemid) REFERENCES items (id),
    UNIQUE (itemid, userid)
) Engine=InnoDB;

我愿意接受有关使用 PHP 和 MySQL 执行此操作的最佳方法的建议，但我认为我需要的是一个查询来返回所有项目名称的列表，然后另一列来标记用户是否有该项目。然后我可以循环遍历 php 中的项目，然后使用基于第二列的条件来显示贴纸是否存在。

到目前为止，我已经得到了以下查询，但它只需要显示当前用户的项目。坚持“where”子句也不起作用，因为它只显示库存项目而不是 NULL 项目(即，它不包括所有项目)。

SELECT items.name, inventory.userid
  FROM items
    LEFT JOIN inventory ON items.id = inventory.itemid

SELECT items.name, inventory.userid
  FROM items
    LEFT JOIN inventory ON items.id = inventory.itemid
  WHERE inventory.userid = '$userid'

Answer 1

尝试将 userid 测试移至 JOIN 条件:

SELECT items.name, inventory.userid
  FROM items
    LEFT JOIN inventory 
      ON items.id = inventory.itemid AND inventory.userid=?

这样，当连接条件失败时(例如当该项目位于另一个用户的库存中时)，该项目本身仍包含在结果中，但用户 ID 为空。它还可以利用表库存上适当定义的索引。