Problem with dynamic UPDATE in PDO

Question

Problem with dynamic UPDATE in PDO

Navigation

#1 by (4 votes)

4

I'm creating a CRUD dynamic in pdo , where I get several parameters inside an array, and inside this array there can be several other arrays. The problem is when I'm 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 looks 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 array $ fields:

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

asked by anonymous 14.12.2015 / 18:58

1 answer

Call JS function within modal Return various attributes using XPATH

score 4 · Accepted Answer

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 it is the primary key) and use implode() to generate the string in campo = ?, format.

I changed the name $json to $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);