我在PHP中有一个函数(请参阅底部),该函数查询MySQL数据库。当我使用以下值时:
用SQL语句:
SELECT user,
scoreVal AS score,
UNIX_TIMESTAMP(timestamp) AS timestamp
FROM Score
WHERE timestamp >= :from
AND timestamp <= :to
AND map = :map
ORDER BY scoreVal DESC, timestamp ASC
LIMIT :limit
在phpMyAdmin中,我得到以下结果:
但是,PHP PDO返回了一个空数组。
到目前为止,我尝试进行调试:
由此,我认为函数中的占位符存在问题,但是我无法找到它们失败的原因。这很可能是在PHP端发生的,因为MySQL不会向错误文件抛出任何错误。
这是我正在使用的函数,其中传入了变量:
功能:
/**
* Gets the highscore list for the map in that timespan
* @param integer $map Id of map for which to fetch the highscore
* @param integer $limit Maximum no. of records to fetch
* @param integer $from Timestamp from when to find rank
* @param integer $to Timestamp up to when to find rank
* @return array Array of highscores arranged by rank for the map in the format [{"user"=>$user,"score"=>score,"timestamp" => timestamp}]
*/
function get_highscore_list($map,$limit,$from,$to){
$sql = "SELECT user,scoreVal AS score,UNIX_TIMESTAMP(timestamp) AS timestamp FROM Score WHERE timestamp >= :from AND timestamp <= :to AND map = :map ORDER BY scoreVal DESC, timestamp ASC LIMIT :limit";
if ($to==intval(0)){
$max =1;
$sql = str_replace(":to","NOW()",$sql,$max);
}
try{
$conn = request_connection();
$stmt = $conn->prepare($sql);
$stmt->execute(array(':map'=>$map,':from'=>$from,':limit'=>$limit));
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
}catch(PDOException $e){
$_POST["exception"]=$e;
continue;
}
return $result;
}
编辑
MySQL表格式:
我尝试输出$ conn-> errorInfo();,但是由于未引发任何错误,因此我返回了一个值数组:[00000,null,null]
request_connection函数仅返回该函数的结果,并且适用于所有其他语句。
/**
* Creates a new PDO connection to the database specified in the configuration file
* @author Ignacy Debicki
* @return PDO A new open PDO connection to the database
*/
function create_connection(){
try {
$config = parse_ini_file('caDB.ini');
$conn = new PDO('mysql' . ':host=' . $config['dbHost'] . ';dbname=' . $config['db'],$config['dbPHPUser'], $config['dbPHPPass']);
date_default_timezone_set($config['dbTimezone']);
return $conn;
} catch(PDOException $e){
throw new Exception("Failed to initiate connection",102,$e);
}
}
谢谢
经过数小时的尝试,我终于找到了解决方案。
我在创建连接时错过了两个重要的陈述:
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
会打开错误报告(请参阅https://stackoverflow.com/a/8776392/2891273)。
一旦启用此功能,就很难解决我的问题,这是因为如果$ to为0,则覆盖:to参数,$conn->execute()
语句中传递的参数数与sql查询中的参数数不匹配。
我的解决方案是$conn->bindValue()
对每个参数使用代替,使用if语句检查是否绑定到:to参数。下面是我的解决方案:
function get_highscore_list($map,$limit,$from,$to){
$sql='SELECT user, scoreVal AS score, UNIX_TIMESTAMP(timestamp) AS timestamp FROM Score WHERE map = :map AND timestamp >= :from AND timestamp <= :to ORDER BY scoreVal DESC, timestamp ASC LIMIT :limit';
if ($to==0){
$sql = str_replace(":to",'CURRENT_TIMESTAMP()',$sql);
}
$conn = request_connection();
$stmt = $conn->prepare($sql);
$stmt->bindValue(':map',$map,PDO::PARAM_INT);
$stmt->bindValue(':from',$from,PDO::PARAM_INT);
if ($to!=0){
$stmt->bindValue(':to',$to,PDO::PARAM_INT);
}
$stmt->bindValue(':limit',$limit,PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $result;
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句