SQLSTATE[HY093]: Invalid Parameter number: Parameter was not defined

Asked

Viewed 4,734 times

-1

0 /opt/lampp/htdocs/Renan/conexion.php(53): Pdostatement->execute(Array)
1 /opt/lampp/htdocs/Renan/conexion.php(72): Conexaodatabase->execute(Object(Pdostatement), Array)
2 /opt/lampp/htdocs/Renan/pdo_acao.php(122): Conexaodatabase->save('INSERT INTO Bl...', Array)
3 /opt/lampp/htdocs/Renan/pdo_acao.php(167): Personal()
4 {main}string(68) "SQLSTATE[HY093]: Invalid Parameter number: Parameter was not defined" {"codError":"999","message":"An error occurred in the process!"}

I am trying to make an insertion in the database and this error appeared when debugging javascript code. What can be ?

public function cadastrarPessoa() {

    $this->data[$this->action] = $this->dadosUpdate();

    if ($this->checkCodSecurity($this->params['lbl_CODCELULA'])) {

        $keys = array_keys($this->data[$this->action]);
        $inputs = implode(', ', $keys);
        $data = array_values($this->data[$this->action]);
        $save = $this->conexao->save("INSERT INTO {$this->tabela } ({preg_replace('/:/','',$inputs)}) VALUES ({$inputs})", $data);

        if ($save) {
            error_log("Entro no salve sucesso!");
            return $this->getMessageView();
        }
        error_log("Entro no salve error!");
        return $this->getMessageView(2);
    }
    return $this->getMessageView(3);
}

private function checkCodSecurity($cod) {
    $data = $this->conexao->fetch("SELECT * FROM tbl_CELULAS WHERE TXT_CODIG_SECUR = :cod", array('cod' => $cod));
    if (!empty($data)) {
        return true;
    }
    return false;
}

public function dadosUpdate() {

    $data = array();

    $linha = $this->conexao->fetch("SELECT * FROM tbl_CELULAS WHERE TXT_CODIG_SECUR = :cod", array('cod' => $this->params['lbl_CODCELULA']));

    $codIgrej = $linha->COD_IDENT_IGREJ;


    //date('Y/m/d',strtotime($this->params['dte_ANIVERSARIO']));  SERVER PARA COLOCAR A DATA NO FORMATO IDEAL PARA O BANCO DE DADOS.

    $data[':COD_IDENT_PESSO'] = 'null'; 
    $data[':COD_IDENT_IGREJ'] = trim($codIgrej); 
    $data[':TXT_NOMEX_PESSO'] = trim($this->params['lbl_NOME']);
    $data[':TXT_APELI_PESSO'] = trim($this->params['lbl_APELIDO']);
    $data[':TXT_FONEX_PESSO'] = trim($this->params['lbl_TELEFONE']);
    $data[':DAT_NASCI_PESSO'] = trim(date('Y/m/d',strtotime($this->params['dte_ANIVERSARIO'])));
    $data[':TXT_NASCI_PESSO'] = 'null';
    $data[':TXT_NATUR_PESSO'] = 'null';
    $data[':FLG_SEXOX_PESSO'] = trim($this->params['lbl_SEXO']);
    $data[':FLG_ESTAD_CIVIL'] = 'null';
    $data[':FLG_IDENT_PESSO'] = 'A';
    $data[':TXT_EMAIL_PESSO'] = trim($this->params['lbl_EMAIL']);
    $data[':TXT_SENHA_USUAR'] = trim($this->params['lbl_SENHA']);
    $data[':TXT_ENDER_CEPXX'] = trim($this->params['lbl_CEP']);
    $data[':SGL_ENDER_ESTAD'] = trim($this->params['lbl_ESTADO']);
    $data[':TXT_ENDER_CIDAD'] = trim($this->params['lbl_CIDADE']);
    $data[':TXT_ENDER_BAIRR'] = trim($this->params['lbl_BAIRRO']);
    $data[':TXT_ENDER_LOGRA'] = trim($this->params['lbl_RUA']);
    $data[':TXT_ENDER_NUMER'] = trim($this->params['lbl_NUMERO']);
    $data[':TXT_ENDER_COMPL'] = trim($this->params['lbl_COMPLEMENTO']);
    $data[':BLO_FOTOX_PESSO'] = 'null';
    $data[':COD_IDULT_ATUAL'] = '-1';
    $data[':DAT_ULTIM_ATUAL'] = date('Y-m-d H:i');
    return $data;
}
if (isset($acao)) {
    if ($acao == 'cadastrar') {
        $actionExecute = new PessoasBanco($_POST, 'cadastrar');
        echo $actionExecute->cadastrarPessoa();
    }
    if ($acao == 'deletar') {

        $actionExecute = new PessoasBanco($_POST, 'deletar');
        echo $actionExecute->deletarPessoa();
    }
    if ($acao == 'verificar') {
        $actionExecute = new PessoasBanco($_POST, 'verificar');
        echo $actionExecute->verificarPessoas();
    }
}

Php connection

public function execute(PDOStatement $stmt, array $data = null) {
    try {
        if (isset($data)) {
            $stmt->execute($data);
        } else {
            $stmt->execute();
        }
    } catch (PDOException $exc) {
        error_log($exc->getTraceAsString());
        var_dump($exc->getMessage());
    }
}

/** UPDATE OR INSERT DATA 

 * */
public function save($sql, array $data) {

    $con = self::getConnection();

    $stmt = $con->prepare($sql);

    $this->execute($stmt, $data);


    //error_log($stmt->rowCount());

    if ($stmt->rowCount()) {
         error_log("Entrou no row cont da conexao");
        return true;
    } else {
        return false;
    }
}

The Query that returns(Insert called in save():

INSERT INTO tbl_PESSOAS (
COD_IDENT_PESSO,
COD_IDENT_CELUL,
COD_IDENT_IGREJ,
TXT_NOMEX_PESSO,
TXT_APELI_PESSO,
TXT_FONEX_PESSO,
DAT_NASCI_PESSO,
TXT_NASCI_PESSO,
TXT_NATUR_PESSO,
FLG_SEXOX_PESSO,
FLG_ESTAD_CIVIL,
FLG_IDENT_PESSO,
TXT_EMAIL_PESSO,
TXT_SENHA_USUAR,
TXT_ENDER_CEPXX,
SGL_ENDER_ESTAD,
TXT_ENDER_CIDAD,
TXT_ENDER_BAIRR,
TXT_ENDER_LOGRA,
TXT_ENDER_NUMER,
TXT_ENDER_COMPL,
BLO_FOTOX_PESSO,
COD_IDULT_ATUAL,
DAT_ULTIM_ATUAL)
VALUES (
:COD_IDENT_PESSO,
:COD_IDENT_CELUL,
:COD_IDENT_IGREJ,
:TXT_NOMEX_PESSO,
:TXT_APELI_PESSO,
:TXT_FONEX_PESSO,
:DAT_NASCI_PESSO,
:TXT_NASCI_PESSO,
:TXT_NATUR_PESSO,
:FLG_SEXOX_PESSO,
:FLG_ESTAD_CIVIL,
:FLG_IDENT_PESSO,
:TXT_EMAIL_PESSO,
:TXT_SENHA_USUAR,
:TXT_ENDER_CEPXX,
:SGL_ENDER_ESTAD,
:TXT_ENDER_CIDAD,
:TXT_ENDER_BAIRR,
:TXT_ENDER_LOGRA,
:TXT_ENDER_NUMER,
:TXT_ENDER_COMPL,
:BLO_FOTOX_PESSO,
:COD_IDULT_ATUAL,
:DAT_ULTIM_ATUAL)

The Array that will pass the values to sql.

Array\n(\n
    [0] => null\n
    [1] => AbC12\n
    [2] => IBA\n
    [3] => Felipe Nunes\n
    [4] => felipe\n
    [5] => 3188488185\n
    [6] => 1993/01/21\n
    [7] => null\n
    [8] => null\n
    [9] => M\n
    [10] => null\n
    [11] => A\n
    [12] => [email protected]\n
    [13] => 555\n
    [14] => 35182392\n
    [15] => MG\n
    [16] => Timoteo\n
    [17] => Timoteo\n
    [18] => Rua 8\n
    [19] => 225\n
    [20] => \n
    [21] => null\n
    [22] => -1\n
    [23] => 2015-08-27 12:53\n
)\n

1 answer

1


SQLSTATE[HY093]: Invalid Parameter number: Parameter was not defined

Now the error has changed if compared to the first version of the question.

When using named placeholder(:valor) the array passed in the execute() with values must have keys the names of named placeholders.

    $keys = array_keys($this->data[$this->action]);//obtem as chaves
    $inputs = implode(', ', $keys);
    $data = array_values($this->data[$this->action]);
   //obtem apenas os valores, um array de indices numericos sera gerado.

To resolve this, pass the names of named placehords in the keys of $data or replace them with questions, so each value will be replaced by the question in that position, as in reply

  • Cara gave it right, thank you very much. We fought 3 days and we came to a solution. Thank you

  • @Renanrodrigues I’m glad that this solves the problem :D, here has other tips on Prepared statements and Pdo in general.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.