MySQL NOW() in Doctrine DBAL insert?

Question

I'm staring at http://doctrine-dbal.readthedocs.org/en/latest/reference/data-retrieval-and-manipulation.html#insert

I'd love to be able to do something like

$db->insert('mytable', [
  'foo' => 'bar',
  'created_on' => new MagicThatMakesNowWork()
]);

Is this impossible? The best solution I've seen is using PHP to get the datetime while setting the timezone, which is less than ideal. For some reason it seems only the ORM or query builder can handle expressions. I know in Zend I can do something like new Zend_Db_Expr('NOW()') and it knows by the object type not to quote NOW() in the built query. No query builder or ORM required.

Not sure if it's not possible or not documented well. The second answer on Doctrine DBAL: Updating timestamp field with 'NOW()' value shows a random string of datetime in the types array which seems weird/bad as well.

You should try Doctrine TimeStampable... https://github.com/l3pp4rd/DoctrineExtensions/blob/master/doc/timestampable.md — Alessandro Lai, May 07 '15 at 07:43
@zerkms I did try that also with no luck. I saw a few places thought that would work. — Dave Stein, May 07 '15 at 20:08
Mh maybe, I use it in a Symfony project, so I have the full Doctrine package — Alessandro Lai, May 07 '15 at 20:16
I'm using Silex and working on getting it to the latest version of DBAL which gives insert to query builder which would let me do something I can put as an answer here — Dave Stein, May 07 '15 at 23:38

score 1 · Answer 1 · answered May 08 '15 at 11:22

1

it seems you're trying to accomplish what the TIMESTAMP column type does automatically. That's been around for way longer than 2014. Use it, and stop trying to manage your created_on and updated_on fields manually. https://dev.mysql.com/doc/refman/5.1/en/datetime.html

answered May 08 '15 at 11:22

Mike Sherov

13,277
8
41
62

Fair answer, but was more curious about expressions - it just so happens this case was for NOW(). On the other hand I don't know the answer to using DBAL to do COLUMN_NAME+1... now I'm gonna see if I can do that. Like votes=votes+1 – Dave Stein May 09 '15 at 03:19

Dean · Answer 2 · 2017-10-15T14:22:17.750

A late answer but the easiest way I've found to do this is to run a query first to get the value of NOW() and then insert that string.

An example:

$now = $db->fetchColumn("SELECT NOW()");
$db->insert('table', array(
    'field1' => $field1
    ,'created' => $now
));

A slightly less desirable alternative would be if you are able to make sure you PHP/MySQL times are in sync, you could always create the DateTime object in PHP and format it as a string ready for MySQL:

$created = new \Datetime('now', new \DateTimeZone('Europe/Paris'));
$created->format('Y-m-d H:i:s')

score 0 · Answer 3 · edited May 23 '17 at 12:26

insert isn't much of an option. So the answer at Expression mysql NOW() in Doctrine QueryBuilder shows manually knowing what's coming for that variable.

This requires DBAL >= 2.5 which was recently released. You'd be out of luck in 2014. Note that datetime is a valid string, but doesn't appear to have a constant http://php.net/manual/en/pdo.constants.php.

public function insert($params) {

  if (!isset($params[static::CREATED_ON])) {
    $params[] = [static::CREATED_ON, new \Datetime('now'), 'datetime'];
  }

  if (!isset($params[static::MODIFIED_ON])) {
    $params[] = [static::MODIFIED_ON, new \Datetime('now'), 'datetime'];
  }

  $conn = $this->_dbs['write'];
  $query_builder = $conn->createQueryBuilder();

  $query_builder->insert(static::TABLE);

  foreach ( $params as $location => $data ) {

    $field = $data[0];
    $value = $data[1];
    $type = (isset($data[2])) ? $data[2] : \PDO::PARAM_STR;
    $prep = (isset($data[3])) ? $data[3] : '?';

    $query_builder
      ->setValue($field, $prep)
      ->setParameter($location, $value, $type);

  }

  $return = $query_builder->execute();

  if (!$return) {
    throw new \Exception("Failed to execute statement");
  }

  return $conn->lastInsertId();

}

public function update($params, $where) {

  $conn = $this->_dbs['write'];
  $query_builder = $conn->createQueryBuilder();

  $query_builder->update(static::TABLE);

  foreach ( $params as $location => $data ) {
    // can do arbitrary expressions via ['field', null, null, 'ANY EXPRESSION YOU WANT']
    // ie ['votes', null, null,'votes=votes+25']
    // or ['votes', '1', \PDO::PARAM_INT, 'votes + ?']
    $field = $data[0];
    $value = $data[1];
    $type = (isset($data[2])) ? $data[2] : \PDO::PARAM_STR;
    $prep = (isset($data[3])) ? $data[3] : '?';

    $query_builder
      ->set($field, $prep)
      ->setParameter($location, $value, $type);

  }

  $query_builder->where($where);

  return $query_builder->execute();

}

MySQL NOW() in Doctrine DBAL insert?

3 Answers3