[Ole]

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

[rod k]

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

[Kyuzo]

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...

[Ole]

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.