CODEWITHSUNDEEP
mysqldateloops
Erwane
Erwane

Reputation: 31

Mysql : get previous date and breaking loop

i'm trying to get the next and previous row from a mysql table. The problem is not in acquiring previous BUT to break a loop in the prev query (or next, it's the same).

Here the table : 

CREATE TABLE IF NOT EXISTS `my_events` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) DEFAULT NULL,
  `date_start` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

and datas :

INSERT INTO `my_events` (`id`, `title`, `date_start`) VALUES
    (1, 'event prev 1', '2014-04-06 10:00:00'),
    (2, 'current event', '2014-04-08 11:00:00'),
    (3, 'event prev 2', '2014-04-06 10:00:00'),
    (4, 'event prev far', '2014-04-04 08:00:00');

where you are on 'current event' page, this is the request to get the closest previous event

SELECT * FROM my_events
WHERE id != 2 AND date_start <= '2014-04-08 11:00:00'
ORDER BY date_start DESC, id DESC
LIMIT 1

it return the event 'event prev 2'. Fine.

Now, the bug :

on 'event prev 2' :

SELECT * FROM my_events
WHERE id != 3 AND date_start <= '2014-04-06 10:00:00'
ORDER BY date_start DESC, id DESC
LIMIT 1

return 'event prev 1' : OK

on 'event prev 1' :

SELECT * FROM my_events
WHERE id != 1 AND date_start <= '2014-04-06 10:00:00'
ORDER BY date_start DESC, id DESC
LIMIT 1

return 'event prev 2' : LOOP

the event 4 (event prev far) never appear

Have any ideas on how to resolv this ?

Thanks

Upvotes: 0

Views: 50

Answers (1)

Racil Hilan
Racil Hilan

Reputation: 25351

I'm not sure what you're trying to achieve, but your event prev 1 and event prev 2 have the exact same date & time and your using <= when comparing the dates, so when you are on any of them, you will always get the other one. If you change the <= to <, you will get event prev far.

Upvotes: 1

Related Questions