1

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".

  • Please stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the [deprecation process](http://news.php.net/php.internals/53799). Instead you should learn about prepared statements and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you care to learn, [here is a quite good PDO-related tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). – DCoder Sep 21 '12 at 12:12
  • Okay, I'll keep in mind your suggestion, It's just that I'm new to php. –  Sep 21 '12 at 12:17
  • All the better reason to learn PDO or MySQLi ;) – Chris Forrence Sep 21 '12 at 12:19

5 Answers5

3

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;
Manolis Agkopian
  • 1,033
  • 13
  • 22
1

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.

JvdBerg
  • 21,777
  • 8
  • 38
  • 55
  • I kow that but I already have a field called "updated" for future updates. So I did this to keep the original date of creation. And CURRENT_TIMESTAMP can only be used once. –  Sep 21 '12 at 12:23
  • That is true. When I need to update a timestamp with PHP I use date( 'Y-m-d H:i:s' ); – JvdBerg Sep 21 '12 at 12:26
  • @peterburg Hang on; 'updated' uses current_timestamp as a default? – Chris Forrence Sep 21 '12 at 12:28
  • Yes !that's why I can't use current_timestamp with created . –  Sep 21 '12 at 13:14
  • That's probably something you should add to your question @peterburg, since half of the answers so far involve altering your table – Chris Forrence Sep 21 '12 at 13:49
0

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

Prashant
  • 152
  • 1
  • 16
0

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())"
Chris Forrence
  • 10,042
  • 11
  • 48
  • 64
0

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.

Chris Forrence
  • 10,042
  • 11
  • 48
  • 64
  • I try it but I still get these annoying zeros. I really don't understand what is going on. NOTE : the "updated" field has type "TIMESTAMP", default="CURRENT_TIMESTAMP", attributs="on update CURRENT_TIMESTAMP" ;and the "created" field has type="datetime", default="none" and not null.This is frustrating! –  Sep 21 '12 at 16:47
  • Just...as a check, is there zeroes in the database table itself? Like, can you access it through phpMyAdmin or mysql-workbench and check, @peterburg? – Chris Forrence Sep 21 '12 at 18:06
  • Also, did using now() in place of the datetime work (my previous answer)? – Chris Forrence Sep 21 '12 at 18:06
  • Yes; there's zeros in phpmyadmin. I tried now() also, and it didn't work .i don't know if it's an error in the sprintf expression which I doubt. –  Sep 21 '12 at 18:10
  • Hrm. I mean, try echoing $insert_sql. I just verified that now() correctly inserts into a datetime. Can you run `echo mysql_errno($link) . ": " . mysql_error($link) . "\n";` after your mysql_query? – Chris Forrence Sep 21 '12 at 18:20
  • $link refers to what?! I noticed that even when I set the default value of 'created' to 'none' phpmyadmin shows "0000-00-00 00:00:00"!this is when I put the type of 'created' as 'timestamp' . –  Sep 21 '12 at 18:52
  • Whoops, $link isn't needed. I had copied and pasted from the PHP doc example. `echo mysql_errno() . ": " . mysql_error() . "\n";` – Chris Forrence Sep 21 '12 at 19:13