Is there an easy way to skip updating a field to an empty value in a database?

Question

This starts when a user fills out a form with optional inputs. The values are passed by AJAX to another PHP page to insert into a database. If a particular input is empty, I don't want to update the data stored in the database. Is there a way to check, besides writing lots of if statements, to see if an input value is empty? If it is empty, how can I write the statement so MySQL won't update the corresponding field in the database?

if(isset($_POST['f_name']) && isset($_POST['m_name']) && isset($_POST['l_name']) && isset($_POST['alt_name'])){
    $fname = $_POST['f_name'];
    $mname = $_POST['m_name'];
    $lname = $_POST['l_name'];
    $altname = $_POST['alt_name'];

If some of the $_POST entries are empty, then they shouldn't be put into the database. I'm saying this because if there's already an existing value in the database, I don't want to overwrite the value with an empty one.

possible duplicate of [Form field input empty than do not update](http://stackoverflow.com/questions/6577678/form-field-input-empty-than-do-not-update), [how to update one or more fields ignoring the empty fields into mysql database?](http://stackoverflow.com/questions/6522096/) — outis, Dec 26 '11 at 23:04

Wouter Dorgelo · Accepted Answer · 2011-12-26T23:07:49.027

2

In this case you also don't allow people to enter just a blank space / &bnsp;

<?php
$empty = FALSE;

foreach ($_POST as $key)
{
    if (!isset($key) || strlen(trim($key)) != 0)
    {
      $empty = TRUE;
    }
}

if (!$empty)
{
    $fname = $_POST['f_name'];
    $mname = $_POST['m_name'];
    $lname = $_POST['l_name'];
    $altname = $_POST['alt_name'];
}
?>

edited Dec 26 '11 at 23:07

answered Dec 26 '11 at 22:56

Wouter Dorgelo

11,770
11
62
80

if(!empty) is for all variables? I want to be able to insert all variables that are not having a space. Would I put the query update inside the if(!empty) also? – hellomello Dec 26 '11 at 23:39
so I can just put `UPDATE user SET fname = '$fname', mname = '$mname', lname = '$lname', altname = '$altname'`; and each of those set parameters would only work if it doesn't have an empty input? – hellomello Dec 27 '11 at 00:00
Yes.. But don't forget to validate the input first!! – Wouter Dorgelo Dec 27 '11 at 00:04
@EnricoPallazzo : hey, I've same issue, I'm using CodeIgniter. Will help me how to use this code in CodeIgniter Model, I'm useing Stored Procedure to store/ update data. – Ganesh Aher Feb 23 '18 at 05:43

Darvex · Answer 2 · 2011-12-26T23:30:02.540

1

well...you could use foreach to check all the values.

$empty = FALSE;
   foreach ($_POST as $key) {
    if (!isset($key)) {
      $empty = TRUE;
       break;
   }
}

if (!$empty) {
$fname = $_POST['f_name'];
    $mname = $_POST['m_name'];
    $lname = $_POST['l_name'];
    $altname = $_POST['alt_name'];
}

edited Dec 26 '11 at 23:30

answered Dec 26 '11 at 22:49

Darvex

3,624
2
23
39

In this case you also allow people just an `space` / ` `. And, you are missing a `)` on the 3rd line. – Wouter Dorgelo Dec 26 '11 at 22:57
true,thanks for pointing that out. if ($key == '') would probably fix that though. Unless i'm missing something again – Darvex Dec 26 '11 at 23:00
@Mr.Pallazzo: In the case of a space, the value isn't empty and in the context of the question, shouldn't be filtered. – adlawson Dec 26 '11 at 23:00
@adlawson It's a common mistake I see everywhere. I doubt there's anyone who has a `` as their first, middle or last name. – Wouter Dorgelo Dec 26 '11 at 23:04
@Darvex `if (!isset(trim($key)))` is a bit useless. If you use `trim()` you need to use it like this: `strlen(trim($key)) != 0`. First you remove the space and if the length of the remaining string is equal to `0` it will return as false. – Wouter Dorgelo Dec 26 '11 at 23:26
ehhh, i'll just leave it like it was...guess it's lack of sleep. – Darvex Dec 26 '11 at 23:29

score -1 · Answer 3 · answered Dec 26 '11 at 22:52

-1

I usually use shorthand if statments (also known as the ternary operator) to do something like this. You might be able to write your SQL query something like this:

$query = "UPDATE my_table SET f_name = ".(!empty($fname) ? "'$fname'" : 'f_name').", m_name = ".(!empty($mname) ? "'$mname'" : 'm_name').", ";
//etc.. etc..

answered Dec 26 '11 at 22:52

Brian Glaz

15,468
4
37
55

3

You have a SQL injection vulnerability. – SLaks Dec 26 '11 at 22:54
Not to mention that queries (or anything, really) with `f_name = f_name` is pretty nasty. – adlawson Dec 26 '11 at 22:56
Values shouldn't be interpolated directly into statements. Instead, build the statement with positional parameters and use a prepared statement. – outis Dec 26 '11 at 22:58
@SLaks Obviously I would deal with that before the line posted above. My posted solution is just a small snippet sample to demonstrate logic. @ adlawson In this context it's perfectly fine, I'm using it to simply set a field's value equal to itself in the event that the user provided value is empty. – Brian Glaz Dec 27 '11 at 03:54

Is there an easy way to skip updating a field to an empty value in a database?

3 Answers3