PDO prepared statement for update doesn't work properly

Question

This is my php code:

public function update($table,$fields_and_values,$condition_field,$condition_field_value)
{
    $query="UPDATE $table SET ";
    foreach($fields_and_values as $field=>$value) $query.=($field."=:".$field." ,");
    $query.=" ";
    $query=str_replace(", "," WHERE ",$query);
    $query.=($condition_field."='".$condition_field_value."'");
    echo $query;
    $stmt=$this->conn->prepare($query);
    foreach($fields_and_values as $field=>$value) $stmt->bindParam(":".$field,$value);
    $stmt->execute();
}

and this is how i call the function in my class:

    $db=new db_connection('localhost','root','','maps');
$db->connect();
    $arr=array('username'=>'testfromnewclass3','password'=>'123456');
    $db->update('users',$arr,'username','term');
    $db->disconnect();

It doesn't matter what the other functions like disconnect do! They work correctly.
My problem is that when this command executes, both username and password become 123456 ! And this is what i get from that echo $query:

UPDATE users SET username=:username ,password=:password WHERE username='term'

Is something wrong with my function? and if so how can i fix it?

Debug your 2nd loop where you are binding values (var_dump it or something, you have to check what's the actual value being passed). Also a few tips: use `bindValue` and not `bindParam`. `bindParam` can alter the value of the variable passed, its intended use is when you have stored procedures which can return values and then those values are contained in variables you passed. Also, never disconnect from MySQL. You don't want that. You're creating overhead that way. — Mjh, Dec 30 '15 at 09:41

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

Use $stmt->bindValue($field, $value); instead of $stmt->bindParam(":".$field,$value);

Check this to understand difference between PDOStatement::bindParam() and PDOStatement::bindValue()

edited May 23 '17 at 12:15

Community

1
1

answered Dec 30 '15 at 09:42

Sazzadur Rahman

2,650
1
18
34

PDO prepared statement for update doesn't work properly

1 Answers1