Error making a dynamic insert in PHP

Question

Error making a dynamic insert in PHP

Navigation

#1 by (3 votes)

3

The code that is causing the error:

public function Inserir($tabela,$sql){

    ksort($sql);

    $Campos_nome=  implode('', '', array_keys($sql));
    $Campos_valor= ': '. implode(', :', array_keys($sql));

    $novo=$this->prepare("INSERT INTO $tabela ( '$Campos_nome')VALUES( $Campos_valor)");

    print "INSERT INTO $tabela (' $Campos_nome')VALUES( $Campos_valor)";
    foreach ($sql as $key => $valor) {
        $novo->bindValue(":$key",$valor);
    }

        $novo->execute();

        if($novo->rowCount()>0){
            $novo->setFetchMode(PDO::FETCH_ASSOC);
            $valor=$novo->fetchAll(); 
        }
}

The error message you received:

Warning: PDOStatement :: execute (): SQLSTATE [HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /home/sam/Dropbox/Portal1/libs/Database.php on line 39

php pdo

asked by anonymous 28.05.2014 / 11:18

1 answer

Get dynamically created Checkbox values Return focus to the window that opened the popup

score 3 · Accepted Answer

Try these adjustments on the following line:

// Remoção do espaço nos primeiros ':'
$Campos_valor= ':'. implode(', :', array_keys($sql));

And, for debug, change these:

$query = "INSERT INTO $tabela ( '$Campos_nome' ) VALUES ( $Campos_valor )";
$novo = $this->prepare( $query );
print hmlentities( $query );

Alternatively, you could consider positional, non-nominated parameters to simplify:

$Campos_nome = implode( '', '', array_keys( $sql ) );
$Campos_valor= '?'.str_repeat( ',?', count( $sql ) - 1 );

...

$i = 1;
foreach ( $sql as $key => $valor ) {
   $novo->bindValue( $i++, $valor );
}