Jump to content


Photo

Strange SELECT result


  • Please log in to reply
4 replies to this topic

#1 nfr

nfr
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 06 June 2006 - 09:30 PM

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.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 06 June 2006 - 10:36 PM

Formatted dates usually default to today's date when the date field being formatted is empty.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 nfr

nfr
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 06 June 2006 - 11:08 PM


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.


#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 06 June 2006 - 11:16 PM

Are you sure $article_id contains "5000"?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 nfr

nfr
  • Members
  • PipPipPip
  • Advanced Member
  • 34 posts

Posted 06 June 2006 - 11:23 PM


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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users