我有$year和$month,我需要选择时间戳在一年/月之后和另一年/月之前的所有记录
WHERE m.id = :magazineId
AND t.isChargedBack IS FALSE
AND t.`timestamp` >= STR_TO_DATE(:year'-08-01', '%Y-%m-%d')
AND t.`timestamp` < STR_TO_DATE('2013-09-01', '%Y-%m-%d')
刚开始的时候,我只分出了一个年份条目,查询就不再查找任何行了。
如何将其参数化?
您是否尝试使用:
`t`.`timestamp` BETWEEN "$DATE1" AND "$DATE2"?
之间的MySQL
您可能还想尝试将日期转换为时间戳,而不是使用STR_to_DATE
试试这个:
$date1 = strtotime('01-08-$year');
$date2 = strtotime('01-09-2013');
$query = "SELECT * from your_table WHERE m.id = '{$magazineId}' AND t.isChargedBack IS FALSE
AND t.`timestamp` BETWEEN '{$date1}' AND '{$date2}'";
本质上(为了示例起见,没有PDO参数)
PHP
$target = $year . '/' . $month . '/01';
SQL
" ...
WHERE m.id = :magazineId
AND t.isChargedBack IS FALSE
AND t.`timestamp` >= '$target'
AND t.`timestamp` < DATEADD('$target' + 1 MONTH);"
$mysqli = new mysqli("host", "user", "password", "world");
$mymagazineid = "Foo";
$year = "2013";
$parameter_clause = <<<EOD
SELECT * FROM YourTable
WHERE m.id = ?
AND t.isChargedBack IS FALSE
AND t.`timestamp` >= STR_TO_DATE(?, '%Y-%m-%d')
AND t.`timestamp` < STR_TO_DATE('2013-09-01', '%Y-%m-%d')
EOD;
$stmt = mysqli_prepare($mysqli, $parameter_clause);
mysqli_stmt_bind_param($stmt, "ss", $mymagazineid, "$year-08-01");
mysqli_stmt_execute($stmt);