Clever Geek Handbook

Binding multiple parameters for a prepared statement

My PHP looks like this:

$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);

I am getting an error on this line: $stmt->execute();

The error says:

PHP Fatal error: Throw "PDOException" with message "SQLSTATE [HY093]: Invalid parameter number" in php / rankings.php: 39

Stack trace:

"#" 0 php / rankings.php (39): PDOStatement-> execute ()

"#" 1 {main} thrown in php / rankings.php on line 39

How can I fix this? I know I can pass multiple variables with a prepared statement, but I'm not really sure how.

+3


source to share


2 answers


You can only use parameters once per request



$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));
+3


source


You can add an array to the execute statement like this:



$sql1="SELECT * FROM myTable WHERE `area` = :criteria AND `condition` = :diagnosis";
    $stmt = $dbh->prepare($sql1);       
    $stmt->execute(array('criteria' => $search_crit, 'diagnosis' => $diagnosis));
+1


source






More articles:


All Articles