Update only provided fields

Question

I have this:

if(isset($_POST["Submit"])) {
$updatequery = @mysql_query("UPDATE users SET FirstName='".$_POST['firstname']."', LastName='".$_POST['lastname']."', Address='".$_POST['address']."', City='".$_POST['city']."', PostalCode='".$_POST['postalcode']."', HomePhone='".$_POST['homephone']."', AltPhone='".$_POST['altphone']."', HomeWebsite='".$_POST['homewebsite']."', EmailAddress='".$_POST['email']."', Paypal='".$_POST['paypal']."', Alertpay='".$_POST['alertpay']."', Payoneer='".$_POST['payoneer']."' WHERE Username = '".$_SESSION['Username']."'");</i>

Now unfortunately when a user updates their info (it's a profile page), it updates everything which means if I left the Paypal email empty (because I already had it there) it updates the Paypal email with emptiness.

How do I solve this?

First, you stop working on this code and learn about [SQL injection attacks](http://bobby-tables.com). — Marc B, Oct 04 '12 at 19:41
You should never, ever put un-sanitised user input into a query. — MrLore, Oct 04 '12 at 19:42

score 0 · Answer 1 · answered Oct 04 '12 at 19:43

0

Loop through the fields, building up an UPDATE statement dynamically.

Pay attention to the corner case where the user erases the value.

answered Oct 04 '12 at 19:43

Alain Collins

16,268
2
32
55

I am not sure how to do this, could you please explain? – Jesse J. Oct 04 '12 at 19:44
That's a PHP problem, but maybe something like this: `if( isset($_POST['firstname'])) { $updatequery .= " firstname = " . $_POST['firstname'] } And, assuming your original code was not just a simplified example, pay attention to the SQL injection comments, above. – Alain Collins Oct 04 '12 at 19:46

Teena Thomas · Answer 2 · 2012-10-04T20:21:18.963

0

$sql = '';
if( !empty($_POST['email']) )  //if value is not empty, then update        
      $sql = ", EmailAddress = '{$_POST['email']}'";

Then in the query,

 if(isset($_POST["Submit"])) {
 $updatequery = @mysql_query("UPDATE users SET FirstName='".$_POST['firstname']."',     LastName='".$_POST['lastname']."', Address='".$_POST['address']."', City='".$_POST['city']."', PostalCode='".$_POST['postalcode']."', HomePhone='".$_POST['homephone']."', AltPhone='".$_POST['altphone']."', HomeWebsite='".$_POST['homewebsite']."' $sql , ...

edited Oct 04 '12 at 20:21

answered Oct 04 '12 at 19:47

Teena Thomas

5,139
1
13
17

I get where the query is but where does the first part go? – Jesse J. Oct 04 '12 at 20:07
after you get the `$_POST` array, anywhere above `if(isset($_POST["Submit"])) {` should work. – Teena Thomas Oct 04 '12 at 20:09
$sql = ", EmailAddress = '$_POST['email'] '"; – Jesse J. Oct 04 '12 at 20:17
there, corrected it -- `$sql = ", EmailAddress = '{$_POST['email']}'";` – Teena Thomas Oct 04 '12 at 20:20
Thanks very much :) Checking it out. – Jesse J. Oct 04 '12 at 20:25

score 0 · Answer 3 · answered Oct 04 '12 at 19:48

Like marc_b said: Watch out for SQL injection aqttacks. Then you can do something like this:

UPDATE users 
SET FirstName = case when '$firstname' = '' then FirstName else '$firstname' end, 
    LastName = ...
WHERE Username = $username

to avoid overriding values with empty ones

billyonecan · Accepted Answer · 2012-10-04T20:56:38.877

0

Just to note, this is a really basic example and I wouldn't recommend using this in a live environment, but it should get you going in the right direction:-

First, create an array mapping your column names to your $_POST variables, eg:

$array = array(
  'FirstName' => 'firstname',
  'LastName' => 'lastname',
  'Address' => 'address',
  'City' => 'city'
);

Next, loop over your newly created $array and check if the value exists in your $_POST array, like so:

  $data = array();
  foreach ($array as $key => $value) {
    if (isset($_POST[$value]) && !empty($_POST[$value]) {
      $data[] = $key . '="' . mysql_real_escape_string($_POST[$value]) . '"';
    }
  }

Notice the $data array that has been created, this stores the column = "value" data. You can then just implode the $data array into your SQL string:

if (!empty($data)) {
  $strSQL = 'UPDATE tbl_name SET ' . implode(', ', $data) . ' WHERE Username = "' . $_SESSION['Username'] . '"';
}

Which would output (with some fictional $_POST data):

UPDATE tbl_name SET FirstName="John", LastName="Doe", Address="Some Street", City="Somewhere"

Just to reiterate, I would not advise that you use this code as it is. It is imperative that you validate and sanitise the user input beforehand!

edited Oct 04 '12 at 20:56

answered Oct 04 '12 at 20:22

billyonecan

20,090
8
42
64

I'll check this out. Also, why not use it? – Jesse J. Oct 04 '12 at 20:29
I used this code and it worked perfectly! However, it does not update information lol! – Jesse J. Oct 04 '12 at 20:45
See my edit in `$strSQL` (was missing the `WHERE` clause). In response to your first comment, this is a script I threw together in a few minutes, the user input is not sanitised or validated, it is prone to errors and sql injection. – billyonecan Oct 04 '12 at 20:51
Alright it works perfectly, I was an idiot and had Email => email instead of EmailAddress => email LOL! Anyways, how should I sanitize this? – Jesse J. Oct 04 '12 at 21:05
There are plenty of questions covering this topic, [here's a good one worth reading](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) – billyonecan Oct 04 '12 at 21:12
Aaaalright, that's what "Sanitizing" is, thx very much already got it covered :) – Jesse J. Oct 04 '12 at 21:16

Update only provided fields

4 Answers4