mktime() into mysql datetime column?

Question

I don't know if it's possible, but I'm trying to assign different dates to each user as the are entered into the database using a loop (I know that bit's possible). To do this I'm building the timestamp using mktime() but my column is datetime (for the purpose of MySQL ORDERBY timestamp DESC) and, naturally, the two don't go together.

My code:

<?php
    foreach($arr as $user_uid => $num) {
      $i = 1;
        while($num > 0) {
          $i++;
          $t = new DateTime("Y-m-d H:i:s", date(mktime(0, 0, 0, $i, 1, 2012)));
          $num--;
        }
     }
?>

At the moment this returns: 0000-00-00 00:00:00.

Any help would be appreciated, thanks!

EDIT: An amount has changed, my code now reads like this:

foreach($arr as $user_uid => $num) {
    $i = 1;
        while($num > 0) {
            $i++;
            $t = date('Y-m-d H:i:s', mktime(0, 0, 0, $i, 1, 2012));
            $num--;
        }

$game = "INSERT INTO wd_game_$gid (game_uid,user_uid,lastmove,startcountry,money) VALUES ('$gid','$user_uid',FROM_UNIXTIME('$t'),'$rand_c','$money')";

This now inserts: 1970-01-01 00:00:00

Answer 1

Solution 1 - use FROM_UNIXTIME() on databse layer

You can do this on the database side, using FROM_UNIXTIME() function (the argument is Unix epoch timestamp, so this is the same as the result of your mktime()).

Solution 2 - fix your current code

Also your code is incorrect, because you pass incorrect date into DateTime constructor (see the documentation). You pass "Y-m-d H:i:s" instead of date('Y-m-d H:i:s', mktime(0, 0, 0, $i, 1, 2012)). You can even resign from using DateTime and just stick to using date('Y-m-d H:i:s', mktime(0, 0, 0, $i, 1, 2012)), as this is sufficient for your database insert.

Answer 2

I don't see why you have to pass to FROM_UNIXTIME function the string representation of the date since you are already using DATETIME datatype. FROM_UNIXTIME parameter is unsigned int, thus the evaluation results 1970 as the year which is the equivalent of the start of the epoch

foreach($arr as $user_uid => $num) {
$i = 1;
    while($num > 0) {
        $i++;
        $t = date('Y-m-d H:i:s', mktime(0, 0, 0, $i, 1, 2012));
        $num--;
    }

$game = "INSERT INTO wd_game_$gid (game_uid,user_uid,lastmove,startcountry,money) VALUES      ('$gid','$user_uid','$t','$rand_c','$money')";

Answer 3

Should be

$t = new DateTime(date("Y-m-d H:i:s", mktime(0, 0, 0, $i, 1, 2012)));

(The format string is the first parameter of date)

---

EDIT: As I've said in the comments, don't confuse php DateTime class with MySQL DATETIME data type.

You can do this:

$t = mktime(0, 0, 0, $i, 1, 2012);  // $t contains now a unix timestamp

// ... 

$game = "INSERT INTO wd_game_$gid (game_uid,user_uid,lastmove,startcountry,money) VALUES ".
        "('$gid','$user_uid',FROM_UNIXTIME($t),'$rand_c','$money')";

Or this:

$t = date("Y-m-d H:i:s", mktime(0, 0, 0, $i, 1, 2012));
// $t contains a textual representation of MySQL DATETIME value

// ... 

$game = "INSERT INTO wd_game_$gid (game_uid,user_uid,lastmove,startcountry,money) VALUES ".
        "('$gid','$user_uid','$t','$rand_c','$money')";

Answer 4

You're approach isn't quite right I don't think. Try:

foreach($arr as $user_uid => $num) {
  $i = 1;
    while($num > 0) {
      $i++;
      $t = date("Y-m-d H:i:s", mktime(0, 0, 0, $i, 1, 2012));
      $num--;
    }
 }

The DateTime() constructor has two optional parameters. A string representing the time and a string representing the time zone - see the DateTime __construct reference