编辑:我已经更改了这个问题的标题,因为我真的没有得到任何结果,而不是空的结果。
我目前正在与PDO合作开展我的一个项目,并且遇到了一个我似乎无法解决的问题。一个标准的MySQL查询(在phpMyAdmin中执行)返回我想要的结果,但是当我使用PDO时,它不会产生任何结果。我注意到如果我不绑定查询工作的值,所以想看看我缺少什么......
下面是 MySQL 查询:
SELECT AVG( demand_value )
FROM Demand
WHERE DATE = STR_TO_DATE( '06 Nov 2013', '%d %M %Y' )
AND TIME
BETWEEN STR_TO_DATE( '07:00:00', '%k:%i:%s' )
AND STR_TO_DATE( '10:00:00', '%k:%i:%s' );
下面是试图获得相同结果的PHP代码(使用PDO):
//Assume $date = '06 Nov 2013', $time_from = '07:00:00', $time_to = '10:00:00'
//Those values are parsed in from a custom function
$ave_btwn_times_stmt =
$connection->get_conn()->prepare("SELECT AVG( demand_value )
FROM Demand
WHERE date = STR_TO_DATE(:date, '%d %M %Y')
AND time BETWEEN STR_TO_DATE(:time_from, '%k:%i:%s')
AND STR_TO_DATE(:time_to, '%k:%i:%s')");
$ave_btwn_times_stmt->bindValue(':date', $date, PDO::PARAM_STR);
$ave_btwn_times_stmt->bindValue(':time_from', $time_from, PDO::PARAM_STR);
$ave_btwn_times_stmt->bindValue(':time_to', $time_to, PDO::PARAM_STR);
$ave_btwn_times_stmt->execute();
$ave_date_times = $ave_btwn_times_stmt->fetchAll();
echo "Average on " . $date . ", between " . $time_from . " and " . $time_to . ": " . $ave_date_times[0][0] . ".";
如果我实现下面的代码,我会得到我想要的结果:
$ave_btwn_times_stmt =
$connection->get_conn()->prepare("SELECT AVG( demand_value )
FROM Demand
WHERE date = STR_TO_DATE('$date', '%d %M %Y')
AND time BETWEEN STR_TO_DATE('$from_time', '%k:%i:%s')
AND STR_TO_DATE('$to_time', '%k:%i:%s')");
$ave_btwn_times_stmt->execute();
$ave_date_times = $ave_btwn_times_stmt->fetchAll();
echo "Average on " . $date . ", between " . $time_from . " and " . $time_to . ": " . $ave_date_times[0][0] . ".";
如果有人能帮助我解决这个问题,我将不胜感激。
您的 MySQL 查询抛出了此错误
对不起,恐怕你做不到。SQLSTATE[42S22]:列不是 找到:1054 "where 子句"中的未知列"TIME"
我已经重构了您的查询,删除了BETWEEEN
并将其替换为 2 个比较测试。
SELECT AVG( demand_value ) FROM Demand
WHERE DATE = STR_TO_DATE( '06 Nov 2013', '%d %M %Y' )
AND `time_from` >= STR_TO_DATE( `time_from`,'%k:%i:%s' )
AND `time_to` >= STR_TO_DATE(`time_to`,'%k:%i:%s' )
以下代码使用未命名的占位符提供所需的输出。
$dbh = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$ave_btwn_times_stmt= $dbh->prepare("SELECT AVG( demand_value )
FROM Demand WHERE DATE = STR_TO_DATE( ?, '%d %M %Y' )
AND `time_from` >= STR_TO_DATE( ?,'%k:%i:%s' )
AND `time_to` >= STR_TO_DATE(?,'%k:%i:%s' )");
// Assign parameters
$ave_btwn_times_stmt->bindParam(1,$date);
$ave_btwn_times_stmt->bindParam(2,$time_from);
$ave_btwn_times_stmt->bindParam(3,$time_to);
$ave_btwn_times_stmt->execute();
$ave_date_times = $ave_btwn_times_stmt->fetchAll();
echo "Average on " . $date . ", between " . $time_from . " and " . $time_to . " = " . $ave_date_times[0][0] . ".";
}
catch(PDOException $e) {
echo "I'm sorry I'm afraid you can't do that.". $e->getMessage() ;// Remove or modify after testing
file_put_contents('PDOErrors.txt',date('[Y-m-d H:i:s]').", mapSelect.php, ". $e->getMessage()."'r'n", FILE_APPEND);
}
//Close the connection
$dbh = null;
测试后,您应该删除错误回显或 Try/Catch 块并依赖 PHP 错误处理