#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Location
    Denmark
    Posts
    53
    Rep Power
    15
    Hi!
    Some of you have probaly seen this before.....
    I'm pretty new in this and have a problem about reformatting a DATE field from a mySQL db to DDMMYYYY.
    After looking in the manual I tried this and it does not work:
    <?PHP

    MYSQL_CONNECT("127.0.0.1","root") OR DIE("No connection to database");
    mysql_select_db("theater") OR DIE("Unable to find database");

    $query = mysql_query("SELECT date_format(opening,'%d%m%Y'),headline,text,credits FROM opera ORDER BY opening");
    ECHO "<TABLE WIDTH=100% BORDER=1 CELLSPACING=0 CELLPADDING=0>";

    while($r = mysql_fetch_array($query)){

    $headline = $r["headline"];
    $opening = $r["opening"];
    $text = $r["text"];
    $credits = $r["credits"];

    "<TR><TD>$headline</TD></TR><TR><TD VALIGN=top>Opening night: $opening</TD></TR><TR><TD VALIGN=top>$text</TD></TR><TR><TD VALIGN=top>$credits</TD></TR>
    }
    "</TABLE>";*/
    }
    ?>
    Everything else is showing as it should, but not at all the date! As I said, I'm pretty new in mySQL and PHP, but where did I go wrong???
    Ole
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Since you are using the mysql_fetch_array() and an associative array for the result, you can't just use "opening" as the index since you're returning the result of a function you've preformed on opening, rather than the value of opening itself. (To see what I mean, do your query at the command line and see how mysql labels the column.)

    What you need to do is alias the result:

    $query = mysql_query("SELECT date_format(opening,'%d%m%Y') AS f_open,headline,text,credits FROM opera ORDER BY opening");

    Then use "f_open" as the index in the result array.

    HTH
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    16
    In your query, make date_format(opening,'%d%m%Y')into an aliased column by using ....date_format(opening, '%d%m%Y') as formatted_opening.....(or whatever alias you wish)

    then use Opening night: $formatted_opening...
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Location
    Denmark
    Posts
    53
    Rep Power
    15
    It's working, thanks!!!
    But I don't understand the %'s. As you told me and as far as I can see in the mySQL manual the correct way to make it is with %'s, but the result is that the date is displayed like %12.%4.%2000.
    This way the date is displayed as it should:
    $query = mysql_query("SELECT date_format(opening,'d.m.Y') AS f_open,headline,text,credits FROM opera ORDER BY f_open");
    I'm working on Windows98 with 3.21.29a-gamma mySQL and PHP 3.0.14.

    What about update? The data is also shown in a form, by which it should be possible to change the dates, texts and on, but I can't figure out how to change the date from DDMMYYYY (as it will be written in Denmark) to YYYYMMDD as mySQL wants it.
    IF($update) {
    MYSQL_CONNECT("127.0.0.1","root");
    mysql_select_db("theater");
    $query = MYSQL_QUERY("UPDATE opera SET headline = '$headline',opening = '$f_opening',tekst = '$tekst',credits = '$credits' WHERE headline = '$headline' ");
    }

    Ole, Denmark.

Similar Threads

  1. Empty date & date format
    By anca in forum Visual Basic Programming
    Replies: 2
    Last Post: February 17th, 2004, 04:38 AM
  2. Replies: 3
    Last Post: February 8th, 2004, 02:55 PM
  3. Retreive date in different format
    By marco-marco in forum MySQL Help
    Replies: 2
    Last Post: December 24th, 2003, 04:15 AM
  4. changing date format from 12/12/2002 to 12-12-2002
    By kimy_cool in forum PHP Development
    Replies: 5
    Last Post: September 19th, 2003, 02:35 PM
  5. What is the proper date format for a txt database?
    By jobesd in forum PHP Development
    Replies: 6
    Last Post: June 14th, 2001, 04:16 PM

IMN logo majestic logo threadwatch logo seochat tools logo