Problem with inserting new rows into mysql

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 newbie to the world of PHP and mysql so am probably missing something simple, but have been told that the problem is probably in my PHP coding by someone over in the sql section of this site. Any help would be much appreciated, the coding I have is as follows (sorry if it seems a bit long winded but I haven’t a clue which bit is relevant):

<?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));
}

?>

Also in #PHP _↴