#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    35
    Rep Power
    5

    searchable tour dates on weekday in time interval


    I need to store travel tour info in a table, tours are available in a data range and some of them all year but only on some days like only on Monday and Friday

    User need to search for a tour selecting a time interval, between his vacation start and end date

    I'm not sure what is the best way to do it

    I was thinking to store weekdays in MySQL WEEKDAYS format 1 = Sunday, 2 = Monday, etc in a field divided by | character but i'm not sure i can i query the table that could be something like

    tours id,datefrom,dateto,daysoftheweek

    1,2017-02-23,2017-04-15,|2|6|;
    2,0000-00-00,0000-00-00,|4|;
    3,0000-00-00,0000-00-00,|2|;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    47
    Rep Power
    26
    Don't store data in delimited fields, use a separate table with a row for each weekday
    Code:
    tour
    +----------+-------------+------------+
    | tour id  | datefrom    | dateto     | 
    +----------+-------------+------------+
    |    1     | 2017-02-23  | 2017-04-15 |
    |    2     | 2017-01-01  | 2017-12-31 |
    |    3     | 2017-01-01  | 2017-12-31 |
    +----------+-------------+------------+
         |
         +------------------------------------------+   
                                                    |   tourdays
                                               +---------+------+
                                               | tour_id | wday |
                                               +---------+------+
                                               |    1    |  2   |
                                               |    1    |  6   |
                                               |    2    |  4   |
                                               |    3    |  2   |
                                               +---------+------+
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    35
    Rep Power
    5
    Ok, thanks

    How can i make a query from a form with dates interval between 2 dates
    for example from 2017-02-23 to 2017-03-15

    because some tour are based on date interval but some are always available only on some days
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,281
    Rep Power
    629
    Sounds like you need to have this thread moved to the MySQL forum.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    35
    Rep Power
    5
    I guess sorry, could you please move it for me?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,281
    Rep Power
    629
    I can't but a moderator can. Click the triangle at the bottom of your post to request it. In the meantime there are a lot of MySQL experts on this forum that may jump in before it gets moved.

    Comments on this post

    • al404 agrees
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    47
    Rep Power
    26
    Originally Posted by al404
    Ok, thanks

    How can i make a query from a form with dates interval between 2 dates
    for example from 2017-02-23 to 2017-03-15

    because some tour are based on date interval but some are always available only on some days
    Code:
    SELECT tour_id
      , datefrom
      , dateto
     FROM tour
     WHERE datefrom <= '2017-03-15'
        AND dateto >= '2017-02-23'
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    35
    Rep Power
    5
    @Barand

    your query would work if all the tours have a starting and ending date as searchable interval but some needs to be without date interval but every few days of the week, for example on every Monday and Friday of every year

    who makes search just input it's time interval from date X to date X

    @gw1500se the only triangle that i can see is to report other people comments, do you mean that one?
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,281
    Rep Power
    629
    Correct or you can PM a moderator.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  18. #10
  19. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,105
    Rep Power
    9644
    Moved.

    Comments on this post

    • Will-O-The-Wisp agrees : Thanks!
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    47
    Rep Power
    26
    Either you have to explain more clearly what exactly you are wanting to do or I have to leave this to someone will telepathic abilities
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    35
    Rep Power
    5
    Sorry, I try to elaborate

    in the website i have different national tour:

    - Liverpool, Manchester, Preston departure every day only from 2017-02-23 to 2017-04-15
    - Liverpool, Manchester departure every day all year but only on Wednesday and Friday
    - Liverpool, Preston departure every day all year but only on Monday

    I live in italy, let say i want to make a trip over LiverpoolI
    I check on website what tour is available by entering only the date I arrive and the date I leave ( like on hotel booking web sites )

    now let suppose I will be in be in Liverpool from 2017-03-21 ( Tuesday ) and 2017-03-23 ( Thursday )

    the page should return me as results

    - Liverpool, Manchester, Preston - because is in the correct date interval
    - Liverpool, Manchester - because starts on Wednesday
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    47
    Rep Power
    26
    Given the data just posted
    Code:
    mysql> select * from tour;
    +---------+--------------------------------+------------+------------+
    | tour_id | description                    | datefrom   | dateto     |
    +---------+--------------------------------+------------+------------+
    |       1 | Liverpool, Manchester, Preston | 2017-02-23 | 2017-04-15 |
    |       2 | Liverpool, Manchester          | 2017-01-01 | 2017-12-31 |
    |       3 | Liverpool, Preston             | 2017-01-01 | 2017-12-31 |
    +---------+--------------------------------+------------+------------+
    
    mysql> select * from tourday;
    +---------+------+
    | tour_id | wday |
    +---------+------+
    |       1 |    1 |
    |       1 |    2 |
    |       1 |    3 |
    |       1 |    4 |
    |       1 |    5 |
    |       1 |    6 |
    |       1 |    7 |
    |       2 |    4 |
    |       2 |    6 |
    |       3 |    2 |
    +---------+------+
    Create a temporary table containing the dates of their visit
    Code:
    create temporary table dates (visitdate date not null primary key);
    insert into dates values ('2017-03-21'),('2017-03-22'),('2017-03-23');
    Then
    Code:
    SELECT visitdate
        , dayname(visitdate)
        , t.tour_id
        , description
    FROM dates d 
        INNER JOIN tour t 
            ON visitdate BETWEEN datefrom AND dateto
        INNER JOIN tourday td 
            ON t.tour_id = td.tour_id 
            AND dayofweek(visitdate) = wday
    ORDER BY visitdate;
    
    +------------+--------------------+---------+--------------------------------+
    | visitdate  | dayname(visitdate) | tour_id | description                    |
    +------------+--------------------+---------+--------------------------------+
    | 2017-03-21 | Tuesday            |       1 | Liverpool, Manchester, Preston |
    | 2017-03-22 | Wednesday          |       1 | Liverpool, Manchester, Preston |
    | 2017-03-22 | Wednesday          |       2 | Liverpool, Manchester          |
    | 2017-03-23 | Thursday           |       1 | Liverpool, Manchester, Preston |
    +------------+--------------------+---------+--------------------------------+

IMN logo majestic logo threadwatch logo seochat tools logo