Retrieve record, if log entry timestamp is greater than 10 minutes

[PaulRyan]

Hello,

I am creating an online RPG, and I'm stumped at the following:

- Select all NPC's that match the current user co-ordinates and location ID

- With each of those NPC's check the attack log to see if they have been attacked within the last 10 minutes by the logged in user

- Only return the NPC's that have not been attacked within the last 10 minutes

 

Tables are as follows:

World NPC's

CREATE TABLE IF NOT EXISTS `world_npcs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`combat_level` smallint(3) NOT NULL,
`location_id` smallint(3) NOT NULL,
`lr_coords` tinyint(2) NOT NULL,
`ud_coords` tinyint(2) NOT NULL,
`energy` tinyint(2) NOT NULL DEFAULT '20',
`timestamp` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `world_npcs`
--

INSERT INTO `world_npcs` (`id`, `name`, `combat_level`, `location_id`, `lr_coords`, `ud_coords`, `energy`, `timestamp`) VALUES
(1, 'Beginner Battler', 2, 3, 1, 13, 20, '2013-01-15 00:00:00'),
(2, 'Beginner Battler', 2, 3, 1, 13, 20, '2013-01-15 00:00:00');

 

Log PvE Attacks

CREATE TABLE IF NOT EXISTS `log_pve_attacks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`attacker_id` int(11) NOT NULL,
`defender_id` int(11) NOT NULL,
`winner_id` int(11) NOT NULL,
`winner_cash_gain` int(11) NOT NULL,
`winner_exp_gain` smallint(4) NOT NULL,
`timestamp` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `log_pve_attacks`
--

INSERT INTO `log_pve_attacks` (`id`, `attacker_id`, `defender_id`, `winner_id`, `winner_cash_gain`, `winner_exp_gain`, `timestamp`) VALUES
(1, 1, 1, 1, 200, 6, '2013-01-27 21:17:47'),
(2, 1, 1, 1, 200, 6, '2013-01-27 21:45:00');

 

My current SQL to return all NPC's in the same co-ordinates as the user:

SELECT `npc`.`id`, `npc`.`name`, `npc`.`combat_level`
FROM `world_npcs` AS `npc`
WHERE `npc`.`location_id` = 3 AND `npc`.`lr_coords` = 1 AND `npc`.`ud_coords` = 13 AND `npc`.`energy` > 0
ORDER BY `npc`.`id` DESC

 

- I have preset the location ID and user co-ordinates in the above SQL query.

- The user ID is from $_SESSION['logged_id'] (This stores the users account id)

- Attacker id will always be the logged in users ID as NPC's cannot attack the users, only users can attack the NPC's

 

I would appreciate any help with this, I'll keep trying other methods, the ones I have tried so far haven't agreed with me.

If anything else is needed, please let me know.

 

Thanks, PaulRyan.

Edited January 27, 2013 by PaulRyan

[Jessica]

You haven't told us what the problem is...

[PaulRyan]

My bad, the problem is quite obvious, I am unable to retrieve the records from the database I want.

I have stated the criteria at the top of the original post, I have tried a few different query modifications to do what I require but to no avail.

 

I am thinking to join the attack log with the NPC selection query, then checking the time stamp (if any) against the current time stamp.

 

I'll keep trying to see what I can come up with.

 

Thanks, PaulRyan.

[PaulRyan]

Updated SQL:

SELECT `npc`.`id`, `npc`.`name`, `npc`.`combat_level`,
 `pve`.`id` AS `attack_id`, IF(`pve`.`timestamp` != '', `pve`.`timestamp`, '0000-00-00 00:00:00') AS `attack_timestamp`
FROM `world_npcs` AS `npc`
LEFT JOIN (SELECT `id`,`defender_id`,`timestamp` FROM `log_pve_attacks` WHERE `attacker_id` = {$_SESSION['logged_in']} AND `timestamp` > NOW()-INTERVAL 10 MINUTE LIMIT 1) AS `pve` ON `pve`.`defender_id` = `npc`.`id`
WHERE `npc`.`location_id` = 3 AND `npc`.`lr_coords` = 1 AND `npc`.`ud_coords` = 13 AND `npc`.`energy` > 0
ORDER BY `npc`.`id` DESC

Edited January 27, 2013 by PaulRyan

[Barand]

Basically you want a query like

 

SELECT whatever
FROM world_npcs N
LEFT JOIN log_pve_attacks A ON A.id = N.defender_id AND A.timestamp >= NOW() - INTERVAL 10 MINUTE
WHERE N.defender_id IS NULL

[Jessica]

Okay, and the problem IS....?

 

Do you get an error? What is it? Did you try echo'ing your SQL and running it in MySQL or phpMyAdmin? How have you tested the problem is not in PHP? What HAVE you tried?

 

Saying what you want done is nice. But we won't do it for you.

 

I will say you probably don't need to do a subquery to do your left join. I'll look at your tables but answering the first few questions would help.

 

Edit: Barand to the rescue :-P

Edited January 27, 2013 by Jessica

[PaulRyan]

Basically you want a query like

 

SELECT whatever
FROM world_npcs N
LEFT JOIN log_pve_attacks A ON A.id = N.defender_id AND A.timestamp >= NOW() - INTERVAL 10 MINUTE
WHERE N.defender_id IS NULL

 

I'll give that a go, I seriously did not know you could LEFT JOIN on 2 different columns. Learn something new everyday.

 

Thanks, PaulRyan.

[PaulRyan]

Thanks a lot Barand for your query, worked like a charm! Also taught me something new

 

Thanks Jessica for your help too, I apologize that I didn't ask the question correctly. I'll be sure to state my actual problem next time

 

Regards, PaulRyan.