SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast archive's Avatar
    Join Date
    Mar 2007
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    update field with current dateTime???

    Hi all again!

    I'm having this accessDate field and I want at special occassions to update it through a PHP script, with the current datetime (NOW in phpmyadmin).

    the field is: datetime NULL default '0000-00-00 00:00:00'

    the query below doesnot seem to work:

    UPDATE users SET accessDate = NOW WHERE loginID = xxxxxxx

    Any ideas? Thank you all!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you forgot the parentheses for NOW()

    you might want to use CURRENT_TIMESTAMP instead (it is standard SQL, whereas NOW() is mysql's proprietary equivalenmt)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast archive's Avatar
    Join Date
    Mar 2007
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your immediate response!

    I'm afraid that neither NOW() nor CURRENT_TIMESTAMP have any impact upon the field's value.

    Could it be the fact that these are (My)SQL functions and cannot be executed when php sends the query? I have to admit i doubt that..

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by archive View Post
    I'm afraid that neither NOW() nor CURRENT_TIMESTAMP have any impact upon the field's value.
    then something else is wrong

    have you tested the update query outside of php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast archive's Avatar
    Join Date
    Mar 2007
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeap! and works perfectly..

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    so you may have a php problem, yes?

    want me to move the thread to the php forum?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast archive's Avatar
    Join Date
    Mar 2007
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    please! thank for your time man.

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Post your php code please

  9. #9
    SitePoint Enthusiast archive's Avatar
    Join Date
    Mar 2007
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here you go:

    $query="UPDATE users SET accessDate=NOW() WHERE loginID = userLoginID ";

    Note that this query has also been run with quotes around NOW().

  10. #10
    SitePoint Enthusiast archive's Avatar
    Join Date
    Mar 2007
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now isn't that stupid or what????
    Just added below the query:

    $result = mysql_query($query);

    Man, I gotta take a break!

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)


    Btw, are you sure there isn't a $ missing in front of userLoginID?
    PHP Code:
    $query="UPDATE users SET accessDate=NOW() WHERE loginID = $userLoginID "

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by archive View Post
    Note that this query has also been run with quotes around NOW().
    that would turn it into a string, and that woun't work

    mysql has very "forgiving" syntax (some would call it what it is -- "wrong")

    for example, if you compare a numeric column like an auto_increment to a specific string like '3' then mysql will silently convert that string to the number 3

    however, that's as far as it goes, it will not convert 'NOW()' to a function call
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast archive's Avatar
    Join Date
    Mar 2007
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh sorry, there is a $ in the original script, i justforgot it when i edited the original ID to make it userLoginID. All in all i just didn't execute the query...


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
  •