I have 4 tables. 3 of which keep users and cards data in, the fourth keeps trade data in
cards - data on the cards
CREATE TABLE `cards` (
`ID` int(255) NOT NULL auto_increment,
`deckid` int(11) NOT NULL,
`url` varchar(255) NOT NULL,
`alttext` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
KEY `deckid` (`deckid`),
CONSTRAINT `cards_ibfk_1` FOREIGN KEY (`deckid`) REFERENCES `cards_deck` (`deckid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10999 DEFAULT CHARSET=latin1
memberlist - data on the members
CREATE TABLE `memberlist` (
`ID` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`level` varchar(12) NOT NULL,
`collecting` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`birthday` varchar(255) NOT NULL,
`date_joined` date NOT NULL,
`prejoiner` tinyint(1) NOT NULL,
`pending` tinyint(1) NOT NULL default '1',
`hiatus` tinyint(1) NOT NULL default '0',
`credits` int(11) NOT NULL default '50',
`lastlogin` date NOT NULL,
`session` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=latin1
user_inventory - list of cards a member has
CREATE TABLE `user_inventory` (
`ID` int(11) NOT NULL auto_increment,
`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 `card_id` (`card_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_inventory_ibfk_1` FOREIGN KEY (`card_id`) REFERENCES `cards` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `user_inventory_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `memberlist` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=85460 DEFAULT CHARSET=latin1
trades_requests - list of trades (sender/reciever and complete will be removed when this is working, they are left over and aren't used any more)
CREATE TABLE `trade_requests` (
`ID` int(11) NOT NULL auto_increment,
`sender` text NOT NULL,
`reciever` text NOT NULL,
`senderid` int(11) NOT NULL COMMENT 'id of the sender',
`recieverid` int(11) NOT NULL COMMENT 'id of the receiver',
`sid1` int(11) NOT NULL,
`sid2` int(11) NOT NULL,
`sid3` int(11) NOT NULL,
`sid4` int(11) NOT NULL,
`sid5` int(11) NOT NULL,
`rid1` int(11) NOT NULL,
`rid2` int(11) NOT NULL,
`rid3` int(11) NOT NULL,
`rid4` int(11) NOT NULL,
`rid5` int(11) NOT NULL,
`complete` tinyint(1) NOT NULL default '2',
PRIMARY KEY (`ID`),
KEY `senderid` (`senderid`),
KEY `recieverid` (`recieverid`),
CONSTRAINT `trade_requests_ibfk_1` FOREIGN KEY (`senderid`) REFERENCES `memberlist` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `trade_requests_ibfk_2` FOREIGN KEY (`recieverid`) REFERENCES `memberlist` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2687 DEFAULT CHARSET=latin1
What i am trying to do, is for every trade the user has sent get the name of the person they are trading with, and the name of the cards they are wishing to trade
So a row that looks like
will come out like
I've almost got it, but it brings up 4 rows
[SELECT tr.ID AS Trade, m.ID AS member, ui.ID AS inventory, m.name, c.alttext
FROM user_inventory ui, memberlist m, trade_requests tr, cards c
WHERE ui.ID
IN (
tr.sid1, tr.sid2, tr.sid3, tr.sid4, tr.sid5, tr.rid1, tr.rid2, tr.rid3, tr.rid4, tr.rid5
)
AND m.ID = ui.user_id
AND c.ID = ui.card_id
AND tr.senderid =1
I'm now stuck as to where to go from here. I can provide other info if needed
