PHP: update through array

Question

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

I tested it with the dollowing code <?php $data = [ 'gerecht' => 'Spaghettie', 'omschrijving' => 'Spaghettie van het huis!' ]; $table = "test"; $id = "3"; //////////////////////////////////// $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 </br>"; //Query opgebouwd foreach($data as $key => $value){ print "stmt->bindparam(:$key,$value)</br>"; } ?> — Jochem
– Jochem, Commented Nov 20, 2015 at 15:19
Correct cause the query should be ' UPDATE test SET gerecht=:gerecht, omschrijving=:omschrijving WHERE id=:id ' — Jochem
– Jochem, Commented Nov 20, 2015 at 15:25
I once has used this method to dynamically to set up query, but I found it is too restrict and lack of flexibility and go for hard code :( — Andrew
– Andrew, Commented Nov 20, 2015 at 15:27

vp_arth · Accepted Answer · 2015-11-20 15:35:24Z

1

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.

answered Nov 20, 2015 at 15:35

vp_arth

15k4 gold badges43 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jochem Over a year ago

Well you sir , are my hero ;) That did the trick indeed! Thanks for the help !

ins0 · Accepted Answer · 2015-11-20 15:41:24Z

1

When dealing with comma seperated strings i suggest the function implode as this function will take care of not filling , at the end of your array so you don't have to.

http://php.net/manual/en/function.implode.php

Example

$table = 'test';
$data = [
    'gerecht' => 'Spaghettie',
    'omschrijving' => 'Spaghettie van het huis!',
];

$updateString = implode(',', array_map(function ($key){
    return sprintf("%s = :%s", $key, $key);
}, array_keys($data)));

$sqlString = sprintf('UPDATE %s SET %s WHERE id = :id', $table, $updateString);

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

array_map(function ($key) use ($data, $stmt) {
    $stmt->bindparam(sprintf(':%s', $key), $data[$key]);
}, array_keys($data));

...bind id, execute whatever

edited Nov 20, 2015 at 15:41

answered Nov 20, 2015 at 15:37

ins0

3,9281 gold badge22 silver badges28 bronze badges

3 Comments

vp_arth Over a year ago

why you use sprintf for set clause, but not for table?

ins0 Over a year ago

whoops my bad :) i will change it, ty

vp_arth Over a year ago

now looks ok) Main issue(reuse of loop temp variable) was fixed here too.

Kenney · Accepted Answer · 2015-11-20 15:25:17Z

0

Here's a slightly smaller version of your code:

public function updateData($table,$data,$id) {

    $query = "UPDATE $table SET "
    . implode( ', ',
      array_map( function($key) { return "$key=?"; }, array_keys( $data ) )
    )
    . " WHERE id=?";

    $stmt = $this->db->prepare($query);
    $stmt->execute( array_merge( array_values( $data ), [$id] ) );

    return $stmt->rowCount() != 0;
}

edited Nov 20, 2015 at 15:25

answered Nov 20, 2015 at 15:20

Kenney

9,11317 silver badges21 bronze badges

1 Comment

Rizier123 Over a year ago

1) foreach uses a different pointer 2) $last_key = end(array_keys($data)); should throw OP a strict standard notice

Collectives™ on Stack Overflow

PHP: update through array

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related