Ramin K Ramin K - 4 months ago 14
MySQL Question

What is the good practice for Updating sql table partially and keeping unedited values?

Okay guys, basically what i am trying to ask is this: I have a profile page on my website. Now, user enters first and last name. Later on they want to update their first name for example. Well, UPDATE syntax in a single query would update the whole field for that id right?

What I am trying to do is to hold on to the last name original value even if the table is updated for first name. I was thinking something like this would work.

if(isset($_POST['submit']) && !empty($_POST['fname'])) {
$firstname = $_POST['fname'];
$db->query('UPDATE users SET fname = :firstname WHERE id = :id');
$db->bind(':fname', $firstname);
$db->execute();
}

if(isset($_POST['submit']) && !empty($_POST['lname'])) {
$lastname = $_POST['lname'];
$db->query('UPDATE users SET lname = :lastname WHERE id = :id');
$db->bind(':lname', $lastname);
$db->execute();
}


i know this may sound silly to make a query for each field but I wonder if it works. And no i haven't tried this because I don't want to mess up my current code.

Thanks.

Answer

If I understand it right you can use just on UPDATE command to do so.

if( isset($_POST['submit']) ) {
    $firstname = $_POST['fname']; 
    $lastname = $_POST['lname'];   
    $db->query('UPDATE users 
                   SET fname = COALESCE(:firstname,fname),
                       lname = COALESCE(:lastname,lname)
                 WHERE id = :id');
    $db->bind(':fname', $firstname);
    $db->bind(':lname', $lastname);
    $db->bind(':id', $_POST['id']); //it seems you forget the ID
    $db->execute();
}

Using the COALESCE function you only change the value of a field if it is not null, if it is it will use the same value that is on the database.

The UPDATE will only update the fields that you specify on the SET clause, every other field will remain with the same value.

Your code actually only are missing the bind for the ID value. It will actually work as you want. The code I suggested is an improvement to only run one update command instead of two.