SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    durango
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Statement Problems - Month & Year

    I am trying to retirve the numbers of records for a specific user for the current month and year. Does anyone see a problem with is statement:

    PHP Code:
     
    $monthsessions 
    mysql_result(mysql_query("SELECT COUNT(*) FROM login WHERE track_users_ID = '$users_ID' AND MONTH(track_date()) = MONTH(CURDATE()) AND YEAR(track_date()) = YEAR(CURDATE())"),0); 
    If I only had a brain.

  2. #2
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is "track_date()" a function that you created? If it is, as far as I know, it will not work within the query.
    John

  3. #3
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    durango
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks fot the reply

    track_date is a datetime field in my database.

    the format of the field is 0000-00-00 00:00:00

    I just assumed that because it is a date it should work the same way. Should I do something different?

  4. #4
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have track_date written as a function call. This would cause problems.

    I have found the easiest way to work with dates is to store them as a timestamp and then format them as needed with the date() function.

    This is my preference.
    John

  5. #5
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What PHP John means is that your query should be like this:
    Code:
    SELECT COUNT(*)
    FROM login
    WHERE track_users_ID = '$users_ID'
      AND MONTH(track_date) = MONTH(CURDATE())
      AND YEAR(track_date) = YEAR(CURDATE())

  6. #6
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Redemption!

    I'm not at all familiar with the mySQL date functions.
    John

  7. #7
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't thank me. I didn't really do anything if you take a look at the query. I just removed the '()' from the end of track_date() like you said.

  8. #8
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    durango
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool! It is working now.

    Thanks,
    Scott


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
  •