DATE_FORMAT problems

[rkwinternet]

Hi,

When I upload the following code to the server without including the DATE_FORMAT code (and the alias) everything behaves as it should i.e. if there is no "ID" parsed in the URL the most recent press release is displayed in the body of the page.

However, as soon as I want to change the date format from mysql's standard output and use DATE_FORMAT, as below, the most recent press release is NOT displayed, but another one which is older - this should not happen!

Can anyone see any flaws in my code/put me on the right track coz it's driving me nuts!

==================

Code with DATE_FORMAT

<?php
if ( !isset($_GET[ID]) ) {
$result = mysql_query("SELECT DISTINCT ID, Title, DATE_FORMAT(PressDate, '%e %M %Y') AS fDate, Copy FROM press ORDER BY fDate DESC LIMIT 1");

                            if (!$result) {

                            print ("<P>Error performing query: " .
                                    mysql_error() . "</P>");
                                    exit();
                             }

                            while ( $row = mysql_fetch_array($result) ) {

							print ("<h3>" . $row["Title"] . "</h3>"); 
                            print ("<p><strong>" . $row["fDate"] . "</strong></p>");
							print ($row["Copy"]);
							  }
							}		
							else {
							$result = mysql_query("SELECT DISTINCT ID, Title, DATE_FORMAT(PressDate, '%e %M %Y') AS fDate, Copy  FROM press WHERE ID = $ID");

                            if (!$result) {

                            print ("<P>Error performing query: " .
                                    mysql_error() . "</P>");
                                    exit();
                             }

                            while ( $row = mysql_fetch_array($result) ) {

							print ("<h3>" . $row["Title"] . "</h3>"); 
                            print ("<p><strong>" . $row["fDate"] . "</strong></p>");
							print ($row["Copy"]);
							  }

							}
							?>

====================

Code without DATE_FORMAT

<?php
if ( !isset($_GET[ID]) ) {
$result = mysql_query("SELECT DISTINCT ID, Title, PressDate, Copy FROM press ORDER BY PressDate DESC LIMIT 1");

                            if (!$result) {

                            print ("<P>Error performing query: " .
                                    mysql_error() . "</P>");
                                    exit();
                             }

                            while ( $row = mysql_fetch_array($result) ) {

							print ("<h3>" . $row["Title"] . "</h3>"); 
                            print ("<p><strong>" . $row["PressDate"] . "</strong></p>");
							print ($row["Copy"]);
							  }
							}		
							else {
							$result = mysql_query("SELECT DISTINCT ID, Title, PressDate, Copy  FROM press WHERE ID = $ID");

                            if (!$result) {

                            print ("<P>Error performing query: " .
                                    mysql_error() . "</P>");
                                    exit();
                             }

                            while ( $row = mysql_fetch_array($result) ) {

							print ("<h3>" . $row["Title"] . "</h3>"); 
                            print ("<p><strong>" . $row["PressDate"] . "</strong></p>");
							print ($row["Copy"]);
							  }

							}
							?>

http://www.len.org.uk/press/index.php is where the DATE_FORMAT code is not included, and as you can see the standard mysql date output is shown.

http://test.len.org.uk/press/index.php is where the DATE_FORMAT is included and it doesn't show the most recent press release.

Please note: I have added further dummy press releases to the test server in order to try to throw further light on what is happening.

Thanks in advance.

Richard

[steadyguy]

I think I had similar problem once ... Try putting the DATE_FORMAT() first.

SELECT
&nbsp;&nbsp;&nbsp;&nbsp;DATE_FORMAT(PressDate, '%e %M %Y') AS fDate,
&nbsp;&nbsp;&nbsp;&nbsp;DISTINCT(ID),
&nbsp;&nbsp;&nbsp;&nbsp;Title,
&nbsp;&nbsp;&nbsp;&nbsp;Copy
FROM press
ORDER BY fDate DESC
LIMIT 1;

[rkwinternet]

Hi Steadyman

I tried your suggestion, but no joy I'm afraid.

Any other suggestions - I've been going round in circles for hours!

Richard

[steadyguy]

Alright then ... what about putting the DISTINCT(ID) first? I ran the query on a test table here, and that part generated an error until I put that bit first ...

[rkwinternet]

Tried putting DISTINCT (ID), first and still no joy. 🙁

Richard

[rkwinternet]

To avoid confusion, a press release page IS displaying - it's just that it's the 07 March 2003 press release and not the Test 3 one that's dated 14 May 2003 i.e. the one with the most recent date.

In fact, it's not even displaying the oldest one, which I could understand if I had mixed up the ORDER BY ASC/DESC.

Richard