日期时间现在 PHP mysql（+ PDO 变体）

Datetime NOW PHP mysql (+ PDO variant)

提问by Chris

Thanks for looking. All helpful answers/comments are up voted.

谢谢你看。所有有用的答案/评论都被投票。

In php, you can use NOW() like this:

在 php 中，你可以像这样使用 NOW()：

mysql_query("INSERT INTO tablename (id,      value,      time_created) 
                            VALUES ('{$id}', '{$value}', NOW())");

How can I do the same thing in PDO. When I bind like this, I get an error:

我怎样才能在 PDO 中做同样的事情。当我这样绑定时，出现错误：

$stmt->bindParam(':time_added', NOW(), PDO::PARAM_STR);

Is it the PDO:PARAM_STR?

是 PDO:PARAM_STR 吗？

回答by Stefan Gehrig

Because nobody has explicitly answered the question, I'll add the correct answer for the sake of completeness.

因为没有人明确回答这个问题，为了完整起见，我将添加正确的答案。

$stmt = $pdoDb->prepare('INSERT INTO tablename (id, value, time_created) VALUES (:id, :value, NOW())');
// either bind each parameter explicitly 
$stmt->bindParam(':id', $id); // PDOStatement::bindValue() is also possibly
$stmt->bindParam(':value', $value);
$stmt->execute();
// or bind when executing the statement
$stmt->execute(array(
    ':id'    => $id,
    ':value' => $value
));

回答by Kenneth Vogt

Presuming your PDO statement is correct you could do something like this:

假设您的 PDO 声明是正确的，您可以执行以下操作：

$date = date('Y-m-d H:i:s');
$stmt->bindParam(':time_added', $date, PDO::PARAM_STR);

回答by B.F.U.

None of the answers solve the question as I see it!

没有一个答案能解决我所看到的问题！

So there are some of my findings: there is NO WAY how to force PDO to pass MySQL function call as a query value - so there is no way to do simple wrapper that will be able to use NOW() or any other function as passed values. Every time you need something like that, you need manually change the query, so the function call is part of the query string. :-(

所以有一些我的发现：没有办法如何强制 PDO 将 MySQL 函数调用作为查询值传递 - 所以没有办法做能够使用 NOW() 或任何其他函数的简单包装器值。每次您需要类似的东西时，您都需要手动更改查询，因此函数调用是查询字符串的一部分。:-(

I'm using function that tests given values for MySQL function I am using and modifies the query itself, but it is not a good solution to my opinion... :-}

我正在使用测试 MySQL 函数的给定值的函数我正在使用并修改查询本身，但这对我的意见来说不是一个好的解决方案...... :-}

回答by Chris Tailor

This might be useful to some of you, maybe not. I was confronted with the same problem as Ollie Saunders was. I'm pretty new to php/mysql, and most of all PDO. I was able to solve the problem with the following:

这可能对你们中的一些人有用，也可能没有。我遇到了和 Ollie Saunders 一样的问题。我是 php/mysql 的新手，最重要的是 PDO。我能够通过以下方式解决问题：

$active = 0;      
$id = NULL;
$query = "INSERT 
        INTO tbl_user(ID_user, firstname, lastname, email, password, active, create_date)
        VALUES (?,?,?,?,?,?,NOW())";

if($stmt=$this->conn->prepare($query)) {
$stmt->bind_param('issssi', $id, $firstname, $lastname, $email, $password, $active);
$stmt->execute();
}

$active = 0;      
$id = NULL;
$query = "INSERT 
        INTO tbl_user(ID_user, firstname, lastname, email, password, active, create_date)
        VALUES (?,?,?,?,?,?,NOW())";

if($stmt=$this->conn->prepare($query)) {
$stmt->bind_param('issssi', $id, $firstname, $lastname, $email, $password, $active);
$stmt->execute();
}

and guess what it works! Hope to have helped here. Any comments are welcome. Try it and tell me if it worked for you, or if you have any additions.

猜猜它有什么作用！希望在这里有所帮助。欢迎提出任何意见。试试看，告诉我它是否适合你，或者你有什么补充。

回答by Sabeen Malik

other than NOW() i also utilize the "timestamp" type column and set its default to CURRENT_TIMESTAMP .. so i just pass nothing for that field and time is automatically set. maybe not exactly what ur looking for.

除了 NOW() 我还利用“时间戳”类型列并将其默认设置为 CURRENT_TIMESTAMP ..所以我只为该字段传递任何内容，时间会自动设置。也许不是你想要的。

回答by Andrew Klaassen

To answer Elmo's question, you cancreate a PDO wrapper that allows for SQL functions like NOW(). You just need to pass an additional argument with the columns that you want to use SQL functions for. Here's mine:

要回答 Elmo 的问题，您可以创建一个 PDO 包装器，允许使用 NOW() 等 SQL 函数。您只需要为要使用 SQL 函数的列传递一个附加参数。这是我的：

function pInsertFunc($action, $table, $values, $sqlfunctions)
{

    global $pdb;

    // There's no way to pass an SQL function like "NOW()" as a PDO parameter,
    // so this function builds the query string with those functions.  $values
    // and $sqlfunctions should be key => value arrays, with column names
    // as keys.  The $values values will be passed in as parameters, and the
    // $sqlfunction values will be made part of the query string.

    $value_columns = array_keys($values);
    $sqlfunc_columns = array_keys($sqlfunctions);
    $columns = array_merge($value_columns, $sqlfunc_columns);

    // Only $values become ':paramname' PDO parameters.
    $value_parameters = array_map(function($col) {return (':' . $col);}, $value_columns);
    // SQL functions go straight in as strings.
    $sqlfunc_parameters = array_values($sqlfunctions);
    $parameters = array_merge($value_parameters, $sqlfunc_parameters);

    $column_list = join(', ', $columns);
    $parameter_list = join(', ', $parameters);

    $query = "$action $table ($column_list) VALUES ($parameter_list)";

    $stmt = $pdb->prepare($query);
    $stmt->execute($values);

}

Use it like this:

像这样使用它：

$values = array(
    'ID' => NULL,
    'name' => $username,
    'address' => $address,
);

$sqlfuncs = array(
    'date' => 'NOW()',
);

pInsertFunc("INSERT INTO", "addresses", $values, $sqlfuncs);

The query string that results looks like this:

结果的查询字符串如下所示：

INSERT INTO addresses (ID, name, address, date) VALUES (:ID, :name, :address, NOW())