How to make the id variable not repeat in the loop?

Question

I’m trying to get one array associative as argument of this methods, actually receives a array in the first argument and a numerical value in the second.

My question is how to make the variable $id do not repeat?

'Cause you’re making that mistake:

Warning: Pdostatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid Parameter number: number of bound variables does not match number of tokens in C: Program Files (x86) Easyphp-5.3.8.0 www connect standardModel.php on line 92

Source:

<?php
public function update($data = array(), $id)
{
    if (is_array($data) && count($data) > 0 && $id != false)
    {
        $sql = "update `{$this->table}` set ";

        foreach($data as $key => $value)
        {
            $sql .= $key . " = ?, ";
        }

        $sql = substr($sql, 0, - 2);
        $sql .= "where id = ?";

        $query = $this->db->prepare($sql);
        foreach($data as $value)
        {
            $query->bindValue($value . ",",$id);
        }

        if ($query->execute()) {
            return true;
        } else {
            return false;
        }
    }
}
?>

Answer 1

Well, I don’t know if that’s gonna help you, but I already did it once like this:

public static function update($tabela, $dados, $condicao = NULL){
    $sql = 'UPDATE '.$tabela.' SET ';
    foreach($dados as $key => $value):
        $campos[] = $key.'=?';
        $valores[] = $value;
    endforeach;
    $sql .= implode(', ', $campos);
    if(!is_null($condicao)) $sql .= ' WHERE '.$condicao;
    try{
        $query = self::$conexao->prepare($sql);
        $query->execute($valores);
    }catch(PDOException $e){
        self::erroLog(__FILE__, __FUNCTION__, $e->getMessage());
    }
}

Explaining:

$tabela i pass the table where the update will be done. $dados is my array containing the data. $condicao is where I leave as null by default to update the entire table, in your case to update a id specific you can pass it as parameter.

Ex:

update('tabelaCarros', $arrayDados, 'id=2');

Complete code on Github

Answer 2

This message appears when the query, when the number of columns is different from the number of values or is more and less columns or otherwise, in your code the way that requires less changes is to assign an input id in the $data after sql mount, a control variable $i is required to inform the question number will match what value($value)

The changed code will stay that way:

$sql = substr($sql, 0, - 2);
$sql .= " where id = ?";
$query = $this->db->prepare($sql);

$data['id'] = $id;
$i = 1; //indice atual da interrogação. 
foreach($data as $value)
{
   $query->bindValue($i, $value);
   $i++;
}

//simplificação sugerida pelo @Maniero
return $query->execute();