mySQL date into useable format

sovereign

I am designing a PHP script that uses mysql to update news. I can store the date into mysql in the mysql format, but when I go to fetch the data I want the date to change from YYYY/MM/DD to DD/MM/YYYY. The trick is that I don't know how to use the mysql DATEFORMAT function when I need to grab the code with the date desc.

Here's some code to show you guys what I'm talking about.

<?php
include "mysql.php";
$sql = "SELECT * FROM news ORDER BY NewsDate DESC";
$result = @($sql);
if (!$result){
echo ('<p>Could not complete the MySQL query command.</p>');
exit();
}
...

				//print the news
				while( $row = mysql_fetch_array($result) ){
					echo ('
					<table width="490" border="0" cellpadding="0" cellspacing="0" bordercolor="#CCCCCC">
						<tr>
							<td valign="top">
							<span class="news-text">'
							.$row['NewsDate']).
							'</span><br><span class="text">'
							.$row['Message'].
							'</span><br><br>';[/I]

bpat1434

//$sql = "SELECT * FROM news ORDER BY NewsDate DESC";
$sql = "SELECT *, DATE_FORMAT(NewsDate, %d/%m/%Y) as newDate FROM news ORDER BY NewsDate DESC";

~Brett

sovereign

Sorry, that code doesn't work. I got the error.

Could not complete the MySQL query command.

Weedpacket

That is a perfectly legitimate MySQL query. Instead of using your own generic error message (which doesn't tell you anything except "it doesn't work"), have a look at the error message returned by [man]mysql_error[/man]. And take off the @ unless you really do want to ignore error messages (which obviously you don't want to do because without them it's harder to fix something that's going wrong).

Then go over to MySQL's web site, and have a look in its manual about how to use the DATE_FORMAT function.

xblue

Weedpacket wrote:
That is a perfectly legitimate MySQL query.

I thought mysql would require the string that specifies the format to be quoted?

I am totally agreeing with Weedpacket's suggestions though.

bpat1434

True, there is the possibility that they need to be quoted. Even still, it's an error that he could have figured out on his own...

$sql = "SELECT *, DATE_FORMAT('NewsDate', '%d/%m/%Y') as newDate FROM news ORDER BY NewsDate DESC";

~Brett

sovereign

Thanks for the help guys. I'm not at my computer right now, but I'll try it out when I get home. I'm pretty new to this whole PHP and MySQL thing, but I'm slowly getting the hang of it.

sovereign

The code seems to work, but I can't call on the variable newDate.

I use:

<span class="h1">'	
.$row['newDate']).

and it shows up as nothing.

bpat1434

Okay, so is the date in the database actually being stored as "12/1/2005" or is it a timestamp? Other than that, it should work as I've used it before...

~Brett

sovereign

Hi guys, I figured out the error:

Your code:

 $sql = "SELECT *, DATE_FORMAT('NewsDate', '%d/%m/%Y') as newDate FROM news ORDER BY NewsDate DESC";

was almost correct, you're not supposed to put quotes around NewsDate.

The correct code would be:

 $sql = "SELECT *, DATE_FORMAT(NewsDate, '%d/%m/%Y') as newDate FROM news ORDER BY NewsDate DESC";

Thanks for the help guys, saved me a lot of trouble.

bpat1434

Well, not quotes since it's a column name. Backticks would have worked. Small, minor issue. Good to hear you got it working.

~Brett