phpraid_raids CREATE TABLE `phpraid_raids` (
`raid_id` int(10) NOT NULL auto_increment,
`description` text NOT NULL,
`freeze` int(10) NOT NULL default '0',
`invite_time` varchar(255) NOT NULL default '',
`location` varchar(255) NOT NULL default '',
`officer` varchar(255) NOT NULL default '',
`old` tinyint(1) NOT NULL default '0',
`start_time` varchar(255) NOT NULL default '',
`dr_lmt` int(2) NOT NULL default '0',
`hu_lmt` int(2) NOT NULL default '0',
`ma_lmt` int(2) NOT NULL default '0',
`pa_lmt` int(2) NOT NULL default '0',
`pr_lmt` int(2) NOT NULL default '0',
`ro_lmt` int(2) NOT NULL default '0',
`sh_lmt` int(2) NOT NULL default '0',
`wk_lmt` int(2) NOT NULL default '0',
`wa_lmt` int(2) NOT NULL default '0',
`min_lvl` int(2) NOT NULL default '0',
`max_lvl` int(2) NOT NULL default '0',
`max` varchar(255) NOT NULL default '',
PRIMARY KEY (`raid_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
phpraid_signups CREATE TABLE `phpraid_signups` (
`signup_id` int(10) NOT NULL auto_increment,
`char_id` int(10) NOT NULL default '0',
`profile_id` int(10) NOT NULL default '0',
`raid_id` int(10) NOT NULL default '0',
`comments` varchar(255) NOT NULL default '',
`cancel` int(1) NOT NULL default '0',
`queue` int(1) NOT NULL default '0',
`timestamp` varchar(255) NOT NULL default '',
PRIMARY KEY (`signup_id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
Mission:
Fetch the last next 5 raids from and including today, and add a colum to the result that shows if a user is signed up or not.
So far:
SELECT raid_id,location,FROM_UNIXTIME(start_time), signup_id as signed_up as start_time
FROM phpraid_raids
LEFT OUTER JOIN (select * from phpraid_signups rs where rs.user_id = $userid )
ON phpraid_raids.raid_id = rs.raid_id
WHERE DATE_SUB(CURDATE(), INTERVAL 0 DAY) <= FROM_UNIXTIME(invite_time)
ORDER BY raid_id ASC limit 5
This shows a list of the next 5 raids.