sql - update only changed values in form to multiple tables

Question

This question is relevant with this question here

Lets say I have fetched values from multiple tables to a form, and want to change one or more inputs ie. phone number or address.

So here is my select query:

SELECT c.*, u.username
FROM   client c
JOIN   users u ON u.id = c.credid
WHERE  credid = :id

Considering the linked question (and answer) above, how could I make prepared update query for values that have CHANGED? My tables are InnoDB.

EDIT: I need to put username to users table and all else to clients table. (clients table field credid is foreign key to users table primary key id)

<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<?php
echo 'Username: <input type="text" name="1" value="' . $getuserinfo['username'] . '" /><br>';
echo 'Client: <input type="text" name="2" value="' . $getuserinfo['company'] . '" /><br>';
echo 'Address: <input type="text" name="3" value="' . $getuserinfo['address1'] . '" /><br>';
echo 'Address 2: <input type="text" name="4" value="' . $getuserinfo['address2'] . '" /><br>';
echo 'ZIP: <input type="text" name="5" value="' . $getuserinfo['zip'] . '" /><br>';
echo 'City: <input type="text" name="6" value="' . $getuserinfo['city'] . '" /><br>';
echo 'Country: <input type="text" name="7" value="' . $getuserinfo['country'] . '" /><br>';
echo 'E-mail: <input type="text" name="8" value="' . $getuserinfo['email'] . '" /><br>';
echo 'Phone number: <input type="text" name="9" value="' . $getuserinfo['phone'] . '" /><br>';
?>

    <input type="submit" name="submit" value="Save " /><br>
</form>

EDIT: I would like to construct the sql somewhat like this.

UPDATE c.(name, address, zip, email, phone, etc.),u.username 
VALUES (:1, :2, :3, :etc) 
FROM client c 
JOIN users u ON u.id = c.credid 
WHERE credid = :id

Is this anywhere near? Or maybe something like this:

UPDATE users,client
SET users.username = :username,
    client.value1 = :value1,
    client.value2 = :value2,
    etc...

 WHERE client.credid=users.id

Answer 1

What you'll need to do to start with is rename you inputs to the name of the table columns e.g.

foreach ($getuserinfo as $key => $val) {

    echo ucfist($key).': <input type="text" name="'.$key.'" value="' . $val . '" /><br>';
}

The above won't give you the exact labels but it's easy enough to add an if statement in to change that.

After you initially get the information add it to the session array to check if anything has changed

$_SESSION = $getuserinfo;

Then after the form is posted back (remember this doesn't contain any validtion apart from checking if the index already exists in the $_SESSION)

if ($_POST['username'] != $_SESSION['user_edit_info']['username']) {

    //run validation and query to update username
}

$posted = $_POST;
unset($posted['username']);

$sql_array = array();

$params = array();

foreach ($posted as $key => $val) {

    //This should prevent extra fields being posted
    if (isset($_SESSION['user_edit_info'][$key]) && $_SESSION['user_edit_info'][$key] != $val) {

        $sql_array[] = "$key=:$key";
        $params[$key] = $val;
    }
}

if (!empty($sql_array)) {

    $params['id'] = $_SESSION['user_id']; //or whatever you set it to in your session

     //I don't know exactly how you're running your PDOs but below should at least show
     //what you need to do
    ('UPDATE client SET '.implode(',', $sql_array).' WHERE credid=:id', $params);
}

//finally you don't need this anymore so just unset it
unset($_SESSION['user_edit_info']);

Hope this helps!