SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2005
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting Date into Table

    Hey there,

    I am wanting to insert a date into a table for the date something was inputted into the database. Which would be the best (or only) way to do that?

    1. DATE function in MySQL
    2. date() function in PHP and submit the output into MySQL

    If there are any other ways, let me know, as I would like this to be the best possible way and work all the time.

  2. #2
    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)
    the best way is 1., except the DATE function is the wrong one

    use the CURRENT_DATE function for DATE columns, and the CURRENT_TIMESTAMP function for DATETIME columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2005
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "INSERT INTO mytable SET thedate = NOW()"

    Alternatively, you can create your table with a timestamp field. The first timestamp field is always set every time the record is created/updated. Might not be what you want.

    If your database is on a different server than where the script is running and you want the script to determine the time then you will need to use php to generate the date/time

    $thedate = date("Ymd");
    "INSERT INTO mytable SET thedate = '$thedate'"

    Good luck with your project.
    PHP Shopping Cart Software Easy Ecommerce Shopping Cart Script.
    PHP Super Cart is 100% template driven.

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2005
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, if I want to keep my script so it will work on several different types of servers (such as different PHP/MySQL versions), is "INSERT INTO mytable SET thedate = NOW()" going to work with all the current versions out there?

  5. #5
    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)
    instead of "INSERT INTO mytable SET thedate = NOW()" (which is horribly non-standard), please use

    INSERT INTO mytable (thedate) VALUES (CURRENT_TIMESTAMP)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Sep 2005
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

    LOCALTIME and LOCALTIME() are synonyms for NOW().

    NOW()

    Now() Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.

    The INSERT syntax I used is also correct.

    You can spend lots of time making sure your SQL statements run across all kinds of databases, or you can build your application.

    My syntax is 100% mysql stanard and will work across any version of mysql on any host.

    Good luck with your project.
    PHP Shopping Cart Software Easy Ecommerce Shopping Cart Script.
    PHP Super Cart is 100% template driven.

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jondolar View Post
    You can spend lots of time making sure your SQL statements run across all kinds of databases, or you can build your application.
    or option C: spend a little bit of time learning the standard syntax that works on your target server and use that, and avoid syntaxes that are known to be incompatible. it doesn't take that much effort and when your next client asks for a non-mysql application you don't have to reinvent the wheel or learn a bunch of new stuff.

  8. #8
    SitePoint Addict
    Join Date
    Sep 2005
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree with that
    PHP Shopping Cart Software Easy Ecommerce Shopping Cart Script.
    PHP Super Cart is 100% template driven.

  9. #9
    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)
    Quote Originally Posted by jondolar View Post
    The INSERT syntax I used is also correct.
    oh, it's fine for mysql, but it is still non-standard

    try to port your app to any other database and you will see what i mean

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

  10. #10
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jondolar View Post
    I agree with that
    ok, then you also have to agree to (try) not use the INSERT/SET syntax and NOW().

  11. #11
    SitePoint Zealot
    Join Date
    Dec 2005
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, thanks for all the info!

    Also, I use the following INSERT syntax already:
    Code:
    INSERT INTO table (field) VALUES ('value1')


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
  •