Another one of those time/date questions!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Ross
Forum Newbie
Posts: 9
Joined: Thu Feb 16, 2006 1:14 pm

Another one of those time/date questions!

Post 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.";
		
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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']
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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"])),
?>
Post Reply