PHP PDO updating table

Question

so I was following a Udemy course and then the instructor made this function

public function update($table, $user_id, $fields = array()){

  $columns = '';
  $i       = 1;

  foreach($fields as $name => $value){

    $columns .= "'{$name}' = :{$name}";
    if($i < count($fields)){
        $columns .= ', ';
    }
    $i++;
  }

  $sql = "UPDATE {$table} SET {$columns} WHERE 'user_id' = {$user_id}";
  if($stmt = $this->pdo->prepare($sql)){
    foreach($fields as $key => $value){
        $stmt->bindValue(':'.$key, $value);
    }
    $stmt->execute();
  }

}

And I wrote it literally so many times after him and it just never seemed to work, would someone explain for me what's wrong with the code?

Do [this](https://stackoverflow.com/a/3999871/2595450) and update your question with the error — Spoody, Jan 27 '18 at 01:52

score 0 · Accepted Answer · answered Jan 27 '18 at 02:03

0

here

$sql = "UPDATE {$table} SET {$columns} WHERE 'user_id' = {$user_id}";

the ' around the table name should be backticks, not single quotes:

$sql = "UPDATE {$table} SET {$columns} WHERE `user_id` = {$user_id}";

same here:

$columns .= "`{$name}` = :{$name}";

answered Jan 27 '18 at 02:03

Jeff

6,895
1
15
33

next time get the error messages as @Mehdi Bounya pointed out! – Jeff Jan 27 '18 at 02:08
sure, would you tell me what do you think of this function tho? is it the best way to do it? or is there's something better I can do? – A. Maher Jan 27 '18 at 02:23
1

it will fail if you don't pass a filled fields-array. You need to check that first. Also check for errors and return true or false, or the number of rows affected. – Jeff Jan 27 '18 at 02:30

score 0 · Answer 2 · 2018-01-27T02:15:35.383

0

You should replace single quotes with backticks:

$columns .= "`{$name}` = :{$name}";

and

$sql = "UPDATE {$table} SET {$columns} WHERE `user_id` = {$user_id}";

Just a suggestion: You could use an array to build the columns clause and you could directly execute without any bindValue, by just passing the $fields array:

function update($table, $user_id, $fields = array()) {

    $columns = [];

    foreach ($fields as $name => $value) {
        $columns[] = "`{$name}` = :{$name}";
    }

    $sql = sprintf('UPDATE %s SET %s WHERE `user_id` = %s'
            , $table
            , implode(', ', $columns)
            , $user_id
    );

    if ($stmt = $this->pdo->prepare($sql)) {
        $stmt->execute($fields);
    }
}

edited Jan 27 '18 at 02:15

answered Jan 27 '18 at 02:04

Oh wow thanks, I was honestly confused, I didn't really know when to use this bindParma() and bindValue() functions and when do i just bind it in the execute function – A. Maher Jan 27 '18 at 02:20
@A.Maher You are welcome. Better look into [this example](https://stackoverflow.com/a/5077108/7941334) to discover the difference btw. `bindParam` and `bindValue` once and for all ;-) – Jan 27 '18 at 02:34
@A.Maher And apply the error handling as in [this answer](https://stackoverflow.com/a/32648423/7941334). Good luck. – Jan 27 '18 at 02:43

PHP PDO updating table

2 Answers2