Formatting timestamp as UK date gives today's date only

[mkultron]

Hi guys, I'm putting together a small event system where I want the user to add his own date and time into a textfield (I'll probably make this a series of drop-downs/a date picker later).

This is then stored as a timestamp - "0000-00-00 00:00:00" which displays fine until I try to echo it out as a UK date in this format - jS F Y, which just gives today's date but not the inputted date.

 

 

 

Here's the code I have right now:

 

$result = mysql_query("SELECT * FROM stuff.events ORDER BY eventdate ASC");

echo "<br />";
echo mysql_result($result, $i, 'eventvenue');
echo ", ";

$dt = new DateTime($eventdate);

echo $dt->format("jS F Y"); 

 

 

 

In my mysql table eventdate is set up as follows:

 

field - eventdate

type - timestamp

length/values - blank

default - current_timestamp

collation - blank

attributes - on update CURRENT_TIMESTAMP

null - blank

auto_increment - blank

 

 

Any help as to why this could be happening would be much appreciated, thanks.

[trq]

"0000-00-00 00:00:00" is not a timestamp.

[mkultron]

I changed it to a datetime and it's just giving today's date.

[Pikachu2000]

If you're storing a fixed event date, chances are you don't want the ON UPDATE CURRENT TIMESTAMP attribute for that field.

[mkultron]

It's not anymore, it's just stored as a datetime. This should be such a simple thing but I can't seem to find a solution anywhere.

[kickstart]

Hi

 

Where are you assigning a value to $eventdate?

 

All the best

 

Keith

[mkultron]

$eventdate should be the eventdate field in from my database table, I've posted all the code I have right now above, do I need to do something like:

 

$eventdate = $row["eventdate"];

 

Thanks in advance

MK

[kickstart]

Hi

 

Yes, if you were bringing back an associative array of the row. Or to continue with how you have done it:-

 

$eventdate = mysql_result($result, $i, '$eventdate');

 

(assuming $i is set up as the row number you want to retrieve)

 

All the best

 

Keith

[mkultron]

Thanks kickstart, I now have the following set up, but still with only the venue name and today's date:

 

        include('database-connect.php');

	$result = mysql_query("SELECT * FROM stuff.eventstest ORDER BY eventdate ASC");

	$eventdate = mysql_result($result, $i, '$eventdate');

                echo "<br />";
                echo mysql_result($result, $i, 'eventvenue');
			echo ", ";

			$dt = new DateTime($eventdate);
			echo $dt->format("jS F Y");

			echo "<br />";  

[kickstart]

Hi

 

Can you echo out $eventdate (unformatted) to check it is a useful value.

 

All the best

 

Keith

[mkultron]

Cool, I've edited as suggested:

 

<?php
        // connect to the database
        include('database-connect.php');

	$result = mysql_query("SELECT * FROM stuff.eventstest ORDER BY eventdate ASC");

	$eventdate = mysql_result($result, $i, '$eventdate');

                echo mysql_result($result, $i, 'eventvenue');
	echo mysql_result($result, $i, 'eventdate');
?>

 

And the value I return is "2012-02-08 10:57:52" (what I wanted, but obviously unformatted).

 

Thanks

MK

[kickstart]

Hi

 

Not sure then as that should work.

 

Although you appear to have an extra $ sign when you try and extract the field from mysql (ie, you want the column  eventdate rather than $eventdate), but I would expect that to stop your echo from working as well.

 

All the best

 

Keith

[Andy-H]

Nah, you're right, he echo'ed out the return value of  mysql_result($result, $i, 'eventdate'); but his variable was set to  mysql_result($result, $i, '$eventdate');

[mkultron]

That works perfectly, thank you so much, kickstart !