SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Dublin
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question PHP/SQL Date query

    Hi,

    Not too sure if this is a PHP or SQL query..............so sorry if it's in the wrong place.

    Anyway on my content website I want to have an option called 'Stories of the week'

    Is there a way to display weekly content without having to manually put in the a new date every day into my code?

    If I was to do it manully I would do as follows:

    "SELECT * , UNIX_TIMESTAMP(storydate) AS storydate_ts FROM content WHERE storydate > 20070819202854 AND storydate < 20070826202854"
    "

    20070819202854 is a week ago 19th August
    20070826202854 is today 26th August

    Is there a way to do this autmatically?
    Last edited by cosmic_bird; Aug 26, 2007 at 05:38. Reason: Typo

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Location
    edge of nowhere
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there a specific day of the week when your week starts in the query? Or is that calculated as 1 full week from the time the script is ran?

    If it's the second case it's simple:

    Since an Unix timestamp is just seconds since a marker, you get the current timestamp with time() and substract a week's worth of seconds and then form the timestamp in your format with date().
    PHP Code:
    $now time();
    $aWeekAgo $now - (7*24*60*60);
    $yourStampFormat date("YmdHis"$aWeekAgo); 
    If you need the week starting last Wednesday to this Wednesday for example, it's a bit more complicated but still doable.
    You will probably need to get the current day of the week with date("D"); and see which day it is, then substract the proper amount of seconds from the Unix timestamp based on what day it is.

    If you need me to go on about this, post a reply.
    Programming boils down to three things: fast, good and cheap.
    Please pick two.

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Dublin
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sebulbus View Post
    Is there a specific day of the week when your week starts in the query? Or is that calculated as 1 full week from the time the script is ran?

    If it's the second case it's simple:

    Since an Unix timestamp is just seconds since a marker, you get the current timestamp with time() and substract a week's worth of seconds and then form the timestamp in your format with date().
    PHP Code:
    $now time();
    $aWeekAgo $now - (7*24*60*60);
    $yourStampFormat date("YmdHis"$aWeekAgo); 
    If you need the week starting last Wednesday to this Wednesday for example, it's a bit more complicated but still doable.
    You will probably need to get the current day of the week with date("D"); and see which day it is, then substract the proper amount of seconds from the Unix timestamp based on what day it is.

    If you need me to go on about this, post a reply.
    Thanks, I think that should do it!

    Sorry I should have specified. I basically just want to show content from the last 7 days, so today would include content from last monday onwards, tomorrow will include content from last tuesday onwards etc.

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Location
    edge of nowhere
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then the code above should work, except that you need only the last 6 days, not 7. Change the 2nd line to show that and you're good to go.

    You generate the stop date the same, except you use directly $now, without substracting a set number of seconds from it.

    Let me know if you understand this, or I can explain differently.
    Programming boils down to three things: fast, good and cheap.
    Please pick two.

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Dublin
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sebulbus View Post
    Then the code above should work, except that you need only the last 6 days, not 7. Change the 2nd line to show that and you're good to go.

    You generate the stop date the same, except you use directly $now, without substracting a set number of seconds from it.

    Let me know if you understand this, or I can explain differently.
    I understand the concept but don't know how to work it into my sql query.

    I used to working with the following type of query "SELECT * , UNIX_TIMESTAMP(storydate) AS storydate_ts FROM content WHERE storydate > 20070819202854 AND storydate < 20070826202854"
    "
    but I don't know where to use the variable $yourStampFormat

    Any advice would be greatly appreciated.

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Location
    edge of nowhere
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I imagine you embed this SQL query in a php script something like this:

    PHP Code:
    $query "SELECT * , UNIX_TIMESTAMP(storydate) AS storydate_ts FROM content WHERE storydate > 20070819202854 AND storydate < 20070826202854"
    This should then become

    PHP Code:

    //get current timestamp and compute dates
    $now time();
    $aWeekAgo $now - (7*24*60*60);
    $startDelimiter date("YmdHis"$aWeekAgo);
    $endDelimiter date("YmdHis"$now);

    //modified query, using the above delimiters
    $query "SELECT * , UNIX_TIMESTAMP(storydate) AS storydate_ts FROM content WHERE storydate > ".$startDelimiter." AND storydate <".$endDelimiter;

    //rest of you code, etc 
    Last edited by sebulbus; Aug 26, 2007 at 07:04. Reason: typo
    Programming boils down to three things: fast, good and cheap.
    Please pick two.

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    Dublin
    Posts
    197
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sebulbus View Post
    I imagine you embed this SQL query in a php script something like this:

    PHP Code:
    $query "SELECT * , UNIX_TIMESTAMP(storydate) AS storydate_ts FROM content WHERE storydate > 20070819202854 AND storydate < 20070826202854"
    This should then become

    PHP Code:

    //get current timestamp and compute dates
    $now time();
    $aWeekAgo $now - (7*24*60*60);
    $startDelimiter date("YmdHis"$aWeekAgo);
    $endDelimiter date("YmdHis"$now);

    //modified query, using the above delimiters
    $query "SELECT * , UNIX_TIMESTAMP(storydate) AS storydate_ts FROM content WHERE storydate > ".$startDelimiter." AND storydate <".$endDelimiter;

    //rest of you code, etc 
    Thanks a million! That's works perfectly.


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
  •