Page 1 of 1

Another one of those time/date questions!

Posted: Thu Feb 16, 2006 1:40 pm
by Ross
Hi,

I'm working on a script to learn a little about programming in php/mysql. I am trying to output mySQL table entries to a HTML table. When I go to print the date to the HTML table, everything works. But, when I try to format the date from the "yyyy-mm-dd" mySQL date to a different format ("mm-dd-yy"), I am unable. The output is the same for every date: "12-31-69".

My question is two parts. First, is it possible to get this php date() function working correctly? If so, could someone please show me where I'm going wrong with it? And Secondly, is it also possible to use the mySQL command DATE_FORMAT to achieve the same results? If so, could someone please show my how that would be implemented in my code. I will include the whole script below, Thanks for the help:

Note: Most of these comments are for me :)

Code: Select all

<?php
 	
	//Log-in to Database
	$db = mysql_connect("localhost", "","");
	//Connect to Database
	mysql_select_db("mydb", $db);
	
	$query = "SELECT * FROM events ORDER BY eventDate ASC";
	$result = mysql_query($query, $db);
	
	if($row = mysql_fetch_array($result))
	{
		//We use this variable to specify the class of the table row
		$rowClass = "odd";
		//Make a new HTML table row for the db_row[0] (that we just called)
		printf("
				<tr class = \"%s\">
					<td> %s </td>
					<td> %s </td>
					<td> %s </td>
					<td> %s </td>
					<td> %s </td>
				</tr>",
				$rowClass,
				$row["eventName"],
				$row["eventBand"],
				date("m-d-y", $row["eventDate"]), //This is where i'm going wrong
				$row["eventTime"],
				$row["eventDescription"]);
				
		while($row = mysql_fetch_array($result))
		{
			if($rowClass = "odd")
				$rowClass = "even";
			else if($rowClass = "even")
				$rowClass = "odd";
				
			printf("
					<tr class = \"%s\">
						<td> %s </td>
						<td> %s </td>
						<td> %s </td>
						<td> %s </td>
						<td> %s </td>
					</tr>",
					$rowClass,
					$row["eventName"],
					$row["eventBand"],
					date('m-d-y', $row["eventDate"]), //This is also where I'm going wrong
					$row["eventTime"],
					$row["eventDescription"]);
		}
	}
	else
		echo "Sorry, no database entries at this time.";
		
?>

Posted: Thu Feb 16, 2006 2:11 pm
by feyd

Posted: Thu Feb 16, 2006 3:41 pm
by pickle
'12-31-69' is the date of the unix epoch. Passing anything less than '0' will get you the same result. So, either $row['eventDate'] doesn't exist, or more likely it's a string. Try this in your query:

Code: Select all

SELECT
  UNIX_TIMESTAMP(eventDate) as 'stamp'
  *
..blah blah blah
Then reference it with $row['stamp']

Posted: Thu Feb 16, 2006 4:18 pm
by RobertGonzalez
Change this...

Code: Select all

<?php
date('m-d-y', $row["eventDate"]),
?>
... to this ...

Code: Select all

<?php
date('m-d-y', strtotime($row["eventDate"])),
?>