Invalid parameter error when doing PDO perform action

Question

I’m trying to learn how to use PDO, so I’ve already worked HARD looking for everything to understand it. So I’m at a point that has left me quite confused.

I got the following code, he’s the: _conecta_bank.php

<?php
class conectar_banco {

    var $host        = 'localhost';
    var $usuario     = 'root';
    var $senha       = '';
    var $banco       = 'trabalho_kinccal';

    var $pdo         = null;
    var $buscaSegura = null;
    var $qtdeLinhas  = null;

    // Cria a função para Conectar ao Banco MySQL
    function conecta() {
        try{
            $this->pdo = new PDO("mysql:host=".$this->host.";dbname=".$this->banco,$this->usuario,$this->senha);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }catch(PDOException $e){
            echo $e->getMessage();
        }
    }

    // Método de Busca
    public function buscar($table, $where = null, $order = null, $limit = null) {
        $busca = 'SELECT * FROM '.$table;
            if($where != null) $busca .= ' WHERE :where';
            if($order != null) $busca .= ' ORDER BY :order';
            if($limit != null) $busca .= ' LIMIT :limit';

        $buscaSegura = $this->pdo->prepare($busca);
        $buscaSegura->bindValue(":where",$where);
        $buscaSegura->bindValue(":order",$order);
        $buscaSegura->bindParam(":limit",$limit);
        $buscaSegura->execute();

        // Salvar número de registros
        echo $this->buscaSegura = $buscaSegura->rowCount();
    }

    function inserir($tabela, $valores, $campos = null){
        $inserir = 'INSERT INTO ' . $tabela;
        if($campos != null) $inserir .= ' ('.$campos.')';
        for($i = 0; $i < count($valores); $i++){
            if( is_string($valores[$i]) ) $valores[$i] = '"'.$valores[$i].'"';
        }
        $valores = implode(',',$valores);
        $inserir .= ' VALUES ('.$valores.')';

        $inserirSeguro = $this->pdo->prepare($inserir);
        //$inserirSeguro->bindValue(
    }

    function qtdeLinhas() {
        return $this->buscaSegura;
    }
}
?>

Well, then I created a index php., and he’s like:

<?php
include ("class/_conecta_banco.php");
$data = new conectar_banco();
$data->conecta();
$data->buscar('usuarios','','','1');
$data->qtdeLinhas();

?>

Well, I didn’t test that one insert but I already know that it should not rotate. I will go straight to the point. When will I open the index php. he gives the following error:

Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[HY093]: Invalid Parameter number: number of bound variables does not match number of tokens' in C: xampp htdocs PDO class_conecta_banco.php:34 Stack trace: #0 C: xampp htdocs PDO class_conecta_banco.php(34): Pdostatement->execute() #1 C: xampp htdocs PDO index.php(5): conectar_banco->buscar('usuarios', ', '', '1') #2 {main} thrown in C: xampp htdocs PDO class_conecta_banco.php on line 34

But if I change that line on index php.

$data->buscar('usuarios','','','1');

to: (I only took the value)

$data->buscar('usuarios','','','');

it even shows result.

In the method fetch I put conditions where I would build the SQL command line, but I don’t understand why when I use it, he doesn’t want to use it! Understand? If I use the echo there after the if it will show that it is forming the string which will be used just below.

ADD: I thought I could do kind of a 'global builder', you know? Do you think there’s any way I can do that? I’ll really have to create all the SELECT possibilities I want to use?

Answer 1

I see some problems (maybe not all) in your use of PDO:

• Parameters serve to override values, not snippets of a clause.
  That is: you cannot use WHERE :where, needs something like WHERE coluna = :valor. And you can’t say ORDER BY :order and pass a column name.

• You cannot associate a parameter to statement and not use it within the statement.
  That is: if you use $buscaSegura->bindValue(":valor", $valor);, the name :valor need to appear in the query. This is the cause of the error you are seeing:

  Invalid Parameter number: number of bound variables does not match number of tokens