SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    Sweden
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL query problem, check if date is within saved period

    I'm trying to build a calendar of reservations and are now stuck in an equation I do not get together.

    The person who administers the calendar can create their own periods of the year when visitors have the opportunity to book. And these periods should now be synchronized with the calendar.

    The periods are stored in the following columns in the database:

    [from_month] - [from_day] - [to_month] - [to_day]

    The calendar is built in PHP. And every day should be checked against the database if it is a valid day to book, according to the periods that are saved. So that date is within one period.

    That's where I got stuck now. I thought it would just be to write:
    Code:
    WHERE from_month <= '".$this_monthnr."'
    AND from_day <= '".$this_daynr."'
    AND to_month >= '".$this_monthnr."'
    AND to_day >= '".$this_daynr."'
    But it does not work if, for example, the "from date" is 08-01 and the "end date" is 02-01. Then it becomes:
    Code:
    WHERE 08 <= '".$this_monthnr."'
    AND 01 <= '".$this_daynr."'
    AND 02 >= '".$this_monthnr."'
    AND 01 >= '".$this_daynr."'
    It becomes crazy as it's a year-end there between this dates, which makes the ending date value becomes lower than the start date. And the parameter [year] should not be included since these periods should be the same year after year, so it is not necessary.

    Would be grateful if some genius wanted to help me. And if there is anything you have questions or do not understand what I mean, just ask.

    The goal is that the SQL query should return the row represent the period in which the selected calendar day is within, if it exists. Otherwise, it should not return anything at all.

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Why not save them as full DATE types, and then do

    WHERE $thisdate BETWEEN from_date AND to_date;

    PS: Flagging this for moving to the mySQL forum, so i can stand in the fire of Rudy's steely gaze once again

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    Sweden
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But I can't save the years values in the database, since the period is the same year after year. Calendar administrator should not need to add new periods each year.

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Just because the value is there, doesnt mean you have to use it.

    "WHERE '2011-".$thismonth."-".$thisday."' BETWEEN from_date AND to_date";

    would work: since you dont care about year, defining all dates to be in year 2011 works.

    That said, i'm fairly sure you could store them as integers (MMDD), and the between would still work.

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    Sweden
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No it doesn't. If from_date is 08-01, and I save it like 2011-08-01. And to_date is 02-01, and I save it like 2011-02-01. To_date is still lower than from_date.

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    so instead save it as 2011-08-01 to 2011-12-31 (801 and 1231) and 2011-01-01 (101) to 2011-02-01 (201). Same effect.

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    Sweden
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You mean I have to set a rule that the period dates has to be within the same year. Not nice solution.

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    No, Just that your program needs to be able to interpret a request for a period overlaping the end of the year and split the time into two entries. A decent solution.

  9. #9
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    Sweden
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, you propose two new columns in periods table. How should the SQL query look like then?

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    I propose two columns period. But even your old query would work if you split the entries.

  11. #11
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    Sweden
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, I don't want one period (in users eye) to be split up to two periods after saving. It have to still look like the period that was set by the admin.

  12. #12
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Right. At this point then I turn you over to the MySQL gods, because obviously my logic doesnt work for you.


Tags for this Thread

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
  •