Menu
I have a web program which allows the administrator to update a user's information... With that being said, I only want columns updated which have indeed been 'updated'...
I have done quite a bit of researching on this and it seems that all methods use outdated querys, which do not make use of the prepare statement to escape input...
Can someone please help me with the statement?
Essentially in psuedocode:
Update FIRSTNAME if $editedUserdata['firstname'] != FIRSTNAME, LASTNAME if $editedUserData['lastname'] != LASTNAME ...etc...
Here is what I have for the post code...
$password = sha1($password);
$editedUserData = array(
'firstname' => $firstname,
'lastname' => $lastname,
'username' => $username,
'password' => $password,
'cellphone' => $cellphone,
'security_level' => $seclvl,
'email' => $email,
'direct_phone' => $direct,
'ext_num' => $extension,
'is_active' => $userflag
);
Then it should be something like
$query = $this->db->prepare('UPDATE FIRSTNAME if(?) IS NOT FIRSTNAME, LASTNAME if(?) IS NOT LASTNAME, USERNAME if (?) IS NOT USERNAME.... VALUES (:firstname, :lastname, :username).....'
if ($query -> execute($editedUserData)) {
more code....
364
MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
Updating data from PHP using PDO First, connect to the database by creating a new instance of the PDO class. Next, construct an SQL UPDATE statement to update data in a table. Then, call the prepare() method of the PDO object. The prepare() method returns a new instance of the PDOStatement class.
For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.
According to MySQL documentation - Ref: (http://dev.mysql.com/doc/refman/5.0/en/update.html)
"If you set a column to the value it currently has, MySQL notices this and does not update it."
78
Maybe I'm not understanding the problem which you're trying to solve but you don't have to test if field value did change.
If field value is "A" and you put there an "A" it will remain the same otherwise, if you put there a "B" it will be updated as expected
The prepared statement would be something like
$stmt = $dbh->prepare("
UPDATE table_name
SET
field1 = :value1,
field2 = :value2
WHERE
field0 = :key
");
$stmt->bindParam(':value1', $value1, PDO::PARAM_STR);
$stmt->bindParam(':value2', $value2, PDO::PARAM_STR);
$stmt->bindParam(':key', $key, PDO::PARAM_INT);
$stmt->execute()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
