dcsimg
www.webdeveloper.com
Results 1 to 4 of 4

Thread: Problem with inserting empty fields into mysql database

  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Problem with inserting empty fields into mysql database

    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.

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    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.

  3. #3
    Join Date
    Apr 2011
    Posts
    3
    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));
    }

    ?>

  4. #4
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles