Problem with dynamic PDO UPDATE

Asked

Viewed 200 times

4

I’m creating a CRUD dynamic in , where I receive several parameters within an array, and within that array there may be several other arrays. The problem is appears when I am mounting SQL.

I asked a similar question here, but the solution was not standardized, it is accusing error:

#5 {main}string(100) "SQLSTATE[HY093]: Invalid Parameter number: number of bound variables does not match number of tokens"

My PHP code is like this:

  $campos = json_decode($item->TXT_COLUN_SINCR, true);
  $campos = implode(' = ?, ', array_keys($campos));
  $valores = json_decode($item->TXT_COLUN_SINCR, true);
  $sql = sprintf("UPDATE %s SET %s %s ", $item->TXT_TABLE_SINCR, $campos, $item->TXT_WHERE_SINCR);
  $sincronismo = $this->conexao->save($sql, $valores);

My $fields array:

Array
(
    [COD_IDENT_IGREJ] => IBM
    [COD_IDENT_CELUL] => 1
    [COD_IDENT_PESSO] => 120151202162837
    [DAT_INICI_PARTC] => 0000-00-00
    [FLG_IDENT_PESSO] => M
    [FLG_STATU_PARTC] => A
    [DAT_FINAL_PARTC] => 
    [MEM_OBSRV_ADCNS] => 
    [COD_IDULT_ATUAL] => 1
    [DAT_ULTIM_ATUAL] => 2015-12-05 15:14:26
    [COD_CELUL_PESSO] => 4
)

Details for parameter error:

Array 
( 
[COD_IDENT_IGREJ] => IBM 
[COD_IDENT_CELUL] => 1 
[COD_IDENT_PESSO] => 120151202162837 
[DAT_INICI_PARTC] => 0000-00-00 
[FLG_IDENT_PESSO] => M 
[FLG_STATU_PARTC] => A 
[DAT_FINAL_PARTC] => 
[MEM_OBSRV_ADCNS] => 
[COD_IDULT_ATUAL] => 1 
[DAT_ULTIM_ATUAL] => 2015-12-05 15:14:26 
[COD_CELUL_PESSO] => 4 
) 
PDOStatement Object 
( 
[queryString] => UPDATE tbl_PESSOA_CELULA SET COD_IDENT_IGREJ = ?, COD_IDENT_CELUL = ?, COD_IDENT_PESSO = ?, DAT_INICI_PARTC = ?, FLG_IDENT_PESSO = ?, FLG_STATU_PARTC = ?, DAT_FINAL_PARTC = ?, MEM_OBSRV_ADCNS = ?, COD_IDULT_ATUAL = ?, DAT_ULTIM_ATUAL = ?, COD_CELUL_PESSO= ? WHERE COD_IDENT_PESSO = '120151202162837' and COD_IDENT_CELUL = '1' and COD_IDENT_IGREJ = 'ibm' 
)
php sql pdo
  • 1

    What mistake you get ?

  • #5 {main}string(282) "SQLSTATE[42000]: Syntax error or access Violation: 1064 You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near ''ibm' '120151202162837'"

  • Only that actually the problem is when mounting the query

  • Do the following in the method save, comments on the lines that follow the $stmt = $con->prepare($sql);, and puts a var_dump($stmt).

  • The result is that sql I put up. The problem is happening when I’m generating sql. I will comment on the line, I know it is but I do not know how to solve.

  • He’s riding sql like this UPDATE tbl_PESSOAS SET VALOR 1, VALOR 2, :solucao1, solucao2 should look like this UPDATE tbl_PESSOA SET valor 1 = :solucao1, valor 2 = :solucao2 But this is what I’m failing to develop.

  • $campos = explode(',', $campos); $binds = explode(',', $binds); and then do this var_dump(array_combine($binds, $campos)).

  • OK I will test, that to make an answer in case anyone has doubt ?

  • It would be unnecessary to answer without being sure that this was the solution. I’ll wait for your return, and then we’ll see if this is the solution or if there’s more.

  • It really didn’t work

  • @Edilson I edited the question for you to see the result

  • 1

    You are riding wrong.

  • Yes, the difficulty is there

  • Vale remembers that I already have an array where I have the [VALUE] = SOLUTION

  • As I described in the previous question of my example. Take a look at it.

  • The select syntax is wrong, the problem is neither PHP nor PDO. Try to select manually directly in DB.

  • The way this is going is kind of tricky to work, adding pieces of code where there shouldn’t even be more code, try this - http://pastebin.com/bhsbVxT8

  • What is the PK(field) to update?

Show 13 more comments

1 answer

4


The problem is the syntax that is wrong, the update should be:

UPDATE tabela SET campo = valor, campo = valor WHERE id = id

It is possible to reshape the code, the idea is to cut the first element of the array (I believe the primary key) and use the implode() to generate the string in the format campo = ?,.

I changed the name $json for $campos

$campos = json_decode($item->TXT_COLUN_SINCR, true);
$pk = array_slice($valores, 0, 1); //extrai a pk e seu valor
$campos = implode(' = ?, ', array_keys($campos));
$where  = sprintf("%s = %s", key($pk), $pk['COD_IDENT_IGREJ']);
$sql = sprintf("UPDATE %s SET %s WHERE %s ", 'tabela', $campos, $where);

Browser other questions tagged php sql pdo

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