SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    foreach date loop

    Hi All,

    I was wondering how would I go about setting up a loop of all dates within a range so I could then check if they match my database record.

    I have these variables:
    PHP Code:
    $the_start date('Y-m-d H:i:s'$_REQUEST['start']);
    $the_end date('Y-m-d H:i:s'$_REQUEST['end']); 
    I need to somehow look though every date between these dates and check each one and cross reference each date's date("w") with a field in my database, if it matches then I print a record.

    The idea is I have a month range from the $the_start to $the_end and I am trying to get a print out of say, each thursday. It's for reocurring events in a calendar.

    Is there an easy way to create this loop?

    Thanks

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You don't.

    Code:
    SELECT foo FROM table WHERE date > this AND date < that;
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That doesn't work for what I need. It doesn't show me all the 'thursdays' within that range so I can match them up to the reocurring 'thursday' database record.

    To explain further:

    I have a table that stores the event name and the weekly reocurring day (date("w")) I then need to loop though the date range and each day that matches my reocurring field to then echo out the event name.

    I hope this makes sense.

    Thanks for the reply.

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So you only want records that are on a Thursday? All others you'll, simply not use?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah kind of, it will be for any day of the week but I am using 'thursday' as the sample.

    I only need the dates within the range that have a matching DB record for that reocurring date.

  6. #6
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,023
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    Read up on the MySQL Date functions. This is still quite doable with SQL alone.

  7. #7
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    Read up on the MySQL Date functions. This is still quite doable with SQL alone.
    So your saying with MySQL I can select 1 record from a table, and it will generate 4 records for my script? e.g. I select the 1 record that has a reocurring day of thursday then it generates the 4 thursdays in my date range even though these records don't exist?

    I don't see how?

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    To do to make it more clear to the others what you want to achieve, I guess what you need to do is give some example data from your database, and how you want that data to display.

    Like:

    table data:
    Thursday Soccer game

    Date range: 14 december 2010 - 24 december 2010

    Result:

    14
    15
    16 Soccer game
    17
    18
    19
    20
    21
    22
    23 Soccer game
    24

  9. #9
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    To do to make it more clear to the others what you want to achieve, I guess what you need to do is give some example data from your database, and how you want that data to display.

    Like:

    table data:
    Thursday Soccer game

    Date range: 14 december 2010 - 24 december 2010

    Result:

    14
    15
    16 Soccer game
    17
    18
    19
    20
    21
    22
    23 Soccer game
    24
    Yes thank you that is basically what I am trying to say. It would work as follows:


    table data:
    Thursday Soccer game

    Date range: 14 december 2010 - 24 december 2010

    Result:

    16 Soccer game
    23 Soccer game

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by elogicmedia View Post
    table data:
    Thursday Soccer game
    this is barely enough to go on

    would you kindly do a SHOW CREATE TABLE for this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So, ideally you want you result set from the database that looks like:-
    Code:
    football | home | 2010-12-24 00:00:00
    football | home | 2010-12-25 00:00:00
    football | home | 2010-12-26 00:00:00
    From a record that looks like this:-
    Code:
    football | home | 2010-12-24 00:00:00 | 2010-12-26 00:00:00 | 1
    ?

    If so, you need to figure out how to get the desired result set I'd say.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  12. #12
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $day_of_week 4//0 = sun, 6 = sat
    $start '2010-12-01';
    $end '2010-12-19'
    Code SQL:
    SELECT `fields` FROM `table`
    WHERE DATE BETWEEN $start AND $end AND DATE_FORMAT(DATE, '%w') = $day_of_week

    This assumes your events are only date (not datetime) resolution. You may need to adjust if the time of day matters.
    Instead of BETWEEN you'd probably need to compare < the start of the next day, to ensure events up to midnight are included.

    Change $day_of_week to your desired value.


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
  •