我的PHP是这样的:
$sql1="SELECT @rownum := @rownum + 1 Rank, q.* FROM (SELECT @rownum:=0) r,(SELECT * ,sum(`number of cases`) as tot, sum(`number of cases`) * 100 / t.s AS `% of total` FROM `myTable` CROSS JOIN (SELECT SUM(`number of cases`) AS s FROM `myTable` where `type`=:criteria and `condition`=:diagnosis) t where `type`=:criteria and `condition`=:diagnosis group by `name` order by `% of total` desc) q"";
$stmt = $dbh->prepare($sql1);
$stmt->bindParam(':criteria', $search_crit, PDO::PARAM_STR);
$stmt->bindParam(':diagnosis', $diagnosis, PDO::PARAM_STR);
$stmt->execute();
$result1 = $stmt->fetchAll(PDO::FETCH_ASSOC);
header('Content-type: application/json');
echo json_encode($result1);
我在这行得到一个错误:$stmt->execute();
错误提示:
PHP致命错误:未捕获异常'PDOException'与消息'SQLSTATE[HY093]: Invalid parameter number'在PHP/ranking . PHP:39
堆栈跟踪:
"#"0 php/ranking .php(39): PDOStatement->execute()
" # " 1{主要}php/rankings.php第39行抛出
我怎样才能解决这个问题?我知道我可以通过一个准备好的语句传递多个变量，但我不太确定如何做到这一点。
在查询中只能使用一次参数
$sql1="SELECT @rownum := @rownum + 1 Rank, q.* FROM (SELECT @rownum:=0) r,(SELECT * ,sum(`number of cases`) as tot, sum(`number of cases`) * 100 / t.s AS `% of total` FROM `myTable` CROSS JOIN (SELECT SUM(`number of cases`) AS s FROM `myTable` where `type`=:criteria and `condition`=:diagnosis) t where `type`=:criteria2 and `condition`=:diagnosis2 group by `name` order by `% of total` desc) q";
$stmt = $dbh->prepare($sql1);
$stmt->execute(array(':criteria' => $search_crit, ':diagnosis' => $diagnosis, ':criteria2' => $search_crit, ':diagnosis2' => $diagnosis));
您可以像这样在execute语句中添加一个数组:
$sql1="SELECT * FROM myTable WHERE `area` = :criteria AND `condition` = :diagnosis";
$stmt = $dbh->prepare($sql1);
$stmt->execute(array('criteria' => $search_crit, 'diagnosis' => $diagnosis));