PHP: update through array

I have a problem with the following code.

index.php

$table  = 'test';
$data = [
    'gerecht' => 'Spaghettie',
    'omschrijving' => 'Spaghettie van het huis!'
];
$id = '3';
$crud->updateData($table,$data,$id);

crud.class.php

    public function updateData($table,$data,$id){
    //Query opbouwen
    $query = "UPDATE {$table} SET "; 

    $last_key = end(array_keys($data));
    foreach($data as $key => $value)
    {
        $query .= $key ."=:". $key;
        if ($key == $last_key) {
            $query .= " ";
        } else {
            $query .= ", ";
        }
    }

    $query .= "WHERE id=:id";
    //Query opgebouwd

    $stmt = $this->db->prepare($query);

    foreach($data as $key => $value){
        $stmt->bindparam(":". $key,$value);
    }

    $stmt->bindparam(":id",$id);
    $stmt->execute();

    if($stmt->rowCount()){
        return true; 
    } 
return false;
}

The code “works” but it doesnt do it the right way. It does update at the requested id in the correct table, but the values are both the same.

The data base contains 2 cols (gerecht and omschrijving) and the values that will be updated to the database a both the values “omschrijving”

So whatever i put in gerecht wil be overwriten bij the value omschrijving .

Is there also maybe a more simple way for this code to work ?

Best regards Jocem

Answer

Try followed:

  public function updateData($table,$data,$id){
    $set = [];

    foreach($data as $key => $value)
    {
        $set[] = "$key = :$key";
    }
    $set = implode(', ', $set);
    $query = "UPDATE $table SET $set WHERE id=:id";
    //Query opgebouwd

    $stmt = $this->db->prepare($query);

    foreach($data as $key => $value) {
        $stmt->bindparam(":$key", $data[$key]);
    }

    $stmt->bindparam(":id", $id);
    $stmt->execute();

    return (bool) $stmt->rowCount();
  }

I think, that issue can be with $value variable binding, this code binds placeholders to array members.