www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Inserting PHP Dates Into MySQL

  1. #1
    Join Date
    Dec 2007
    Location
    Mississippi
    Posts
    1,063

    Inserting PHP Dates Into MySQL

    If you format how today's date is supposed to look in PHP, and then insert it into MySQL (into either a date or datetime field, because I don't know the difference), will it stay formatted correctly when you take it out of the database?

  2. #2
    Join Date
    Mar 2007
    Posts
    946
    date is just the date. For exampe YYYY-MM-DD

    datetime is the date and time so YYYY-MM-DD 00:00:00

    You can insert your php date as long as it is in the format above depending on which one you choose.

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,537
    You can control the format when it is retrieved from the DB via MySQL's DATE_FORMAT() function.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Dec 2007
    Location
    Mississippi
    Posts
    1,063
    Quote Originally Posted by NogDog View Post
    You can control the format when it is retrieved from the DB via MySQL's DATE_FORMAT() function.
    Would that just go in the SQL query you construct with PHP?

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,537
    Yes, e.g.:
    PHP Code:
    $sql "SELECT `title`, DATE_FORMAT(`date_submitted`), '%Y/%m/%d') AS `date` FROM `some_table`"
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Dec 2007
    Location
    Mississippi
    Posts
    1,063
    What if your query was getting a lot out, like:

    post_id
    author_id
    posted_by
    post_title
    date (obviously)
    post_body

    How would it work when you pulled that much out?

  7. #7
    Join Date
    Sep 2008
    Location
    Tel Aviv, Berlin, L.A.
    Posts
    165
    Not any different. And, by the way, six columns is all but much.

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,537
    I just noticed I had a typo in my last response. There's an extra ")". It should be:
    PHP Code:
    $sql "SELECT `title`, DATE_FORMAT(`date_submitted`, '%Y/%m/%d') AS `date` FROM `some_table`"
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,537
    Quote Originally Posted by Joseph Witchard View Post
    What if your query was getting a lot out, like:

    post_id
    author_id
    posted_by
    post_title
    date (obviously)
    post_body

    How would it work when you pulled that much out?
    If it really bothers you to take a minute to type out the column names, you could always do:
    Code:
    SELECT *, DATE_FORMAT(`date`, '%Y/%m/%d') AS `date_ymd` . . .
    But really, it makes things clearer in your code if you go ahead and type out all the column names and also gets rid of any ambiguity as to what is being retrieved. Besides, it also may save some processing if you do not, in fact, actually need every column in that table). Never worry about saving time by avoiding a little typing now, when it might save you a lot of time later when debugging/updating the code later.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  10. #10
    Join Date
    Dec 2007
    Location
    Mississippi
    Posts
    1,063
    So the AS DATE part won't mess up any of the other columns?

    PHP Code:
    <?php

    $sql 
    "SELECT post_id, post_title, posted_by, DATE_FORMAT(posted_when, %Y, %m, %d) AS DATE FROM table";
    That would be the correct syntax to get the year, month, and date?

  11. #11
    Join Date
    Jul 2005
    Posts
    159
    Quote Originally Posted by Joseph Witchard View Post
    So the AS DATE part won't mess up any of the other columns?

    PHP Code:
    <?php

    $sql 
    "SELECT post_id, post_title, posted_by, DATE_FORMAT(posted_when, %Y, %m, %d) AS DATE FROM table";
    That would be the correct syntax to get the year, month, and date?

    DATE_FORMAT(`date_submitted`, '%Y/%m/%d') = 2008/10/1
    DATE_FORMAT(`date_submitted`, '%Y-%m-%d') = 2008-10-1
    DATE_FORMAT(`date_submitted`, '%Y . %m . %d') = 2008 . 10 . 1

  12. #12
    Join Date
    Dec 2007
    Location
    Mississippi
    Posts
    1,063
    Thanks everyone!

  13. #13
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,537
    Quote Originally Posted by Joseph Witchard View Post
    So the AS DATE part won't mess up any of the other columns?...
    Just try to pick an alias name (the word after "AS") that will not collide with any other of the column names being selected and that is descriptive enough that it's easy to remember.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  14. #14
    Join Date
    Dec 2007
    Location
    Mississippi
    Posts
    1,063
    You mean I can just pick virtually any name I want to use instead of the word DATE?

  15. #15
    Join Date
    Sep 2008
    Posts
    408
    Yup, you can make it banana_sauce and it would be usable as: $row['banana_sauce']
    PHP Code:
    <?
     $sql 
    mysql_query("SELECT post_id, post_title, posted_by, DATE_FORMAT(posted_when, %Y, %m, %d) AS banana_sauce FROM table LIMIT 1");
     
    $row mysql_fetch_row($sql);
     print 
    $row['banana_sauce']; # prints 2008, 1, 1
    ?>

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles