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'
JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)
