Date not formating correctly

[Cheez]

Hello everyone,

I'm extracting a query from my mySQL database for a news article on my site, but when I extract it, the date is set to "Wednesday, December 31st, 1969" instead of the date extracted from the mySQL (the format is correct, however). Here is my php code:

<?php

include "global_header.php";
include "db_open.php";

$id = $_GET['id'];

$query = "SELECT * FROM `news_articles` WHERE `id` = '$id' ";
$result = mysql_query($query);

while ($array = mysql_fetch_array($result)) {
	$title = $array['news_title'];
	$blurb = $array['news_short'];
	$image = $array['news_image'];
	$story = $array['news_full'];		
	$date = $array['news_date'];
	$authid = $array['news_auth'];	

		$query = "SELECT * FROM `forums_members` WHERE `ID_MEMBER` = '$authid' ";
		$authrslt = mysql_query($query);	
		while ($autharray = mysql_fetch_array($authrslt)) {
			$author = $autharray['memberName'];	
		}

?>

	  	<table width="575" border="0" cellspacing="0" cellpadding="1">
          <tr>
            <td><span class="largetext style4"><strong>

			<?php
			echo $title;
			?>

            <br /></strong><span class="nav"><em>Posted on 

			<?php
			$dateext = date("l, F jS, Y",$date);
			echo $dateext . " ";
			?>

			by

			<?php
			echo $author;
			?>

			</em></span></span></td>
          </tr>
          <tr>
            <td height="1" bgcolor="#000000"></td>
          </tr>
          <tr>
            <td><span class="style1"><img src="images/news/

			<?php
			echo $image;
			?>

			.gif" width="150" height="150" hspace="10" vspace="10" border="0" align="right" />

			<?php
			echo $story;
			?>

			</span></td>
          </tr>
        </table>

<?php

}

include "global_footer.php";

?>

Any help would be greatly appreciated.

Thanks,

-Reality15

[bradgrafelman]

1. What happens if you just echo $date without using PHP's date() function? What type of value do you see?

2. Using "SELECT *" is definitely not a good practise. Instead, you should be selecting the columns you intend to work with, e.g. "SELECT column1, column2, column3 FROM ..." even if you plan to use all columns that are (currently) in the table.

3. After you've fixed your query with the suggestion in point #2, you can replace news_date with MySQL's DATE_FORMAT() function (it works very similarly to PHP's date() function) only if you were smart and used some sort of date/time column type for this field.

4. Why have two separate queries? A simple join would probably work here:

   SELECT `news_title`, `news_short`, `news_image`, `news_full`, DATE_FORMAT(`news_date`, 'whatever you want'), `news_author`
   FROM `news_articles` JOIN `forums_members`
   ON `news_articles`.`news_auth` = `forums_members`.`ID_MEMBER`
   WHERE `news_articles`.`id` = $id

   Couple of things to note about my example query:
   --- 1) I didn't select the author id, since you don't seem to use it. If you do plan on using it, you'll have to add it to the column list.
   --- 2) I removed the quotes around the news article ID, as it sounds like id holds numerical values and should thus be a numerical column. If not... why isn't it?
   --- 3) You should probably verify that $_GET['id'] exists before you start querying the DB and displaying results... both of which are dependent on an "id" value being specified.
   --- 4) You are vulnerable to MySQL injection attacks. Never take user-supplied data and put it in a querying string. ALWAYS run this data through a function such as [man]msyql_real_escape_string/man (or, at the very least, [man]addslashes/man). Since you are expecting a numerical value here, you might even ditch both of those functions for something like [man]intval/man. That way, you're a) stripping out the characters that make SQL attacks possible, and b) ensuring that you have a numerical value to use in your query.

EDIT: Well, I don't know why I even asked the question posed in point #1, because by the time I got through #4 I had already told you a better/different way of doing this, so point #1 might be a moot point. Then again, it's a helpful debugging tip, so I think I'll leave it there.

[Cheez]

Alright, so I changed the query and extraction a little bit according to your changes, but now it won't even display a date... here's the PHP code:

<?php

include "global_header.php";
include "db_open.php";

$id = $_GET['id'];

$query = "SELECT `news_title`, `news_short`, `news_image`, `news_full`, DATE_FORMAT(`news_date`, '%M %e %Y'), `memberName` FROM `news_articles` JOIN `forums_members` ON `news_articles`.`news_auth` = `forums_members`.`ID_MEMBER` WHERE `news_articles`.`id` = $id";
$result = mysql_query($query);

while ($array = mysql_fetch_array($result)) {
	$title = $array['news_title'];
	$blurb = $array['news_short'];
	$image = $array['news_image'];
	$story = $array['news_full'];		
	$date = $array['news_date'];	
	$author = $array['memberName'];

?>

	  	<table width="575" border="0" cellspacing="0" cellpadding="1">
          <tr>
            <td><span class="largetext style4"><strong>

			<?php
			echo $title;
			?>

            <br /></strong><span class="nav"><em>Posted on 

			<?php
			echo $date;
			?>

			by

			<?php
			echo $author;
			?>

			</em></span></span></td>
          </tr>
          <tr>
            <td height="1" bgcolor="#000000"></td>
          </tr>
          <tr>
            <td><span class="style1"><img src="images/news/

			<?php
			echo $image;
			?>

			.gif" width="150" height="150" hspace="10" vspace="10" border="0" align="right" />

			<?php
			echo $story;
			?>

			</span></td>
          </tr>
        </table>

<?php

}

include "global_footer.php";

?>

Thanks for the help,

-Jon

[bradgrafelman]

Try adding a column alias so that you can still use your $date variable:

... DATE_FORMAT(`news_date`, '%M %e %Y') AS news_date ...