whisher — 2012-05-30T13:40:34-04:00 — #1
CREATE TABLE IF NOT EXISTS contest (
id int(10) unsigned NOT NULL auto_increment,
title varchar(255) NOT NULL,
slug varchar(255) NOT NULL,
valid_from date NOT NULL,
valid_to date NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique_contest_title (title),
UNIQUE KEY unique_contest_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO contest (id, title, slug, description, valid_from, valid_to) VALUES
(1, 'my first cool contest', 'my-first-cool-contest', 'my desc', '2012-05-01', '2012-05-31');
What's the query to know if a new contest overlap an old contest ?
I don't want two or more contest in the same time.
By now I ended up with this simple query
SELECT id FROM pc_contest WHERE valid_from = '2012-05-10' OR valid_to = '2012-05-31'
and it seems to work but I'd like to know your opinion
r937 — 2012-05-30T13:51:05-04:00 — #2
whisher — 2012-05-30T14:56:54-04:00 — #3
SELECT COUNT( id ) AS num
valid_to >= '2012-05-10'
valid_from <= '2012-05-28'
now I just realized what a silly query I did
whisher — 2012-05-30T15:11:43-04:00 — #4
Just the last thing I take advance one post two queries
How about this query to get the current contest
SELECT * FROM
contest WHERE valid_from <= NOW AND valid_to >= NOW
r937 — 2012-05-30T16:10:22-04:00 — #5
what happened when you tested it?
whisher — 2012-05-30T17:03:21-04:00 — #6
it works fine but also the silly query worked well (at least at first sight)
mittineague — 2014-09-23T03:25:14-04:00 — #7
This topic is now archived. It is frozen and cannot be changed in any way.