Handling empty field inputs on UPDATE

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Handling empty field inputs on UPDATE

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

outputThis() does...?
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post by seodevhead »

feyd wrote:outputThis() does...?
Sorry... outputThis() simply does an html_entities() and stripslashes.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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."
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
Last edited by waskelton4 on Mon Feb 26, 2007 4:02 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
Post Reply