Oh no, another date format question!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Noobie
Forum Commoner
Posts: 85
Joined: Sun May 15, 2005 11:38 am

Oh no, another date format question!!

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

it's never a bad idea to check the manual

try:

Code: Select all

SELECT DATE_FORMAT(newsdate, '%d/%m/%Y')....;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

select id, DATE_FORMAT(`newsdate`, '%d-%m-%Y') AS `newsdate`, newssummary from archive ORDER BY newsdate DESC
Noobie
Forum Commoner
Posts: 85
Joined: Sun May 15, 2005 11:38 am

Post 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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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?
Noobie
Forum Commoner
Posts: 85
Joined: Sun May 15, 2005 11:38 am

Post 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.
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

That faint apostrophe - ` is called backtick and is very useful for escaping names in MySQL syntax ;)
Post Reply