Insert Mysql PDO

Question

Insert Mysql PDO

Asked 9 years, 2 months ago

Viewed 114 times

4

Well, I need to do an insert and I use PHP OO, but how do I do this? According to my code is registering, but is not working perfect.

I’m doing something wrong ? Do not give any error message, just say you are successfully registered!

        try {
                   $sql = $conn->prepare("INSERT INTO tbl_PESSOAS (
                                       COD_IDENT_IGREJ,
                                       TXT_NOMEX_PESSO,
                                       TXT_FONEX_PESSO,
                                       DAT_NASCI_PESSO,
                                       FLG_SEXOX_PESSO,
                                       FLG_IDENT_PESSO,
                                       TXT_EMAIL_PESSO,
                                       TXT_SENHAX_USUAR,
                                       TXT_ENDER_CEPXX,
                                       TXT_ENDER_BAIRR,
                                       TXT_ENDER_LOGRA,
                                       TXT_ENDER_NUMER,
                                       TXT_ENDER_COMPL,
                                       COD_IDULT_ATUAL,
                                       DAT_ULTIM_ATUAL)
                               VALUES (:COD_IDENT_IGREJ,
                                       :TXT_NOMEX_PESSO,
                                       :TXT_FONEX_PESSO,
                                       :DAT_NASCI_PESSO,
                                       :FLG_SEXOX_PESSO,
                                       :FLG_IDENT_PESSO,
                                       :TXT_EMAIL_PESSO,
                                       :TXT_SENHAX_USUAR,
                                       :TXT_ENDER_CEPXX,
                                       :TXT_ENDER_BAIRR,
                                       :TXT_ENDER_LOGRA,
                                       :TXT_ENDER_NUMER,
                                       :TXT_ENDER_COMPL,
                                       :COD_IDULT_ATUAL,
                                       :DAT_ULTIM_ATUAL)");

        $collection = array(
            'COD_IDENT_IGREJ' => $codIgreJ,
            'TXT_NOMEX_PESSO' => $lbl_NOME,
            'TXT_FONEX_PESSO' => $lbl_TELEFONE,
            'DAT_NASCI_PESSO' => $dte_ANIVERSARIO,
            'FLG_SEXOX_PESSO' => $lbl_SEXO,
            'FLG_IDENT_PESSO' => $lbl_FLAG_IDENT,
            'TXT_EMAIL_PESSO' => $lbl_EMAIL,
            'TXT_SENHAX_USUAR' => $lbl_SENHA,
            'TXT_ENDER_CEPXX' => $lbl_CEP,
            'TXT_ENDER_BAIRR' => $lbl_BAIRRO,
            'TXT_ENDER_LOGRA' => $lbl_RUA,
            'TXT_ENDER_NUMER' => $lbl_NUMERO,
            'TXT_ENDER_COMPL' => $lbl_COMPLEMENTO,
            'COD_IDULT_ATUAL' => $lbl_COD_ID,
            'DAT_ULTIM_ATUAL' => $lbl_NOW
        );

        $sql->execute($collection);
        /* $UltAiAdd = $conn->lastInsertId();

          $sql2 = $conn->prepare("INSERT INTO tbl_PESSOA_CELULA VALUES ( COD_IDENT_PESSO =  :COD_IDENT_PESSO, COD_IDENT_CELUL = :COD_IDENT_CELUL, FLG_IDENT_PESSO = :FLG_IDENT_PESSO, "
          . "COD_IDULT_ATUAL = :COD_IDULT_ATUAL, DAT_ULTIM_ATUAL = :DAT_ULTIM_ATUAL)");
          $sql2->bindParam(":COD_IDENT_PESSO", $UltAiAdd);
          $sql2->bindParam(":COD_IDENT_CELUL", $codCel);
          $lbl_FLAG_PE = 'M';
          $sql2->bindParam(":FLG_IDENT_PESSO", $lbl_FLAG_PE);
          $sql2->bindParam(":COD_IDULT_ATUAL", $lbl_COD_ID);
          $sql2->bindParam(":DAT_ULTIM_ATUAL", $lbl_NOW);

          $sql->execute(); */
    } catch (Exception $exc) {
        error_log($exc->getMessage());
        echo "058"; //Erro no cadastro.
    }
    echo "057"; //Cadastrado com sucesso!

What is not working perfect, can show us?

– Ivan Ferrer

2015/08/24 at 14:12
Just not nothing. No error, but when I go to the data table it is not registered.

– Renan Rodrigues

2015/08/24 at 14:13
instead of using bindParam, try to pass everything as a direct array in execute: $collection[] = ['COD_IDENT_PESSO", $UltAiAdd]; $sql->execute($collection);

– Ivan Ferrer

2015/08/24 at 14:16
It is that the good bindParam you have to treat the data type.

– Ivan Ferrer

2015/08/24 at 14:19
Let’s go continue this discussion in chat.

– Renan Rodrigues

2015/08/24 at 14:20
you need to make the PDO imprint the error ... $sql->execute() ? print 'sucesso' : print_r($sql->errorInfo()); do the same to $sql2

– rray

2015/08/24 at 14:21
Take a look at her

– Renan Rodrigues

2015/08/24 at 14:24
I do not understand php, but I would inser so:"INSERT INTO tbl_NomedaTabela (campo1, campo2 VALUES (:valor1, :valor2)"

– emanuelsn

2015/08/24 at 14:24
lacked kinship in $sql->errorInfo() ... I forgot to put.

– rray

2015/08/24 at 14:25
@rray is not working the command.

– Renan Rodrigues

2015/08/24 at 14:29
I need the error to appear on the console as it would ?

– Renan Rodrigues

2015/08/24 at 14:30
Since there are two Inserts a referential integrity possibility, the value entered(id) in the second table does not exist pq the first Insert failed either by a constraint or any other reason.

– rray

2015/08/24 at 14:32
If this calling this Insert via ajax, you need to take the return of ajax and give a console.log() in your javascript.

– rray

2015/08/24 at 14:33
the $sql->errorInfo() returned some message?

– rray

2015/08/24 at 17:15
Had no log, continued the same way.

– Renan Rodrigues

2015/08/24 at 17:18
This error says that there are more columns than values in Insert, there must be some wrong placeholder.

– rray

2015/08/24 at 17:18

Show 11 more comments

2 answers

1

The problem is that you were mixing the UPDATE structure with the insertion of the query:

To Insert use:

INSERT INTO TABELA (campo1, campo2) VALUES (:campo1, :campo2)

For update use:

UPDATE TABELA SET campo1=?, campo2=? WHERE campo_where:campo_where

    $stmt = $conn->prepare("INSERT INTO tbl_PESSOAS (
                                           COD_IDENT_IGREJ,
                                           TXT_NOMEX_PESSO,
                                           TXT_FONEX_PESSO,
                                           DAT_NASCI_PESSO,
                                           FLG_SEXOX_PESSO,
                                           FLG_IDENT_PESSO,
                                           TXT_EMAIL_PESSO,
                                           TXT_SENHAX_USUAR,
                                           TXT_ENDER_CEPXX,
                                           TXT_ENDER_BAIRR,
                                           TXT_ENDER_LOGRA,
                                           TXT_ENDER_NUMER,
                                           TXT_ENDER_COMPL,
                                           COD_IDULT_ATUAL,
                                           DAT_ULTIM_ATUAL)
                                   VALUES (:COD_IDENT_IGREJ,
                                           :TXT_NOMEX_PESSO,
                                           :TXT_FONEX_PESSO,
                                           :DAT_NASCI_PESSO,
                                           :FLG_SEXOX_PESSO,
                                           :FLG_IDENT_PESSO,
                                           :TXT_EMAIL_PESSO,
                                           :TXT_SENHAX_USUAR,
                                           :TXT_ENDER_CEPXX,
                                           :TXT_ENDER_BAIRR,
                                           :TXT_ENDER_LOGRA,
                                           :TXT_ENDER_NUMER,
                                           :TXT_ENDER_COMPL,
                                           :COD_IDULT_ATUAL,
                                           :DAT_ULTIM_ATUAL)");

$collection = array(
  ':COD_IDENT_IGREJ' => $codIgreJ,
  ':TXT_NOMEX_PESSO' => $lbl_NOME,
  ':TXT_FONEX_PESSO' => $lbl_TELEFONE,
  ':DAT_NASCI_PESSO' => $dte_ANIVERSARIO,
  ':FLG_SEXOX_PESSO' => $lbl_SEXO,
  ':FLG_IDENT_PESSO' => $lbl_FLAG_IDENT,
  ':TXT_EMAIL_PESSO' => $lbl_EMAIL,
  ':TXT_SENHAX_USUAR'=> $lbl_SENHA,
  ':TXT_ENDER_CEPXX' => $lbl_CEP,
  ':TXT_ENDER_BAIRR' => $lbl_BAIRRO,
  ':TXT_ENDER_LOGRA' => $lbl_RUA,
  ':TXT_ENDER_NUMER' => $lbl_NUMERO,
  ':TXT_ENDER_COMPL' => $lbl_COMPLEMENTO,
  ':COD_IDULT_ATUAL' => $lbl_COD_ID,
  ':DAT_ULTIM_ATUAL' => $lbl_NOW
);
$conn->execute($stmt, $collection);
$UltAiAdd = $conn->lastInsertId();

I’ll try in a little while, I’ll post the result, thank you for now.

– Renan Rodrigues

2015/08/24 at 14:32
1

I think the way to declare the abbreviated array was in 5.4

– rray

2015/08/24 at 14:38
[Mon Aug 24 10:55:39 2015] [error] [client 192.168.1.105] PHP Parse error: syntax error, Unexpected '[' in /opt/lampp/htdocs/Renan/pdo_acao.php on line 47, referer: http://127.0.0.1:58889/http-services/Emulator-webserver/ripple/userapp/x/C/Users/hotsystems/Appdata/Local/XDK/xdk-scratchdir/b9796bb6-a6c6-4479-94a3-7154a6043f83/Platforms/android/Assets/www/index.html

– Renan Rodrigues

2015/08/24 at 16:16
Made this mistake...

– Renan Rodrigues

2015/08/24 at 16:16
I updated the question, from a look there.

– Renan Rodrigues

2015/08/24 at 16:33
@Renanrodrigues, sorry, there was an error, see if it works now. The values were duplicated the wrong way.

– Ivan Ferrer

2015/08/24 at 16:47
[Mon Aug 24 11:37:52 2015] [error] [client 192.168.1.105] PHP Parse error: syntax error, Unexpected '[' in /opt/lampp/htdocs/Renan/pdo_acao.php on line 47, referer: http://127.0.0.1:58889/http-services/Emulator-webserver/ripple/userapp/x/C/Users/hotsystems/Appdata/Local/XDK/xdk-scratchdir/b9796bb6-a6c6-4479-94a3-7154a6043f83/Platforms/android/Assets/www/index.html

– Renan Rodrigues

2015/08/24 at 16:59
Switch to the array() traditional. and try again.

– Ivan Ferrer

2015/08/24 at 17:24
Some fields were missing, as @rray pointed out.

– Ivan Ferrer

2015/08/24 at 17:37
Test and see if it stopped giving error.

– Ivan Ferrer

2015/08/24 at 17:52
Okay, but now the problem of not registering.

– Renan Rodrigues

2015/08/24 at 17:53
Let’s go continue this discussion in chat.

– Ivan Ferrer

2015/08/24 at 17:54

Show 7 more comments

Browser other questions tagged mysql pdo

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

by rray • **66,288** points · Answer 1 · 2015-08-24T17:25:01+00:00

1

SQLSTATE[HY093]: Invalid Parameter number: number of bound variables does not match number of tokens in

It means that in Insert there are more columns specified than values, in case you have a bind that is not mapped to a column that is :TXT_FONEX_PESSO

But the mistake is not this because I fixed and conitnua

– Renan Rodrigues

2015/08/24 at 17:30
Still the same error message? @Renanrodrigues

– rray

2015/08/24 at 17:34
yes. log error is the memso

– Renan Rodrigues

2015/08/24 at 17:35
I updated it again and the error remains what it could be ? Is there any easier way for me to develop this Sert ? The interesting thing is that the select worked perfectly.

– Renan Rodrigues

2015/08/24 at 17:50
Has a bind that is XT_SENHAX_USUAR, it seems that it lacked a T before. @Renanrodrigues

– rray

2015/08/24 at 17:52