MySQL update query, how to skip empty values?

Question

I've got such query:

$sql = "UPDATE test_accs SET 
    acc_owner = '$owner_id', 
    acc_policy_version = '$version', 
    acc_policy_last_update = '$approved', 
    acc_policy_next_update = '$renewed' 
        WHERE acc_id = '1'";

Now, all of these values on the web folmular are optional, one can set one of these values, two, or so. Now, after I submit the form, it goes in the query like that:

UPDATE test_accs SET acc_owner = '2', acc_policy_version = '1.2', acc_policy_last_update = '2012-12-19', acc_policy_next_update = '2012-12-18' WHERE acc_id = '1'

It works only when I submit all values from the form. Can you please show me how could it work even if not all the values has been sent, just for example one of them?

When I set one value (f.ex. policy version), it looks like that:

UPDATE test_accs SET acc_owner = '', acc_policy_version = '1.2', acc_policy_last_update = '', acc_policy_next_update = '' WHERE acc_id = '1'

and it isn't working.

It might be possible cause of the acc_owner table values?

#1366 - Incorrect integer value: '' for column 'acc_owner' at row 1

Thanks in advice.

Form:

echo '<td>Change owner: <select name="owner_id" onchange="showUser(this.value)" style="font-size:9px"><option value="">Select a person:</option>';
    while($owners = mysql_fetch_array($owners_query)) { echo '<option value="'.$owners['id'].'">'.$owners['surname'].' '.$owners['name'].'</option></h2>'; } echo '</select></td>';
    echo "<td><input name='version' style='width:50px;text-align:center' placeholder='0.0' /></td>";
    echo "<td><input name='approved' class='datepicker_all' readonly='readonly' style='text-align:center' placeholder='1999-01-01' /></td>";
    echo "<td><input name='renewed' class='datepicker_all' readonly='readonly' style='text-align:center' placeholder='1999-01-01' /></td>";

no, not your form... the php that collects the values from your form. If I understand you, you want to update rows and exclude the fields left blank on the form? — IROEGBU
– IROEGBU, Commented Dec 12, 2012 at 17:10

spencer7593 · Accepted Answer · 2012-12-12 17:36:41Z

One way to accomplish this is to use an expression in the SQL statement that tests whether the supplied value is an empty string. If the supplied value is an empty string, then use the current value of the column as the value to assign to the column. Otherwise, assign the supplied value to the column.

In the example below, the each of the supplied values have to be include TWICE in the statement: once in the conditional test, and then again, as a possible result of the conditional test.

This statement:

UPDATE test_accs
   SET acc_owner              = IF('2'='',acc_owner,'2')
     , acc_policy_version     = IF('1.2'='',acc_policy_version,'1.2')
     , acc_policy_last_update = IF('2012-12-19'='',acc_policy_last_update,'2012-12-19')
     , acc_policy_next_update = IF('2012-12-18'='',acc_policy_next_update,'2012-12-18')
 WHERE acc_id = '1'

is equivalent to the first UPDATE statement in the question, in that it sets the value of all four columns to the new specified value.

This statement:

UPDATE test_accs
   SET acc_owner              = IF(''='',acc_owner,'')
     , acc_policy_version     = IF('1.2'='',acc_policy_version,'1.2')
     , acc_policy_last_update = IF(''='',acc_policy_last_update,'')
     , acc_policy_next_update = IF(''='',acc_policy_next_update,'')
 WHERE acc_id = '1'

changes ONLY the value of the acc_policy_version column, the values of the other three columns will remain unchanged.

This is not necessarily the best approach, but it is workable for some scenarios.

It's also possible to create an expression that requires each supplied value be specified in the statement one time, although I think these expressions are a little less intuitive:

   SET acc_owner          = COALESCE(NULLIF( ''     ,''),acc_owner         )
     , acc_policy_version = COALESCE(NULLIF( '1.2'  ,''),acc_policy_version)

That's essentially doing the same thing as the examples above.

If the supplied value is equal to '' (like it is for acc_owner in the example above), then the NULLIF expression will return a NULL. The COALESCE function essentially causes that NULL value to be skipped, and the current value of the column will remain unchanged (the current value of the column is assigned to the column.)

If the supplied value is not equal to '' (like it is for acc_policy_version in the example above), then the NULLIF expression will return the supplied value. The COALESCE function will pick up that value, and assign it to the column.

v0d1ch · Accepted Answer · 2012-12-12 16:58:23Z

1

Check if acc_owner is empty and set it to zero is one option, you can't insert empty space if column is supposed to hold integer - or just don't do update unless you have int value

1:

if(strlen($acc_owner)==0){
  $acc_owner=0;

}

2:

 if(is_int($acc_owner)){
  //update it
  }

answered Dec 12, 2012 at 16:58

v0d1ch

2,7481 gold badge24 silver badges27 bronze badges

Comments

jamis0n · Accepted Answer · 2012-12-12 17:11:51Z

1

Is the value for the Integer field required? If not, then check for the GET/POST value being set, and if its empty, don't include that in your update statement.

if(isset($_GET['acc_id'])) {
    $acc_id = $_GET['acc_id'];
    $sql = "UPDATE test_accs SET ";

    if(isset($_GET['version'])) {
       $version = $_GET['version'];
       $sql = $sql . "acc_policy_version = " . $version . ",";
    }

    if(isset($_GET['owner_id'])) {
       $owner_id = $_GET['owner_id'];
       $sql = $sql . "acc_owner = " . $owner_id . ",";
    }

    $sql = $sql .
        "acc_policy_last_update = '$approved', 
        acc_policy_next_update = '$renewed' 
        WHERE acc_id = " . $acc_id;

    //Execute SQL
    echo "successfully updated " . $acc_id;
} else { 
    echo "invalid acc_id"; 
}

edited Dec 12, 2012 at 17:11

answered Dec 12, 2012 at 17:04

jamis0n

3,8508 gold badges39 silver badges52 bronze badges

1 Comment

Pawel Over a year ago

Yeah! That kinda looks like what I've needed, I'll check it in about 1 hour and approve it if ok, thanks man!

Gilles Hemmerlé · Accepted Answer · 2012-12-12 17:19:55Z

1

1 - Convert your $owner_id to type int

$owner_id = (int)$owner_id;

2 - Use a condition to update this field only if a value > 0

$sql = "UPDATE test_accs SET " . 
        ($owner_id > 0 ? "acc_owner = '$owner_id', " : "") .
        "acc_policy_version = '$version', " .
        "acc_policy_last_update = '$approved', " . 
        "acc_policy_next_update = '$renewed' " .
        "WHERE acc_id = '1'";

Note : Be carrefull, your variables seems not correctly securised and you have risks of mysql injections. See http://php.net/manual/fr/mysqli.real-escape-string.php.

And, maybe you should think about use the PDO php extension (http://fr2.php.net/manual/en/intro.pdo.php) for you mysql developpement or any orm ?

answered Dec 12, 2012 at 17:19

Gilles Hemmerlé

5373 silver badges10 bronze badges

Comments

Éderson T. Szlachta · Accepted Answer · 2012-12-12 17:02:22Z

0

You should verify all values that came from a html form. Than, if you mysql field can be NULL, just set NULL to php var:

if (strlen($owner_id) == 0) {
    $owner_id = NULL;
    // OR 
    $owner_id = 0;
} else {
    $owner_id = addslashes($owner_id);
}

$sql = "UPDATE test_accs SET 
    acc_owner = '$owner_id', 
    acc_policy_version = '$version', 
    acc_policy_last_update = '$approved', 
    acc_policy_next_update = '$renewed' 
        WHERE acc_id = '1'";

answered Dec 12, 2012 at 17:02

Éderson T. Szlachta

5186 silver badges6 bronze badges

2 Comments

Pawel Over a year ago

hmz, but woudn't that mean I'd override account owner with value 0? owners table holds people that maintains accounts, so setting there 0, would mean, that nobody maintains account. If I understood you correctly.

Éderson T. Szlachta Over a year ago

I don't know about your structure, but I guess you shouldn't update acc_owner, just the other fields.

Rubin Porwal · Accepted Answer · 2012-12-12 17:25:34Z

0

You can initialize variables holding values for optional fields with default values according to their respective data types.

Please refer the code snippet mentioned below.

      $owner_id=0;
      $version=0;
      $approved='';
      $renewed='';

     if($_SERVER['REQUEST_METHOD']=='POST')
     {
          extract($_POST);
      }

             $sql = "UPDATE test_accs SET 
acc_owner = '$owner_id', 
acc_policy_version = '$version', 
acc_policy_last_update = '$approved', 
acc_policy_next_update = '$renewed' 
    WHERE acc_id = '1'";

answered Dec 12, 2012 at 17:25

Rubin Porwal

3,8451 gold badge27 silver badges27 bronze badges

Collectives™ on Stack Overflow

MySQL update query, how to skip empty values?

6 Answers 6

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related