www.webdeveloper.com
Results 1 to 3 of 3

Thread: Problem with inserting new rows into mysql

  1. #1
    Join Date
    Apr 2011
    Posts
    3

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

    ?>

  2. #2
    Join Date
    Apr 2010
    Posts
    227
    Huh

    Null means no value.

    Leave the field as not null and if the value from the user is empty DONT place a NULL in a not null field.
    You can simply enter in nothing.

    Your making it hard for yourself IMHO

  3. #3
    Join Date
    Dec 2009
    Location
    City of Smiles
    Posts
    172
    I maybe off-topic in regards with my reply, but I do suggest enclosing your php syntax using the [php] bbcode tag on the editor. This makes the script be accurately readable.
    Last edited by kingdm; 04-21-2011 at 01:45 AM.

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