Strange SELECT result

[nfr]

Hello -

I have 2 select statements:

1.) select DATE_FORMAT(article_creation_date, '%d/%m/%Y') from t_article_details where article_id = 5000;

2.) SELECT article.article_edition_number, article.article_id, article.article_title, article.article_author_id, author.article_author_name, author.article_author_email, article_text, DATE_FORMAT(article.article_publication_date, '%d/%m/%Y') as article_publication_date, DATE_FORMAT(article.article_creation_date, '%d/%m/%Y') as article_creation_date, article.article_summary FROM t_article_details as article, t_article_author_details as author WHERE ((article_id = '$article_id') and (article.article_author_id = author.article_author_id));

The first gives the desired result: 27/05/2005

+------------------------------------------------+
| DATE_FORMAT(article_creation_date, '%d/%m/%Y') |
+------------------------------------------------+
| 25/07/2005 |
+------------------------------------------------+
1 row in set (0.00 sec)

The second gives an incorrect result: 06/06/2006

$article_creation_date = $row_RS_article_details['article_creation_date'];
echo "article_creation_date: $article_creation_date";

Can someone please explain why?!? It should be the same result in both cases, surely...

Regards,

Neil.

[Barand]

Formatted dates usually default to today's date when the date field being formatted is empty.

[nfr]

That would make sense... But the field has a value... So if I have the following statement:

SELECT article.article_edition_number, article.article_id, article.article_title, article.article_author_id, author.article_author_name, author.article_author_email, article_text, DATE_FORMAT(article.article_publication_date, '%d/%m/%Y') as article_publication_date, DATE_FORMAT(article.article_creation_date, '%d/%m/%Y') as article_creation_date, article.article_summary FROM t_article_details as article, t_article_author_details as author WHERE ((article_id = '$article_id') and (article.article_author_id = author.article_author_id));

... how do I assign "DATE_FORMAT(article.article_creation_date, '%d/%m/%Y') as article_creation_date" to a variable? I thought that I had done this with:

$article_creation_date = $row_RS_article_details['article_creation_date'];
echo "article_creation_date: $article_creation_date";

Regards,

Neil.

[Barand]

Are you sure $article_id contains "5000"?

[nfr]

Direct selects from the database:

mysql> select article_creation_date from t_article_details where article_id = 5000;
+-----------------------+
| article_creation_date |
+-----------------------+
| 20050725133459 |
+-----------------------+
1 row in set (0.05 sec)

mysql> select DATE_FORMAT(article_creation_date, '%d/%m/%Y') from t_article_details where article_id = 5000;
+------------------------------------------------+
| DATE_FORMAT(article_creation_date, '%d/%m/%Y') |
+------------------------------------------------+
| 25/07/2005 |
+------------------------------------------------+
1 row in set (0.01 sec)

Here, I asign it to a variable:

mysql_select_db($database_db, $db);
$query_RS_article_details = "SELECT article.article_edition_number, article.article_id, article.article_title, article.article_author_id, author.article_author_name, author.article_author_email, article_text, DATE_FORMAT(article.article_publication_date, '%d/%m/%Y') as article_publication_date, DATE_FORMAT(article.article_creation_date, '%d/%m/%Y') as article_creation_date, article.article_summary FROM t_article_details as article, t_article_author_details as author WHERE ((article_id = '$article_id') and (article.article_author_id = author.article_author_id))";
$RS_article_details = mysql_query($query_RS_article_details, $leaderdb) or die(mysql_error());
$row_RS_article_details = mysql_fetch_assoc($RS_article_details);
$totalRows_RS_article_details = mysql_num_rows($RS_article_details);
$article_creation_date = $row_RS_article_details['article_creation_date'];
$article_publication_date = $row_RS_article_details['article_publication_date'];
$article_modification_date = $row_RS_article_details['article_modification_date'];

echo "article_creation_date: $article_creation_date";
echo "article_publication_date: $article_publication_date";
echo "article_modification_date: $article_modification_date";

The output:

article_creation_date: 07/06/2006article_publication_date: 11/04/2020article_modification_date:

Any ideas?

Regards,

Neil.