3

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'm getting an error on this line: $stmt->execute();

The error says:

PHP Fatal error: Uncaught exception '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 do fix this? I know I can pass multiple variables with a prepared statement, but I'm not quite sure how to do it.

jonmrich
  • 4,233
  • 5
  • 42
  • 94
  • Please show us your **full** error message which you get and make sure, that you show us your **real** and correct code. – Rizier123 May 29 '15 at 13:31
  • Are you sure about the content of `$search_crit` and `$diagnosis` ? – Random May 29 '15 at 13:34
  • @Random Yes...just did `var_dump` and the value are correct. – jonmrich May 29 '15 at 13:40
  • @Rizier123 Just updated with the full SQL query. Was trying to simplify a bit, but maybe the problem is elsewhere. I know I can run this query without prepared statements without errors. – jonmrich May 29 '15 at 13:43
  • are you sure about `@rownum := @rownum + 1` Since `:` is used to define variable names, isn't it the problem, does the statement needs a variable names `=` and so a 3rd parameter definition ? – Random May 29 '15 at 13:49
  • All that first part does is add a "ranking" by adding another column and numbering the rows 1 to whatever. Not sure if it causes a problem here, but I can run it directly in my SQL console. – jonmrich May 29 '15 at 13:57

2 Answers2

4

You can use parameters only once in a query

$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));
Narendrasingh Sisodia
  • 21,247
  • 6
  • 47
  • 54
  • This was exactly the problem. I thought there might be some issue with that, but had no idea what to do alternatively. Thanks! – jonmrich May 29 '15 at 14:15
1

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));
sanderbee
  • 694
  • 7
  • 24