I have a select box in a form on my page, and i want to fill it with information from the database.
I have 2 tables in the database
CREATE TABLE IF NOT EXISTS `cards` (
`ID` int(255) NOT NULL auto_increment,
`amount` int(4) NOT NULL,
`type` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`deck` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`category` varchar(255) NOT NULL,
`alttext` varchar(255) NOT NULL,
`features` varchar(255) NOT NULL,
`worth` int(1) NOT NULL,
`deny` int(3) NOT NULL,
`donated` varchar(255) NOT NULL,
`created` tinyint(1) NOT NULL,
PRIMARY KEY (`ID`),
KEY `worth` (`worth`),
KEY `deny` (`deny`),
KEY `cards_idx_worth_deny` (`worth`,`deny`),
KEY `alttext` (`alttext`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9903 ;
CREATE TABLE IF NOT EXISTS `user_inventory` (
`ID` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL COMMENT 'Name of the user',
`user_id` int(11) NOT NULL COMMENT 'ID of the user from the memberlist table',
`card_id` int(11) NOT NULL COMMENT 'ID of the card from the cards table',
`status` varchar(255) NOT NULL,
`from` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
KEY `alttext` (`alttext`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22281 ;
I want to see what decks in the cards table a user has all the cards for. So for each distinct deck in the cards table, count the number of rows to it, then compare it to the number of rows in the user_inventory table.
This is the sql code i was using before i changed the structure of the user_inventory table, getting rid of the columns, url,alttext,deck,worth,amount
SELECT u.user_id, u.deck, u.name, u.status, u.url, u.ID
FROM (
SELECT ID, user_id, deck, name,
status , url, count( DISTINCT alttext ) AS user_count
FROM user_inventory
WHERE url NOT LIKE '%00.png'
AND name = '".mysql_real_escape_string($_COOKIE['mttcg']['u'])."'
AND status NOT IN ('mastered', 'double')
GROUP BY user_id, deck
) AS u
INNER JOIN (
SELECT deck, count( * ) AS card_count
FROM cards
GROUP BY deck
) AS c ON u.deck = c.deck
WHERE u.user_count = c.card_count
