Php timestamp date

[RazorRamon]

I built a quick date time test page. The coding is below followed by its results. I'm entering the raw timestamp into table time because i want to be able to manipulate it once i show it (say if i want to show time and date using the same timestamp). Let me know what i'm doing wrong. Thanks

datetest.php

include 'config2.php';
$dbname = 'time';
$tablename = 'time';
include 'opendb.php';

$name=$_POST['name'];
$date= date("g:ia");
$timestamp=time();


mysql_query ("INSERT INTO $tablename (timestamp,name) 
VALUES ('$timestamp','$name')"); 

$res = mysql_query("SELECT timestamp, name FROM time ORDER BY timestamp DESC;");


while ( $row = mysql_fetch_array($res) ) {
$timestamp = strtotime($row["timestamp"]);
	echo $row["name"] . "<br />";
	echo $row["timestamp"] . "<br />";
	echo date("g:i a F j, Y ", $timestamp) . "<br /><br />";
}

Here are the results. Why does it keep showing me the the same date?

john
1311821078
12:00 am January 1, 1970 

toni
1311820715
12:00 am January 1, 1970 

rafmon
1311820431
12:00 am January 1, 1970

[urtrivedi]

As far as I know in mysql by default datatime field is stored in
yyyy-mm-dd format. Though if data is in another format then you may use mysql strtodate (some thing like that) function.

I suggest you to directly use mysql current_timestamp function

mysql_query ("INSERT INTO $tablename (timestamp,name) VALUES (current_timestamp,'$name')");

Note: you should also avoid using keywords as column or table name. Here you may change timestamp column to my_time or any other

[RazorRamon]

i tried storing time as int, varchar, and datetime. Datetime seems to be the most strict. I want to be able to convert to month name. Question, why should i change the keywords? i'm sure its for security purposes but can you elaborate?

[urtrivedi]

When you are dealing with date and time, always use datatime datatype. Do not use varchar. You will always able to extract data by month, year, week, day whatever you want using certain functions.

I said anywhere you should use some names for columns and tables other than keyword like select, group, from, order, timestamp, date etc.

It is not related to security, but some time certain queries do not work just due to such colnames.
Though it is not mandatory. In mysql You may wrap such names with ``.
like `timestamp`

[RazorRamon]

ok i got it to where it shows me this.

juan
1311871968
4:52 pm July 28, 2011 

george
1311871950
4:52 pm July 28, 2011 

george
1311871488
4:44 pm July 28, 2011

I haven't changed the query to have quotes but this is how i changed it. Now my issue is how do I make it to where it shows the users time zone? Or show how long ago the comment was made?(like on facebook where it shows comment was made 2 hours ago,5 min ago, etc...)

Heres the code.

include 'config2.php';
$dbname = 'time';
$tablename = 'time';
include 'opendb.php';

$name=$_POST['name'];
$date= date("g:ia");
$timestamp=time();


mysql_query ("INSERT INTO $tablename (timestamp,name) 
VALUES ('$timestamp','$name')"); 

$res = mysql_query("SELECT timestamp, name FROM time ORDER BY timestamp DESC LIMIT 0,10;");


while ( $row = mysql_fetch_array($res) ) {
	echo $row["name"] . "<br />";
	echo $row["timestamp"] . "<br />";
	echo date("g:i a F j, Y ",  $row["timestamp"]) . "<br /><br />";
}