Page 1 of 1

Handling empty field inputs on UPDATE

Posted: Mon Jan 22, 2007 11:17 am
by seodevhead
I was wondering how you guys would handle a situation like this:

I have a database table that has a bunch of VARCHAR columns for things like 'location', 'occupation', etc, and most of these columns are DEFAULT NULL.

When a user signs up for this application, a record in the above table is created and all the columns are set to DEFAULT NULL, awaiting any future updates when the user feels like adding info to their profile.

There is a simple XHTML form that handles the text field inputs and submits the form to process the MySQL UPDATE with the updated data. Here is an example of how I validate (very simplistic for ease of reading) and UPDATE the record in the database:

Code: Select all

if (isset($_POST['location']) && is_string($_POST['location']))
{
       $clean['location'] = escape_data($_POST['location']);
} else
{
       $clean['location'] = NULL;
}

$query = "UPDATE table1 SET location='{$clean['location']}' WHERE id=8";
$result = mysql_query($query);
The XHTML form that submits the data to the above code, is set up like so (snippet):

Code: Select all

<?php
$infoQuery = "SELECT location FROM table1 WHERE id=8 LIMIT 1";
$infoResult = mysql_query($infoQuery);

$info = mysql_fetch_assoc($infoResult);
?>

<input type="text" name="location" size="100" maxlength="250" value="
<?php
     if (isset($_POST['location']) && !empty($_POST['location']))
           echo outputThis($_POST['location']);
     elseif (!empty($info['location']))
           echo outputThis($info['location']); ?>
" />
This all works fine and dandy, but I have a feeling that if the user submits the above form without typing anything into the 'location' text field input, then the 'NULL' value that is appended to the above $clean['location'] is not being UPDATE'd into the DB record properly as a NULL value. Because when I look in phpMyAdmin, it goes from being blank with a checkmark on 'NULL' to just being blank without that NULL checkmark.

Is there anyway I can change my UPDATE statement to properly insert NULL into the location column if the user submits the form without actually typing anything into the location input field? Thanks for any help on this matter, it is greatly appreciated.

Posted: Mon Jan 22, 2007 11:30 am
by feyd
outputThis() does...?

Posted: Mon Jan 22, 2007 11:34 am
by seodevhead
feyd wrote:outputThis() does...?
Sorry... outputThis() simply does an html_entities() and stripslashes.

Posted: Mon Jan 22, 2007 11:44 am
by feyd
So you're getting blank strings in the table and you don't want them to be blank but NULL?

Seems like a trim() and empty() is in order, followed by some generation of SQL.

Posted: Mon Jan 22, 2007 11:52 am
by seodevhead
feyd wrote:So you're getting blank strings in the table and you don't want them to be blank but NULL?

Seems like a trim() and empty() is in order, followed by some generation of SQL.
Actually I have a bit of an error in the code I posted, different than the code I am actually using.

Instead of validating location with if (isset($_POST['location'])) I rather have, if (!empty($_POST['location'])) for obvious reasons. However, I think the problem is in that if $clean['location'] is set to NULL with:

Code: Select all

$clean['location'] = NULL;
The UPDATE sql statement is treating $clean['location'] as a string since it is:

Code: Select all

UPDATE table1 SET location='{$clean['location']}' WHERE id=8
* Note that I have to use single-quotes around the array variable.

I am guessing it is these single-quotes that are causing the problem? Not really sure how to get around this though.

Posted: Mon Jan 22, 2007 11:56 am
by feyd
The single quotes cause it to be a string to MySQL, but NULL values produce empty strings when converted, not the text "null."

Posted: Mon Jan 22, 2007 12:00 pm
by seodevhead
So does it sound like my method I have hear is acceptable or unacceptable for what I am trying to do. It seems to work fine, since I check all outputs with !empty() to make sure there is actually something in each column. But of course if there is an easy fix with my setup that can produce true NULL values for these columns.. I'd love to hear any suggestions. Thanks a bunch feyd for your time and help.

Posted: Mon Jan 22, 2007 12:06 pm
by feyd
Well, what you could do is start from a base array (where all the values are nulled), then incorporate in the submitted values which are not empty() (after a trim()), escaping as required.

This can either be processed further to generate the SQL after the incorporation or during incorporation.

Posted: Fri Feb 23, 2007 2:35 pm
by waskelton4
I found this thread because i'm actually doing what your first example is kind of doing..

here is some of the code..

Code: Select all

$floats = array('field1', 'field2', 'field3','field4','field5','field6');

$sql = "UPDATE table SET lastChanged = getDate(), "

foreach($floats as $field) {

	strlen(trim($_POST[$field])) == 0 ? $sql .= " $field = NULL, " : $sql .= " $field = $_POST[$field],";
}

$sql .= " field7 = $_POST['field7'] WHERE id = $_POST['id'] ";  // the field7 part is there so there is no comma left before the where
This works very well but I'm curious if this is the best way to do it. It seems a little cumbersome but I don't know of any other way to deal with the single quotes..

any thoughts on this??

thanks
will

Posted: Fri Feb 23, 2007 9:16 pm
by feyd
Since there is no escapement waskelton4, your code is dangerous.

One optimization I can see is making the comma appear at the beginning of the concatenated strings instead of the end. Using array_key_exists() may be a good idea too.

Posted: Mon Feb 26, 2007 4:19 pm
by waskelton4
thanks feyd,

I do have some escaping going on elsewhere but might incorporate it into this code if i begin using it on all my posted forms.

thanks again
ws