SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP/MySQL Date Conversion Problem...Newbie

    I need help converting the date format of mysql's defaut YYYY-DD-MM using PHP. Heres the code from my page:
    ---------------------------
    //connect to the database server
    $db = @mysql_connect("localhost", "u", "p");

    //connect to the *MAIN* Database
    mysql_select_db("db",$db);

    // print the list if there is not editing
    $result = mysql_query("SELECT * FROM News",$db);
    while ($myrow = mysql_fetch_array($result)) {

    while ($myrow = mysql_fetch_array($result)) {
    printf("<br>".
    "<b>&nbsp;<u>%s</u></b><br>".
    "&nbsp;<font face=Verdana size=1>%s</font><br>".
    "<p>&nbsp;&nbsp;&nbsp;%s<br>".
    "<hr width=600 color=#cccccc size=1 noshade> \n", $myrow["NewsHeadLine"], date("d m, Y", $myrow[NewsDate]), $myrow["NewsStory"]);
    }


    }
    __________________________________________---
    The results that I get back are "31 12, 1969"

    Why is this (I think it has something to do with UNIX timestamps...not sure) and what code do I use if for example the date in the DB was "2001-11-14" PHP would return "November, 14 2001"?

    Thanks,
    Brian Barnes
    --------------------------
    "Do or do not, there is no try" - Yoda

  2. #2
    SitePoint Enthusiast UCAP's Avatar
    Join Date
    Jun 2001
    Location
    Switzerland
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Replace the "d m, Y" in the date() function with "F, j Y".

    PHP manual: date

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well thats great...It really doesn't solve my problem however. I have the PHP manual and I have bought several books such as Welling and Thomson's "PHP and MySQL Web Development", Kevin's book, The PHP Bible, and a few on just MySQL....I can't find the answer.

    I should of went into more detail on the real problem. I have the date 2001-11-12 in my database and the script above is returning the date as 1969-12-31.....Its way off on the year(about 30 years off) not to mention the month and day.....

    Why is this?
    Brian Barnes
    --------------------------
    "Do or do not, there is no try" - Yoda

  4. #4
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The date() function only works with unix timestamps. Here's a function I wrote to convert a mysql date field into an unix timestamp and then reformats it to a normal date:
    PHP Code:
    function makeDate($mysql_date)
    {
        
    $bad explode("-"$mysql_date);
        
    $year $bad[0];
        
    $month $bad[1];
        
    $day $bad[2];
        
    $final mktime(000$month$day$year);
        
    $final date("m-d-Y"$final);
        return 
    $final;


  5. #5
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No need to convert the date with PHP, just use the mySQL date_format() function, look
    PHP Code:
    <?php
    $db 
    = @mysql_connect"localhost""u""p"); 

    mysql_select_db("db",$db); 

    $result mysql_query("SELECT *, DATE_FORMAT(NewsDate,'%e %c, %Y') as NewsDate FROM News",$db);
    while (
    $myrow mysql_fetch_array($result)) { 
    ?>
       <br><b> <u><?=$myrow['NewsHeadLine']?></u></b><br>
       <font face=Verdana size=1><?=$myrow['NewsDate']?></font><br>
       <p><?=$myrow['NewsStory']?><br>
       <hr width=600 color=#cccccc size=1 noshade>
    <?php

    ?>
    Paul

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I found the answer

    I found the solution to the problem that I was having. I am posting it hoping that it will help others who may need it...

    _______________________________________________

    //connect to the database server
    $db = @mysql_connect("localhost", "user", "pass");

    //connect to the *MAIN* Database
    mysql_select_db("database",$db);

    // print the list if there is not editing
    $result = mysql_query("SELECT DATE_FORMAT(NewsDate, '%M %d, %Y'), NewsHeadLine, NewsStory FROM News",$db);
    while ($myrow = mysql_fetch_array($result)) {
    printf("<br>".
    "<b>&nbsp;<u>%s</u></b><br>".
    "&nbsp;<font face=Verdana size=1>%s</font><br>".
    "<p>&nbsp;&nbsp;&nbsp;%s<br>".
    "<hr width=600 color=#cccccc size=1 noshade> \n", $myrow["NewsHeadLine"], $myrow["DATE_FORMAT(NewsDate, '%M %d, %Y')"], $myrow["NewsStory"]);



    }
    _____________________________________________-

    NewsDate is the Date Col. in my DB...
    Notice the query DATE_FORMAT(NewsDate, '%M %d, %Y')
    ..Then I called it as..
    $myrow[DATE_FORMAT(NewsDate, '%M %d, %Y')"],
    ______________________________________________
    Brian Barnes
    --------------------------
    "Do or do not, there is no try" - Yoda

  7. #7
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: I found the answer

    Originally posted by geochemguy
    I found the solution to the problem that I was having. I am posting it hoping that it will help others who may need it...
    geochemguy, looks like my code

    BTW, you don't need this thing : $myrow[DATE_FORMAT(NewsDate, '%M %d, %Y')"].

  8. #8
    SitePoint Enthusiast
    Join Date
    Nov 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It basically is the same code that you entered.

    However, I had not read your post when I figured this out. I was actually writing my last post when your email arrived to tell me that someone had posted :-)

    I swear.

    Thanks for your help though. Where were you several hours ago "-)
    Brian Barnes
    --------------------------
    "Do or do not, there is no try" - Yoda

  9. #9
    Happy Holidays !! Paul S's Avatar
    Join Date
    Mar 2001
    Location
    Mexico
    Posts
    1,287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by geochemguy
    It basically is the same code that you entered.

    However, I had not read your post when I figured this out. I was actually writing my last post when your email arrived to tell me that someone had posted :-)

    I swear.
    No problem


    Thanks for your help though. Where were you several hours ago "-)
    Working (I guess ... )


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
  •