Error making dynamic PHP Insert

Question

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 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

Answer 1

Try these settings in the following line:

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

And, to debug, change these:

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

Alternatively, you could consider positional parameters, not rated, 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 );
}