Page 1 of 1

Oh no, another date format question!!

Posted: Thu Oct 27, 2005 11:05 am
by Noobie
Hi again

Final question from me for the week (promise) and it's a real Noobie special :?

I want to format my date to appear as dd-mm-yyyy when it appears on the page. I've been through lots of previous posts on the subject and I understand that it's best to use mySQL's date_format to do this - but I can't for the life of me figure out how to include that into the existing query without breaking it (again and again and again...).

I think this is the relevant bit that needs the date_format adding to it:

Code: Select all

$result = @mysql_query('select id, newsdate, newssummary from archive ORDER BY newsdate DESC');
where archive is the table and newsdate is the column that needs formatting.

I'd be pathetically grateful if anyone can explain how to fit date_format into the above bit of code (once you've all stopped laughing of course!)

For the record, and in case I've got the wrong bit above - the complete code looks like this:

Code: Select all

<?php
    $dbh=mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error());
    mysql_select_db ("mydatabase");
    	
			echo '<h3>Here are all our news items: </h3>';
			$result = @mysql_query('select id, newsdate, newssummary from archive ORDER BY newsdate DESC');
			if (!$result) {
			exit('<p>Error performing query: ' .
			mysql_error() . '</p>');
			}

			while ($row = mysql_fetch_array($result)) {
			$newsid = $row['id'];
			$newsdate = $row['newsdate'];
			$newssummary = $row['newssummary'];
			echo "<p>" .  $newsdate .  "<br />"  .   
			"<a href='news2.html?id=$newsid'>" . "<img src=\"images/doc.gif\" alt=\"news item\">" . $newssummary . "</a>"  .  "</p>";
			}
			
			$newssummary = htmlspecialchars($newssummary);
			$newstext = htmlspecialchars($newstext);
			
	?>
Thank you for your continued help!

Noobie

Posted: Thu Oct 27, 2005 11:30 am
by Burrito
it's never a bad idea to check the manual

try:

Code: Select all

SELECT DATE_FORMAT(newsdate, '%d/%m/%Y')....;

Posted: Thu Oct 27, 2005 11:33 am
by feyd

Code: Select all

select id, DATE_FORMAT(`newsdate`, '%d-%m-%Y') AS `newsdate`, newssummary from archive ORDER BY newsdate DESC

Posted: Thu Oct 27, 2005 11:40 am
by Noobie
I checked it. checked it again, had a bit of lunch, checked it again - still can't get it to work.

I tried this:

Code: Select all

$result = @mysql_query('SELECT DATE_FORMAT(newsdate, '%d/%m/%Y') id, newsdate, newssummary from archive ORDER BY newsdate DESC');
But I get an error message about an unexpected %

This is my problem. I understand the

Code: Select all

SELECT DATE_FORMAT(newsdate, '%d/%m/%Y')
bit - but can't seem to fit it into the rest of the existing query successfully.

Posted: Thu Oct 27, 2005 11:42 am
by Burrito
do as Feyd suggested and alias the the field

also you're missing a comma...

finally, why are you selecting it again after you've formatted it?

Posted: Thu Oct 27, 2005 11:48 am
by Noobie
Thank Feyd - after some fiddling with the apostrophes it now works beautifully.

Code: Select all

$result = @mysql_query("select id, DATE_FORMAT(newsdate, '%d-%m-%Y') AS newsdate, newssummary from archive ORDER BY newsdate DESC");
forgive me 'cos I'm new to this - was a bit confused by the faint apostrophe around newsdate (when I pasted them).

I'm going to lie down in a dark room for a while now.

Posted: Fri Oct 28, 2005 1:51 am
by n00b Saibot
That faint apostrophe - ` is called backtick and is very useful for escaping names in MySQL syntax ;)