Convert from 2003-05-10 to 10th May 2003

[Stu]

This one has been bugging me for the past hour at least so I'd be glad if someone could help me whilst I have some hair left intact 😉

What I have is a field in a MySQL database which receives data in the form of the curdate() function. As you know, this function puts the date in this format: 2003-05-10 but I want it in this format: 5th May 2003.

I think I've just about exhausted my book on PHP ("Beginning PHP4" by Wrox Press) and have yet to come up with an answer!

Here is my current code:

<title>View News</title>

</head>

</html>



<?php

$dbhost = 'localhost';
$dbusername = '***';
$dbuserpassword = '***';
$default_dbname = '***';
$db=mysql_connect($dbhost,$dbusername,$dbuserpassword);
mysql_select_db("sleyland");

$query = "SELECT id, subject, postedby, email, date, date_format(date, \'%D %b %Y\') as formatted_date, message FROM news ORDER BY id";
$result = mysql_query($query);

while ($query_data = mysql_fetch_array($result));
	{

$id = $query_data["id"];
$subject = $query_data["subject"];
$postedby = $query_data["postedby"];
$email = $query_data["email"];
$date = $query_data["date"];
$formatted_date = $query_data["formatted_date"];
$message = $query_data["message"];

echo "<font color=darkblue>'$subject'</font> posted by <a href=mailto:$email>$postedby</a>
 on $formatted_date <br>--------------------------<br>$message<br><br>==========================<br><br>";
}

?>

You can see the output of the script here: http://www.2312.co.uk/phpprojects/news/viewnews5.php

Thanks ever so much to any one who can help. This is one seriously annoying thing!

Stu

[cgraz]

http://www.mysql.com/doc/en/Date_and_time_functions.html

Follow that link and search the page for DATE_FORMAT

Cgraz

[Stu]

Hi cgraz.

From what I can see from the link you gave me, I already have that in this:

date_format(date, \'%D %b %Y\')

However, when I run the script, I am told the following:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/.sites/146/site439/web/phpprojects/news/viewnews5.php on line 21

Hopefully that means more to you than me!

[cgraz]

$result = mysql_query($query) or die(mysql_error());

mysql_error() will help you troubleshoot the problem.

Cgraz

[Stu]

Hi cgraz.

That code was useful in the fact that at least I don't get warnings that make little sense to me!

The code is now fixed and is posted below for anyone who may want it in the future.

<title>View News</title>

</head>

</html>



<?php

$dbhost = 'localhost';
$dbusername = '***';
$dbuserpassword = '***';
$default_dbname = '***';
$db=mysql_connect($dbhost,$dbusername,$dbuserpassword);
mysql_select_db("sleyland");

$query = "SELECT id, subject, postedby, email, date_format(date, '%M %e %Y'), message FROM news ORDER BY date DESC, id DESC";
$result = mysql_query($query);

while ($query_data = mysql_fetch_row($result))
	{

$id = $query_data[0];
$subject = $query_data[1];
$postedby = $query_data[2];
$email = $query_data[3];
$date = $query_data[4];
$message = $query_data[5];

echo "<font color=darkblue>'$subject'</font> posted by <a href=mailto:$email title=$postedby>$postedby</a>
 on $date <br>--------------------------<br>$message<br><br>==========================<br><br>";
}

?>

Thanks for the help cgraz 🙂

[eiler]

Good job - well done