Problem with inserting empty fields into mysql database

[mglover88]

Hi,

I have a registration form built in dreamweaver designed to insert data into my database through PHP, with some of the fields in my form optional to the user.

My understanding is that I should set these 'optional fields' to NULL in mysql in order to be able to proceed with the registration should the user choose not to enter any data. This appears to work fine and the website moves on as required upon the form being submitted.

However, should the user choose to enter something into one of these fields marked NULL in mysql the data is lost and when I look at the inserted row it simply says NULL, regardless of what data the user has entered in the form.

If I go the other way and mark the field NOT NULL then the data appears correctly, only for the user to get an error message saying 'column cannot be null' should they decide to leave it blank.

I am a relative newbie to the world of PHP and mysql so am probably missing something simple. Any help would be much appreciated.

Thanks in advance.

[WolfShade]

Can't say without seeing code, but the issue is most likely a PHP issue, not so much of an SQL issue.

Yes, by marking ALLOW NULL in those fields in the database, you can leave the value blank (same thing if you default the column value to ''). But if data is entered into the form field but it's not being updated in the database, I'm guessing the problem is in PHP not updating that column in the database.

Just a guess.

[mglover88]

Hi Wolfshade,
Thanks for your reply. The php for inserting my form to the database is:

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "signUpForm")) {
$insertSQL = sprintf("INSERT INTO logindetails (email, password, first_name, last_name, tel_number, location, gender, dob_day, dob_month, dob_year, height_feet, height_inches, weight_stone, weight_lbs, heart_rate, fitness_goals, training_venue, training_partners, spare_time, sessions_per_week, where_did_you_hear_about_us, other, exercises_do_not_like, exercises_do_like, workout_details) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['emailAddress'], "text"),
GetSQLValueString($_POST['password'], "text"),
GetSQLValueString($_POST['firstName'], "text"),
GetSQLValueString($_POST['lastName'], "text"),
GetSQLValueString($_POST['telNumber'], "text"),
GetSQLValueString($_POST['location'], "text"),
GetSQLValueString($_POST['gender'], "text"),
GetSQLValueString($_POST['dobDay'], "int"),
GetSQLValueString($_POST['dobMonth'], "text"),
GetSQLValueString($_POST['dobYear'], "int"),
GetSQLValueString($_POST['heightFeet'], "int"),
GetSQLValueString($_POST['heightIns'], "int"),
GetSQLValueString($_POST['weightStone'], "int"),
GetSQLValueString($_POST['weightLbs'], "int"),
GetSQLValueString($_POST['heartRate'], "int"),
GetSQLValueString($_POST['fitnessGoals'], "text"),
GetSQLValueString($_POST['trainingVenue'], "text"),
GetSQLValueString($_POST['trainingPartners'], "text"),
GetSQLValueString($_POST['trainingTime'], "text"),
GetSQLValueString($_POST['numberOfSessions'], "text"),
GetSQLValueString($_POST['whereDidYouHearAboutUs'], "text"),
GetSQLValueString($_POST['otherWhere'], "text"),
GetSQLValueString($_POST['otherWhere'], "text"),
GetSQLValueString($_POST['exercisesDoNotLikeDoing'], "text"),
GetSQLValueString($_POST['exercisesDoLikeDoing'], "text"));

mysql_select_db($database_local, $local);
$Result1 = mysql_query($insertSQL, $local) or die(mysql_error());

$insertGoTo = "par-qForm.php";
if (isset($_SERVER['QUERY_STRING'])) {
$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
$insertGoTo .= $_SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $insertGoTo));
}

?>

[WolfShade]

I'm not that versed in PHP, I've barely started learning it.

My guess is that it most likely has something to do with the ternary conditionals you are using. Plus, you are encapsulating NULL in quotes, which makes me think that "NULL" is actually being entered as the value, not that the column is null for that record.

Post your question and sample code in the PHP forum, here. Sorry I can't be of more assistance.