SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Don't allow overlap date for a contest.

    Hi,
    My table
    PHP Code:
    CREATE TABLE IF NOT EXISTS contest (
      
    id int(10unsigned NOT NULL auto_increment,
      
    title varchar(255NOT NULL,
      
    slug varchar(255NOT NULL,
      
    description text,
      
    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 (idtitleslugdescriptionvalid_fromvalid_toVALUES
    (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
    PHP Code:
    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

    Bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    SELECT COUNTid ) AS num
    FROM  contest
    WHERE 
    (
    valid_to >=  '2012-05-10'
    )
    AND (
    valid_from <=  '2012-05-28'



    Ps
    now I just realized what a silly query I did :O

  4. #4
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by whisher View Post
    How about this query to get the current contest
    SELECT * FROM `contest` WHERE valid_from <= NOW AND valid_to >= NOW
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it works fine but also the silly query worked well (at least at first sight) :P


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •