"Invalid parameter number" in PDO

Question

I'm doing a prepared statement in PDO. I'm getting the error written below and I don't know where's my mistake.

Error!: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Code:

$qA=array();
$lastName=$_POST['cours'];

if(trim($_POST["nomActivity"]) != ""){
  $nomActivity= filter_var($_POST['nomActivity'], FILTER_SANITIZE_STRING);
  $qA[] = "a.title = :title";
}
if(trim($_POST["nPlace"]) != ""){
  $nPlace= filter_var($_POST['nPlace'], FILTER_SANITIZE_NUMBER_INT);
  $qA[] = "a.nPlace = :nPlaceA";
} 
if(isset($_POST['date'])){
  $date = $_POST['date'];
  $qA[] = "a.date = :date";
}

$stmtA = $con->prepare("UPDATE activity a SET " . implode(", ", $qA) . " where a.title= :lastN");

if(trim($_POST["nomActivity"]) != ""){
  $stmtA->bindParam(":title", $nomActivity);
} 
if(trim($_POST["nPlace"]) != ""){
  $stmtA->bindParam(":nPlaceA", $nPlace);
} 
if(trim($_POST["date"]) != ""){
  $stmtA->bindParam(":date", $date);
}
if(trim($_POST["cours"]) != ""){
  $stmtA->bindParam(":lastN", $lastName);
} 

$stmtA->execute();

You are always using `:lastN` in your query (`where a.title= :lastN`), but you are contitionally binding it. That means there are cases where it might not be bound. — Patrick Q, Apr 27 '18 at 19:19
: "It looks like you're writing your own ORM. Have you considered using one that's already written, tested, and widely supported like [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/) or [Eloquent](https://laravel.com/docs/master/eloquent)?" — tadman, Apr 27 '18 at 20:32
@tadman: as much as I liked Propel in its heyday, Propel is essentially abandoned now. The maintainers gave up with refactoring Propel2 and have not made a [Propel3](https://github.com/propelorm/Propel3) release in two years. — halfer, May 01 '18 at 22:40
@halfer I'm always open to updating those recommendations with more current examples. Have any suggestions or favorites? — tadman, May 01 '18 at 22:41
@tadman: I wish I did, but while there's [plenty of PHP ORM projects](http://stackoverflow.com/questions/108699), none of them are particularly substantial or current. Doctrine and Eloquent are the only serious/maintained ones I know of. I'd be happy to be corrected on this too! — halfer, May 01 '18 at 22:46
([This looks OK](http://j4mie.github.io/idiormandparis/) but both of the subprojects are declared feature-frozen now, so I don't know what the maintenance status is now). — halfer, May 01 '18 at 22:48

ScaisEdge · Accepted Answer · 2018-04-27T19:26:12.067

1

seems you miss the proper check for cours

  if(trim($_POST["nomActivity"]) != ""){
    $nomActivity= filter_var($_POST['nomActivity'], FILTER_SANITIZE_STRING);
    $qA[] = "a.title = :title";
  }
  if(trim($_POST["nPlace"]) != ""){
    $nPlace= filter_var($_POST['nPlace'], FILTER_SANITIZE_NUMBER_INT);
    $qA[] = "a.nPlace = :nPlaceA";
  } 
  if(isset($_POST['date'])){
    $date = $_POST['date'];
    $qA[] = "a.date = :date";
  }

  // this one
  if(isset($_POST['cours'])){
    $lastName=$_POST['cours'];
  } else {
    $lastName = 'your_default_value';
  }

and you have always :lastN to assign

  $stmtA->bindParam(":lastN", $lastName);

edited Apr 27 '18 at 19:26

answered Apr 27 '18 at 19:19

ScaisEdge

131,976
10
91
107

@PatrickQ correct .. answer updated . with a suggestion for default value of not $_POST['cours'] – ScaisEdge Apr 27 '18 at 19:26
@scaisEdge, @Patrick Q Is it necessary since `cours` is from a select with option required ? – Hiiro Apr 27 '18 at 19:28
@Hiiro . a value for $lastName is necessary for binding the :lastN . that is always present .. so or you assign when cours is present or you assign a value by default ( or you change the where condition) – ScaisEdge Apr 27 '18 at 19:30
There will always be a value for `$lastName` since it come from this ` – Hiiro Apr 27 '18 at 19:41
then remember to assign ever the related values in $stmtA->bindParam(":lastN", $lastName); in your cade you assign this param only of cours is on $_POST ... as is suggested at the bottom pf my answer .. – ScaisEdge Apr 27 '18 at 19:42

"Invalid parameter number" in PDO

1 Answers1