PDO使用MySQL的NOW() [英] PDO Use MySQL's NOW()
问题描述
我将如何在PDO准备好的语句中使用MySQL的NOW()，或者在考虑到Apache Server和Database Server可能存在当前时间不匹配(几秒钟)或难得的时光可能相隔时区?
How would I use MySQL's NOW() in PDO prepared statements, or how would I workaround using it while keeping in mind that possibly the Apache Server and Database Server might have either a slightly current time mismatch (few seconds), or in rare occasions might be timezones apart?
我的代码中具有以下功能:
I have the following function in my code:
try {
$dbh->insert("users", array(
"email" => $email,
"password" => $password,
"salt" => $salt,
"ingame" => $ingame,
"kiosk" => $kiosk
));
} catch (PDOException $ex) {
error($ex);
}
哪个电话:
/**
* Inserts data into a table. Data must be given in key-value pairs.
*
* Example: $dbh->insert("table", array(
* "data1" => $data1,
* "data2" => $data2
* );
*
* @param type $table The table to insert to
* @param type $keyvaluepairs The key-value pairs.
* @return type The statement that this query produced.
*/
public function insert($table, $keyvaluepairs) {
$sql = "INSERT INTO `{$table}` (";
$values_sql = ") VALUES(";
$values = array();
foreach ($keyvaluepairs as $key => $value) {
$sql .= "`${key}`, ";
$values_sql .= "?, ";
$values[] = $value;
}
$query = substr($sql, 0, -2).substr($values_sql, 0, -2).")";
return $this->query($query, $values);
}
哪个电话:
//TODO update documentation to show it also handles associative arrays with bindvalue
/**
* Can be called to create a query. Use either unnamed or named placeholders for the prepared statements.
*
* Example: $dbh->query("INSERT INTO table (data1, data2) VALUES(?, ?)", array($data1, $data2));
*
* @param type $query The input query, including unnamed or named placeholders
* @param type $values The input values. If it's not an array, then it will be an one-element array
* @return type The statement constructed by this query
*/
public function query($query, $values = array()) {
if (!is_array($values)) {
$values = array($values);
}
$statement = $this->dbh->prepare($query);
$statement->setFetchMode(PDO::FETCH_OBJ);
$i = 1;
if (is_assoc($values)) {
foreach ($values as $key => $value) {
$statement->bindValue($key, $value);
}
}
else {
foreach ($values as $value) {
$statement->bindValue($i++, $value);
}
}
$statement->execute();
return $statement;
}
我在其中的功能:
function is_assoc($array) {
return (bool)count(array_filter(array_keys($array), 'is_string'));
}
所以这里的问题是我不能使用自定义的MySQL查询来插入，因为为了简单起见我已经封装了这些查询，但是我仍然希望能够不插入而插入NOW() 使用TIMESTAMP/CURRENT_TIMESTAMP().
So the deal here is that I cannot use custom MySQL queries for the inserts as I've encapsulated those for the sake of easyness, but I still want to be able to insert NOW() without making use of TIMESTAMP / CURRENT_TIMESTAMP().
我希望您理解这个问题需要一个解释性的答案，因为我已经阅读了正常"答案并表明它们不能满足我的需求.
I hope you understand that this question requires an explained answer as I have already read the 'normal' answers and shown that they do not satisfy my needs.
更新:我已经在DBH类中添加了const SQL_NOW = 1;，但是现在我想将insert修改为类似这样的内容:
UPDATE: I have added const SQL_NOW = 1; to my DBH class, however now I want to modify the insert to something like this:
public function insert($table, $keyvaluepairs) {
$sql = "INSERT INTO `{$table}` (";
$values_sql = ") VALUES(";
$values = array();
foreach ($keyvaluepairs as $key => $value) {
if ($value == SELF::SQL_NOW) {
$sql .= "NOW(), ";
}
else {
$sql .= "`${key}`, ";
$values_sql .= "?, ";
$values[] = $value;
}
}
$query = substr($sql, 0, -2).substr($values_sql, 0, -2).")";
return $this->query($query, $values);
}
这可能是一个合适的解决方案，但是我不能将1用作SQL_NOW值，因为如果我想插入整数1，它将失败.如果我采用这种解决方案，那么SQL_NOW将具有什么值?甚至有可能赋予它没有价值吗?
Which may be a suitable solution, however I cannot use 1 as SQL_NOW value, as it would then fail if I'd want to insert an integer 1. If I go with this solution, what value would SQL_NOW then have? Is it even possible to give it no value?
推荐答案
一个很好的问题！
这是一个完美的例子，清楚地说明了为什么所有众多insert(), update()和所有其他旨在替代SQL的东西在设计上都是错误的.
This is a perfect example that clearly shows why all these numerous insert(), update() and all other stuff, intended to substitute SQL, are wrong by design.
NOW()不是您将要面对的唯一问题.乍一看，只是因为 SQL并不是一种愚蠢的语言.它是有意发明的.而且它的可靠性已经被证明了数十年.这意味着要像学习PHP时的练习那样编写整个SQL并非那么容易.
NOW() is not the only issue you will face with. Just because SQL is not that silly a language as it seems at first glance. And it was invented on purpose. And it's reliability was proven for decades. Means it is not that easy to write whole SQL just as an exercise while learning PHP.
因此，您最能做的是保持SQL原样.
您真正需要的是一个或两个辅助函数.自动执行重复性任务.就这些.虽然SQL必须保留原样.这样一来，您就可以使用所有功能，包括使用函数，带有参数(！)的函数，查询修饰符(例如"INSERT IGNORE")或扩展语法(例如JOINS).
What you really, really need is a helper function or two. To automate the repetitive tasks. That is ALL. While SQL have to be left as is. Which will let you to use ALL it's power including use of functions, functions with arguments(!), query modifiers, such as 'INSERT IGNORE' or extended syntax like JOINS.
如果您使用的是PDO，那不是那么简单但是可行.
In case you are using PDO, it is not that simple but feasible.
但是，唯一正确的解决方案是对SET语句使用特殊类型的占位符.
However, the only proper solution is to use a placeholder of the special type for the SET statement.
$data = array(
"email" => $email,
"password" => $password,
"salt" => $salt,
"ingame" => $ingame,
"kiosk" => $kiosk,
);
$dbh->query("INSERT INTO ?n SET reg = NOW(), ?u","users", $data);
只需一行就可以解决所有麻烦以及许多很多其他问题.
只需一个query()方法即可运行所需的任何查询，甚至是REPLACE INTO.
Just one single line to solve all that mess and many, many, many other issues.
Just one single query() method to run any query you want, even REPLACE INTO.
更新.
您打算在课程中简化 的内容.但是目前，您正在使其变得越来越复杂.最后，您将拥有一个笨拙的巨人，即使对于它的创建者和其他人而言，也几乎不了解众多例外的语法不一致.还有哪个 still 不允许您运行某些查询.
You were planning your class to simplify things. But at the moment you are making it more and more complex. In the end you will have a hulking giant which inconsistent syntax for the numerous exceptions scarcely understood even by it's creator and noone else. And which still don't let you run some queries.
请在为时已晚之前重新考虑您的设计.
Please rethink your design before it's too late.
这篇关于PDO使用MySQL的NOW()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
