Handling empty field inputs on UPDATE
Posted: Mon Jan 22, 2007 11:17 am
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:
The XHTML form that submits the data to the above code, is set up like so (snippet):
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.
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);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']); ?>
" />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.