SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast demonDESIGN's Avatar
    Join Date
    Dec 2003
    Location
    Perth, WA
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    automatically adding date to database

    hi Guys,

    I'm hoping this can be answered easily.

    I have an Orders table in a database.
    The Orders table holds order summaries, tax, total, cust id etc.

    I also want to automatically add to the date field with the date the order is added.

    Have tried now(); but am not sure if my problems are syntax or whether I'm using the wrong function.

    The error I get is "Call to undefined function now() in blah blah etc etc

    Any ideas?

    Thanks in advance.

    --Neil

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    It seems like you're trying to call it as a PHP function. It's a MySQL function:

    PHP Code:
    $query 'INSERT INTO tbl_name (date_field) VALUES(NOW())'
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SitePoint Enthusiast demonDESIGN's Avatar
    Join Date
    Dec 2003
    Location
    Perth, WA
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    right, so I can't call a mySQL function from PHP?

    for example, what code would I use if I can't to grab the current date and put it into a session variable?

    thanks
    --Neil

  4. #4
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by demonDESIGN
    right, so I can't call a mySQL function from PHP?
    The function is called when the query is executed.

    Quote Originally Posted by demonDESIGN
    what code would I use if I can't to grab the current date and put it into a session variable?
    Take a look at the date function.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  5. #5
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by demonDESIGN
    I also want to automatically add to the date field with the date the order is added.
    In your PHP script that processes your form, add this code where you register your variables:
    Code:
        $orderdate = date("Y-m-d H:i:s") ;
    Then add the $orderdate variable to the list of variables that is inserted to the database:

    Code:
    ("INSERT INTO tablename (orderdate) VALUES('$orderdate')")

    In the database, add a field for orderdate and set it to type: DATETIME. (revised)

    That will give you a date/timestamp in the format:
    "Thursday, February 17, 2005 8:19 pm"
    Last edited by michael - ohio; Mar 7, 2005 at 19:33.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, no, if you want to record the time, you can't use DATE, you have to use DATETIME

    in any case, it would be more efficient to let mysql insert the current value, rather than forcing it to parse some string that you pass to it from an external source

    if you want date only, use the mysql function CURRENT_DATE

    if you want date and time, use the mysql function CURRENT_TIMESTAMP

    (note NOW() is the same as CURRENT_TIMESTAMP)

    use the syntax in post #2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy -

    The php format presented is exactly what I am using in my script:
    $date = date("Y-m-d H:i:s") ;

    date() is the PHP function which I use in my script to add a $date to the confirmation email I send out upon form submit. The script also saves the variable $date to a MySQL db field named 'date' and set as "type=datetime".

    So, the qustion is: what's being saved in the MySSQL field? The $date variable created by PHP (as the insert command directs) or MySQL's own datetime time stamp? And if it is MySQL's own datetime, then why don't I get an error when my script tries to save the vsriable $date to the same field?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you are saving your $date value

    you're passing in to mysql a string of characters, which, after mysql parses them, it accepts as a valid datetime value, or not (but if you're using $date, it likely will)

    i was just suggesting that using a keyword, like CURRENT_DATETIME, would allow mysql to use its system clock datetime value immediately, instead of having to parse a string of characters to ensure it contains valid year, month, day, hour, minute, and second values, even if it uses a fairly loose interpretation of "valid" and will allow, for example, february 31, if that's what you feed it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict michael - ohio's Avatar
    Join Date
    Dec 2004
    Location
    ohio
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you're passing in to mysql a string of characters, which, after mysql parses them, it accepts as a valid datetime value, or not (but if you're using $date, it likely will)

    Well, yes, MySQL accepts them: created by the PHP DATE function they are the same format as the mysql datetime type, aren't they? This has been properly and correctly saving a time stamp in my DB for a couple of months now (with around 2,000 entries)... without error.

    i was just suggesting that using a keyword, like CURRENT_DATETIME, would allow mysql to use its system clock datetime value immediately,...

    Ok, I see how that makes sense. I'd still have to use the PHP DATE function to include a date/time in the body of the email confirmation that the script sends out, but I could stop wasting server time by changing the DB date save to the MySQL keyword... does that about sum it up correctly?


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
  •