在mysql中显示创建行的日期

I added a new row to my users' table to show the date of creation of each user. I tried both timestamp and datetime types for that but it still displays me 0000-00-00 00:00:00 next to each user.

here's my code for adding the rows to the users's table

$first_name = trim($_REQUEST['first_name']);
$last_name = trim($_REQUEST['last_name']);
$username = trim($_REQUEST['username']);
$password = trim($_REQUEST['password']);
$email = trim($_REQUEST['email']);
$created = strftime("%Y-%m-%d %H:%M:%S", time());
$insert_sql = sprintf("INSERT INTO users (first_name, last_name, username, password, email, created)" .
"VALUES('%s', '%s', '%s', '%s', '%s', '%s');",
mysql_real_escape_string($first_name),
mysql_real_escape_string($last_name),
mysql_real_escape_string($username),
mysql_real_escape_string(crypt($password, $username)),
mysql_real_escape_string($email),
mysql_real_escape_string($created));
mysql_query($insert_sql) or die(mysql_error());

And here's the code that displays the informations

$select_users = "SELECT * FROM users";
$result =  mysql_query($select_users);
while ($user = mysql_fetch_array($result)) {
$user_row = sprintf(
"<td><input type='checkbox' name='checkbox[]' id='checkbox[]' value='%d'/></td>" .
"<td>%s %s</td> " .
"<td>%s</td>" .
"<td><a href='mailto:%s'>%s</a></td> " .
"<td>%s</td>" .
"<td><a href='javascript:delete_user(%d);'><img class='delete_user' src='images/trash.png' alt='' title='' border='0' /></a></td>",
$user['user_id'], $user['first_name'], $user['last_name'], $user[username],
$user['email'], $user['email'], $user['created'], $user['user_id']);
echo $user_row;

I got all the fields displayed correctly except for the creation date which shows 0000-00-00 00:00:00 . I don't know what I did wrong. Thank you for the help.

EDIT : I already have a field called "updated" which uses "Current_timestamp".

try $created = date("m-d-y H:i:s", time());

You can actually add the current time within MySQL without reusing current_timestamp. Try this as your insertion query; it doesn't involve any database schema changes, and it should still get you the correct result:

$query = "INSERT INTO users (first_name, last_name, username, password, email, created)"
. "VALUES('%s', '%s', '%s', '%s', '%s', now())"

Instead of setting the time with php, you could make a field in the database named created with type of timestamp and set default value to CURRENT_TIMESTAMP. This will automaticly do what you need without php. Here is the code:

    ALTER TABLE `users` ADD COLUMN `created` timestamp DEFAULT CURRENT_TIMESTAMP;

MySQL has the possibility to have a timestamp on a field that is inserted when the record is created.

In your SQL put this for the created:

`Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

You do not need any PHP code to handle the timestamp.

A potential workaround is that since you know the value of $created, you don't really need to escape it. Therefore, your $insert_sql would be:

$insert_sql = sprintf("INSERT INTO users (first_name, last_name, username, password, email, created)"
. "VALUES('%s', '%s', '%s', '%s', '%s', '".$created."');",
mysql_real_escape_string($first_name),
mysql_real_escape_string($last_name),
mysql_real_escape_string($username),
mysql_real_escape_string(crypt($password, $username)),
mysql_real_escape_string($email));

Note that this is ONLY ONLY ONLY when you absolutely control everything about the variable.