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

发布于 2024-11-19 17:44:15 字数 1069 浏览 0 评论 0原文

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

我想显示所有可用贴纸的列表，但也显示用户是否有贴纸。因此，我们的想法是显示空盒子（项目）的列表，然后如果用户拥有贴纸，则在盒子内显示图像。

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

以下是可用的列：

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'

原文

I'm trying to produce a page that would function a bit like a digital sticker album. My SQL knowledge isn't very good so I need a bit of help finishing off what I have so far.

I want to display a list of all available stickers but then also show whether or not a user has a sticker. So the idea is to display a list of empty boxes(items) and then display an image inside the box if the user owns the sticker.

The two relevant tables I have are called "items" and "inventory". Items contains all the available stickers and inventory contains the stickers owned by the users.

Here are the available columns:

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;

I'm open to suggestions on the best way to go about doing this using PHP and MySQL, but I think what I need is a query to return a list of all the item names and then another column to flag whether the user has the item. I can then loop through the items in php and then use a conditional based on the second column to show if the sticker is there or not.

So far i've got as far as the below query but it needs to only show items for the current user. Sticking in a 'where' clause doesn't work either as it then only shows the inventory items and not the NULL items (that is, it doesn't include all items).

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'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

愚人国度 2024-11-26 17:44:15

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

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

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

Try moving the userid test to the JOIN condition:

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

That way, when the join condition fails (such as when the item is in another user's inventory), the item itself is still included in the result, but with a null userid. It can also make use of an appropriately defined index on table inventory.

回复收藏 0 原文

三人与歌 2024-11-26 17:44:15

如果我理解有误，请纠正我，我会相应更新。

您正在输出所有项目的表格。
列出的每一项
将有一个空的“框”，如果当前用户有此框，则该框将被填充
物品。

为此，您应该只需要 2 个查询——一个用于获取所有商品，另一个用于获取当前用户库存的商品 ID。

// Get all current user's items
$qUsersItems = "SELECT items.id AS itemID FROM items, inventory WHERE items.id = inventory.itemid AND inventory.userid = " . $givenUserID .
$rUsersItems = mysql_query($qUserItems);

// Store the user's items' IDs into an array
while (list($itemID) = mysql_fetch_assoc($rUsersItems))
    $usersItems[] = $itemID;


// Get all items
$qAllItems = "SELECT id, name FROM items";
$rAllItems = mysql_query($qAllitems);

// ... (To wherever you are outputting your items list)

while ($item = mysql_fetch_assoc($rAllItems)) {

    if (in_array($item['id'], $usersItems)) // Since this ID is in the users list of items (ids)
        $fillBox = "HTML to fill box";
    else
        $fillBox = "";

    /* Your "per item" HTML here, for example (assuming you have outputted table tags before and after the while loop */        
    echo<<<HTML

<tr>
    <td>{$item['name']</td>
    <td>{$fillBox}</td> <!-- This would be your empty box /-->
</tr>

HTML;

注意：此代码未经测试，并且由于显而易见的原因未完成。写完这篇文章后，我意识到为什么人们要求先展示你所做的事情。

Correct me if I've misunderstood, and I will update accordingly.

You are outputting a table of all your items.
Each item listed
will have an empty "box", that is filled if the current user has this
item.

To do this, you should only need 2 queries-- one to fetch all your items, another to fetch the item IDs of the current user's inventory.

// Get all current user's items
$qUsersItems = "SELECT items.id AS itemID FROM items, inventory WHERE items.id = inventory.itemid AND inventory.userid = " . $givenUserID .
$rUsersItems = mysql_query($qUserItems);

// Store the user's items' IDs into an array
while (list($itemID) = mysql_fetch_assoc($rUsersItems))
    $usersItems[] = $itemID;


// Get all items
$qAllItems = "SELECT id, name FROM items";
$rAllItems = mysql_query($qAllitems);

// ... (To wherever you are outputting your items list)

while ($item = mysql_fetch_assoc($rAllItems)) {

    if (in_array($item['id'], $usersItems)) // Since this ID is in the users list of items (ids)
        $fillBox = "HTML to fill box";
    else
        $fillBox = "";

    /* Your "per item" HTML here, for example (assuming you have outputted table tags before and after the while loop */        
    echo<<<HTML

<tr>
    <td>{$item['name']</td>
    <td>{$fillBox}</td> <!-- This would be your empty box /-->
</tr>

HTML;

Note: This code is untested, and unfinished for obvious reasons. After I wrote this, I realize why people ask to show what you have done first.

回复收藏 0 原文

~没有更多了~