SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select records for today

    In PHP I want to do an SQL to select records that were added today.

    I cant work out what the best way is. The date is recorded as a timestamp.

    This is what I have tried, but not sure it is right or best:

    "SELECT * FROM chat WHERE sent>=".mktime(0,0,0,date("m"),date("d"),date("Y"))." and sent<= " .mktime(23,59,59,date("m"),date("d"),date("Y"))

    The timestamps generated from the php was:
    1258927200 Sun, 22 Nov 2009 22:00:00 GMT = from
    1259013599 Mon, 23 Nov 2009 21:59:59 GMT = to
    Not start/end of day.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    first, let's have a look at the datatype of the sent column

    second, are you sure you're asking a database question and not a php question?

    by the way, what database is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I wasnt sure if this should be under PHP or MySQL cos its a mix of both.

    Sent is an int(10) - seconds since 1970 (or whatever). Sorry to confuse with the use of term timestamp - incorrect I know.

    Its MYSQL DB.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the reason i asked about the database is because we do have a MySQL forum but you decided to use the Databases forum instead

    nevertheless, your question is a php question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $start mktime(000);
    $end mktime(235959); 
    Output:
    PHP Code:
    echo date('m/d/Y H:i:s'mktime(000));
    echo 
    date('m/d/Y H:i:s'mktime(235959)); 
    11/23/2009 00:00:00
    11/23/2009 23:59:59

  6. #6
    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)
    PHP Code:
    <?php
    $sql 
    sprintf(
        
    "SELECT * FROM chat WHERE sent BETWEEN '%s' AND '%s'",
        
    date('Y-m-d H:i:s'mktime(000)),
        
    date('Y-m-d H:i:s'mktime(235959))
    );
    #SELECT * FROM chat WHERE sent BETWEEN '2009-11-23 00:00:00' AND '2009-11-23 23:59:59'
    ?>
    @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.

  7. #7
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the reason i asked about the database is because we do have a MySQL forum but you decided to use the Databases forum instead

    nevertheless, your question is a php question
    Sorry its 2am here. I meant to post in MySQL not general DB forum.
    But yeah probably more on the PHP than MySQL side

  8. #8
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I think this is the format I need:
    $start = mktime(0, 0, 0);
    $end = mktime(23, 59, 59);

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    WHERE sent BETWEEN '2009-11-23 00:00:00' AND '2009-11-23 23:59:59'
    no

    if you were going to do it in the SQL (which some would argue you shouldn't), then you would use this instead --
    Code:
    WHERE sent >= UNIX_TIMESTAMP('2009-11-23')
      AND sent  < UNIX_TIMESTAMP('2009-11-24')
    or, even more to the point, this --
    Code:
    WHERE sent >= UNIX_TIMESTAMP(CURRENT_DATE)
      AND sent  < UNIX_TIMESTAMP(CURRENT_DATE + INTERVAL 1 DAY)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok guys. next part to the question, which is purely SQL not php related.

    I want to count messages from user 8 where the recipient of the messages is unique between these date ranges - to see how many unique people they contacted.

    2nd half- I would further like to refine the above, to only count the times the unque person replied - but this part is over my head.
    Eg.
    if "user id: 1" contacts "user id: 2" 10 times and "user id: 2" replies ( once or more ) = 1 successful contact attempt

    if "user id: 1" contacts "user id: 3" 20 times (or however many) and "user id: 3" doesnt reply = failed contact attempt

    the table format is:
    id (unique auto increment), from (user id of sender), to (user id of recipient), sent (time as int(10))

    each message = 1 record
    so if user 1 contacts user 2 = 1 record (from = 1, to = 2)
    and user 2 replies to user 1 = 1 more record (from = 2, to = 1)
    and user 2 messages user 3 = another record (from = 2, to = 3)

    I really hope this makes sense.

    So far I got:
    SELECT count(DISTINCT messages.to) FROM messages WHERE messages.from = 8 and messages.sent >= 1258927200 and messages.sent <= 1259013599
    But that only counts unique attempts (successful and failed contact attempts), not just successful attempts.

  11. #11
    SitePoint Evangelist
    Join Date
    Jan 2005
    Posts
    425
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh this thread has been moved to PHP so second part isn't appropriate to PHP section.
    Ive created a thread in MySQL section.
    Please ignore this.
    Refer: http://www.sitepoint.com/forums/show....php?p=4439761


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
  •