-4

First: Sorry, I know, this question has been asked a million times. And I know this is a human error that I'm probably missing a bindStatement somewhere down the line. Can you help me find it?

I'm having the following problem with PDO and prepared statements.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in /home/midelive/public_html/creapizza/model/temaHelper.php:64 Stack trace: #0

That is the error message. It happens for two methods on the same class, and its driving me nuts

code N1:

  public function create(tema $t, $idUser){
    $pdo_options[PDO::ATTR_ERRMODE]=PDO::ERRMODE_EXCEPTION;  ///username, Pass, DB
$conn= new PDO($this->connection,$this->admin,$this->pass,$pdo_options);
    $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $tipo=$t->tipo;
    $nombreEmpresa=$t->titulo;
    $logo=$t->logo;
    $subtitulo=$t->subtitulo;
    $simbolo1=$t->simbolo1;
    $simbolo2=$t->simbolo2;
    $simbolo3=$t->simbolo3;
    $texto1=$t->txt1;
    $texto2=$t->txt2;
    $texto3=$t->txt3;
    $linkSitio=$t->link;
    $linkFacebook=$t->linkfb;
    $descripcion=$t->descripcion;

    $stmt=$conn->prepare(sprintf("INSERT INTO tema  (idUsuario, tipoTema, nombreEmpresa, logo, 
        subtitulo,simbolo1, simbolo2, simbolo3, texto1, texto2, texto3, linkFacebook, Descripcion)
         VALUES (:idUsuario,:tipoTema,:nombreEmpresa,:logo,:subtitulo, :simbolo1, simbolo2, :simbolo3, 
         :texto1, :texto2, :texto3,:linkFacebook,:Descripcion)"));
    $stmt->bindParam(':idUsuario', $idUser);
    $stmt->bindParam(':tipoTema', $tipo);
   $stmt->bindParam(':nombreEmpresa',$nombreEmpresa);
    $stmt->bindParam(':logo',$logo);
    $stmt->bindParam(':subtitulo', $subtitulo);
    $stmt->bindParam(':simbolo1',$simbolo1);
    $stmt->bindParam(':simbolo2',$simbolo2);
    $stmt->bindParam(':simbolo3',$simbolo3);
    $stmt->bindParam(':texto1',$texto1);
    $stmt->bindParam(':texto2',$texto2);
    $stmt->bindParam(':texto3',$texto3);
    $stmt->bindParam(':linkFacebook',$linkFacebook);
    $stmt->bindParam(':Descripcion',$descripcion);

    $stmt->execute();
    $statement=$conn->query("SELECT LAST_INSERT_ID()");
$lastID=$statement->fetch(PDO::FETCH_NUM);
return $lastID[0];
}

Code N2:

public function create(tema $t, $idUser){
    $pdo_options[PDO::ATTR_ERRMODE]=PDO::ERRMODE_EXCEPTION;  ///username, Pass, DB
$conn= new PDO($this->connection,$this->admin,$this->pass,$pdo_options);
    $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $tipo=$t->tipo;
    $nombreEmpresa=$t->titulo;
    $logo=$t->logo;
    $subtitulo=$t->subtitulo;
    $simbolo1=$t->simbolo1;
    $simbolo2=$t->simbolo2;
    $simbolo3=$t->simbolo3;
    $texto1=$t->txt1;
    $texto2=$t->txt2;
    $texto3=$t->txt3;
    $linkSitio=$t->link;
    $linkFacebook=$t->linkfb;
    $descripcion=$t->descripcion;

    $stmt=$conn->prepare(sprintf("INSERT INTO tema  (idUsuario, tipoTema, nombreEmpresa, logo, subtitulo,simbolo1, simbolo2, simbolo3, 
            texto1, texto2, texto3, linkFacebook, Descripcion) VALUES (:idUsuario , :tipoTema , :nombreEmpresa , :logo , :subtitulo ,
            :simbolo1 , :simbolo2 , :simbolo3 , :texto1 , :texto2 , :texto3 , :linkFacebook , :Descripcion)"));
    $stmt->bindParam(':idUsuario', $idUser);
    $stmt->bindParam(':tipoTema', $tipo);
    $stmt->bindParam(':nombreEmpresa',$nombreEmpresa);
    $stmt->bindParam(':logo',$logo);
    $stmt->bindParam(':subtitulo', $subtitulo);
    $stmt->bindParam(':simbolo1',$simbolo1);
    $stmt->bindParam(':simbolo2',$simbolo2);
    $stmt->bindParam(':simbolo3',$simbolo3);
    $stmt->bindParam(':texto1',$texto1);
    $stmt->bindParam(':texto2',$texto2);
    $stmt->bindParam(':texto3',$texto3);
    $stmt->bindParam(':linkFacebook',$linkFacebook);
    $stmt->bindParam(':Descripcion',$descripcion);

    $stmt->execute();
    $statement=$conn->query("SELECT LAST_INSERT_ID()");
$lastID=$statement->fetch(PDO::FETCH_NUM);
return $lastID[0];
}

I'm going kinda crazy counting over and over the statements and the query. I did a ctrl+F search to check that I did not mispelled any of those, but found nothing.

Nissa
  • 4,636
  • 8
  • 29
  • 37
Bruno Gutierrez
  • 165
  • 6
  • 2
    You don't need to bind parameters in PDO. Just pass them as an array to `PDOStatement::execute()` – miken32 Feb 16 '17 at 21:43
  • `sprintf` is unnecessary, but will not cause any harm unless you pass a string that looks like a format specifier like `%d` or something. Best to remove it anyway. – miken32 Feb 16 '17 at 21:50

2 Answers2

1

The error message is quite clear about the reason: The number of bound variables does not match the number of tokens. That is you either have too few or too much tokens.

When looking closer at the first code snippet, you will see that you missed the colon (:) in front of simbolo2, thus having one token too few. So instead of

stmt=$conn->prepare(sprintf("INSERT INTO tema  (idUsuario, tipoTema, nombreEmpresa, logo, 
    subtitulo,simbolo1, simbolo2, simbolo3, texto1, texto2, texto3, linkFacebook, Descripcion)
     VALUES (:idUsuario,:tipoTema,:nombreEmpresa,:logo,:subtitulo, :simbolo1, simbolo2, :simbolo3, 
     :texto1, :texto2, :texto3,:linkFacebook,:Descripcion)"));

you should put

stmt=$conn->prepare(sprintf("INSERT INTO tema  (idUsuario, tipoTema, nombreEmpresa, logo, 
    subtitulo,simbolo1, simbolo2, simbolo3, texto1, texto2, texto3, linkFacebook, Descripcion)
     VALUES (:idUsuario,:tipoTema,:nombreEmpresa,:logo,:subtitulo, :simbolo1, :simbolo2, :simbolo3, 
     :texto1, :texto2, :texto3,:linkFacebook,:Descripcion)"));

Quite easy to miss, especially since the query is a bit longer.

Striezel
  • 3,693
  • 7
  • 23
  • 37
  • thanks!, that solved the first one! ... im trying to find a similar error on the second one (misplaced colon (:) or using a semicolon (;), but i i havent found one. ¿Something else im missing? – Bruno Gutierrez Feb 17 '17 at 02:17
-2

If you cannot manage to make named parameters match, go for positional placeholders. At least to spot an error you will only have to count the question marks.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345