php 我们可以在Mysql Query中使用PHP函数strtotime吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24568048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can we use PHP function strtotime in Mysql Query
提问by user3766078
I have the following MySQL syntax which gives me an error.
我有以下 MySQL 语法，这给了我一个错误。
I am aware that you cannot compare date variables directly, so I use strtotimeto create a Unix timestamp to compare dates.
我知道你不能直接比较日期变量，所以我使用strtotime创建一个 Unix 时间戳来比较日期。
Can you use the PHP strtotimefunction within a MySQL query?
您可以strtotime在 MySQL 查询中使用 PHP函数吗？
$result = select * from table where strtotime(Date) >= strtotime($_POST[DateFrom1])
&& strtotime(Date) <= strtotime($_POST[DateTo1])";
回答by Code L?ver
Your code must be this:
您的代码必须是这样的：
$result = "select * from table
where unix_timestamp(Date) >= unix_timestamp(".$_POST[DateFrom1].")
&& unix_timestamp(Date) <= unix_timestamp(".$_POST[DateTo1].")";
To convert date in timestamp in mysql there is function unix_timestamp.
要在 mysql 的时间戳中转换日期，有函数unix_timestamp.
回答by lodev09
Your code is NOTa valid PHP syntax. Code below is a clean and proper way of doing this:
您的代码是NOT有效的 PHP 语法。下面的代码是一种干净且正确的方法：
$from = date('Y-m-d', strtotime($_POST["DateFrom1"]));
$to = date('Y-m-d', strtotime($_POST["DateTo1"]));
$query = "select * from table where date between '$from' and '$to'";
// use this query to your mysqli
回答by Mark Miller
Assuming your datefield is of the type dateor datetime, then you cando direct comparison without converting to a timestamp.
假设您的date字段是dateor类型datetime，那么您可以进行直接比较而无需转换为时间戳。
Also, for your specific case, it looks like you could utilize the expr BETWEEN min AND maxoperation, which is equivalent to min <= expr AND expr <= max.
此外，对于您的特定情况，您似乎可以使用expr BETWEEN min AND max操作，这相当于min <= expr AND expr <= max。
$result = "
SELECT * FROM table
WHERE date BETWEEN '" . date('Y-m-d', strtotime($_POST['DateFrom1'])) . "'
AND '" . date('Y-m-d', strtotime($_POST['DateTo1'])) . "'";
Note: if you are storing your dates as a varchardatatype or something other than a dateor datetime(or maybe timestamp), then you should really consider changing this, in order to take full advantage of MySQL's capabilities.
注意：如果您将日期存储为varchar数据类型或除 a dateor datetime（或可能timestamp）以外的其他内容，那么您应该真正考虑更改它，以充分利用 MySQL 的功能。
回答by mtfurlan
No, you need to use the mysql function UNIX_TIMESTAMPas described here
不，您需要使用此处UNIX_TIMESTAMP描述的 mysql 函数
So maybe something along the lines of
所以也许是这样的
$sql = "SELECT * FROM table WHERE UNIX_TIMESTAMP(date) >= '" . strtotime($_POST[DateFrom1]) . "' && UNIX_TIMESTAMP(date) <= '" . strtotime($_POST[DateTo1]) . "';";
Better yet would be to use Named Placeholdersand PDO.
更好的是使用命名占位符和 PDO。
$db = new PDO('mysql:host=whateverHost;dbname=yourDB;charset=utf8', 'username', 'password');
$stmt = $db->prepare("SELECT * FROM table WHERE UNIX_TIMESTAMP(date) >= :dateFrom1 && UNIX_TIMESTAMP(date) <= :dateTo1");
$stmt->execute(array(':dateFrom1' => $_POST[DateFrom1], ':dateTo1' => $_POST[DateTo1]));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
Using PDO means you don't have to worry about things such as SQL injection, and it allows for FAR cleaner SQL statements, you don't have to be throwing values from PHP directly into the SQL.
使用 PDO 意味着您不必担心诸如 SQL 注入之类的事情，并且它允许使用 FAR 更清洁的 SQL 语句，您不必将值从 PHP 直接抛出到 SQL 中。
回答by bitsm
Use mysql's UNIX_TIMESTAMP() it may be what you're looking for.
使用 mysql 的 UNIX_TIMESTAMP() 它可能是你正在寻找的。
