Need help with date_format using PHP/MySQL.

I've read pretty much every post on four sites for six hours, and still have not found an answere to this. I have a query, and am trying to format the date to a "Friday June 6th, 2000" or whatever. The field is a date field. I run the query, but the line where the date would be just doesn't show up. Below is the script:

<?php

$db = mysql_connect("localhost", "******");
mysql_select_db("creed",$db);
$result = mysql_query("SELECT diaryweek, diarytitle,
diarybody, date_format(diarydate,'%M %D, %Y')
FROM tourdiary
where diaryweek like 'April 5 - 15' ORDER by diarydate DESC",$db);
printf("<center><font face=Arial,Helvetica size=3
color=yellow><b>Tour Diary: %s</font></b></center><br>n",
mysql_result($result,0,"diaryweek"));
if ($myrow = mysql_fetch_array($result)) {
echo "<table border=0 cellpadding=6 width=400 align=center>n";
do {

printf("<tr><td valign=top align=left>
<font face=Arial,Helvetica size=2
color=white><b>%s</b><p>%s<p></td></tr>n",
$myrow["diarydate"],
$myrow["diarybody"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table>n";
} else {
echo "Sorry, no tour info";
}

?>

What am I doing wrong?

You need to alias the date_format result:

$result = mysql_query("SELECT diaryweek, diarytitle,
diarybody, date_format(diarydate,'%M %D, %Y') as diarydate
FROM tourdiary
where diaryweek like 'April 5 - 15' ORDER by diarydate DESC",$db);


That will work for you.

It gave me a:

: 0 is not a MySQL result index

Hmm. Does it have to do something with this part of the script using a printf statement?


if ($myrow = mysql_fetch_array($result)) {
echo "<table border=0 cellpadding=6 width=400 align=center>n";
do {

printf("<tr><Atd valign=top align=left><font face=Arial,Helvetica size=2
color=white><b>%s</b><p>%s<p></td></tr>n",
$myrow["diarydate"],
$myrow["diarybody"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table>n";
} else {
echo "Sorry!";
}

It might have something to do with the alias being the same as the field name. Try changing the alias and the corresponding $myrow["diarydate"] to something else. If that doesn't work, put:
print mysql_error();
after the query.

It doesn't have anything to do with your printf statement. It means mysql returned an error.

It's working. My fault. No sleep for two days you start to lose focus.

There was no space between my alias and the "FROM tourdiary", so it basically said,

"date_format(diarydate,'%M %D, %Y') as dateFROM tourdiary"

and returned the error, database not found.

Thanks, saved my butt!