Avatar of dsellers99
dsellers99 asked on

Null or Not Null in MYSQL?

I have a php site I built in dreamweaver. I've been struggling with this newbie question - I have a form that submits into a MYSQL database. Most of the text fields on the form are required (I have validation rules on them). But some of the form text fields don't need to be required, so there is no validation in them. But when the form is submitted, I get the error message:

SQL error: Column 'my_field' cannot be null.

I went into the insert record wizard and set a default value for the fields that are optional (that aren't required), but I still get the error message. Should I go into the database and chnage the value from NOT NULL to NULL? Should I take the default value out or leave it in? Thanks for any suggestions.
MySQL ServerAdobe DreamweaverPHP

Avatar of undefined
Last Comment

8/22/2022 - Mon
CWS (haripriya)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Loganathan Natarajan

It seems you are trying to insert empty value to the not null field ... try to insert some default value 0 or NULL

Better way if fields are not required you in database make change as NULL ,
If you set default value it means it is required field....
Its depend on your fields type

I see  - so I can just make the database field NULL and I won' t get the error anymore! What about this - if I keep it as NOT NULL and in the form wizard, enter "none " as the default value, will this be the value that is inserted into the database when the form is submitted? I tried to do this with a NOT NULL fileld (I entered the default value of "none") but I still got that error message. Thank you for any sugggestions.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

While selecting 'none'value  you need to take care of Datataype also...

If its int then it will not take value 'none'

Can you post your code ?

Here's the code. Your can see that in the form field "Other_shapes_linked_to_target" I have the default value as "non accliable"

But when I leave the form field empty, that value doesn't get posted to the database. I created this in developer's toolbox, so that could complicate the isse. Anyway, if you have any ideas about getting that default value to submit, let me know. I do have it set us varchar.
<?php require_once('../Connections/project44.php'); ?>
// Load the common classes
// Load the tNG classes
// Make a transaction dispatcher instance
$tNGs = new tNG_dispatcher("../");
// Make unified connection variable
$conn_project44 = new KT_connection($project44, $database_project44);
// Start trigger
$formValidation = new tNG_FormValidation();
$formValidation->addField("Shapes_present_with_target", true, "text", "", "1", "10000", "Please choose atleast one option above.");
$formValidation->addField("Other_shapes_linked_to_target", false, "text", "", "2", "10000", "Please enter a valid value.");
$formValidation->addField("Colors_present_with_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Number_of_people_or_objects_around_this_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Any_names_locations_or_initials_connected_to_this_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Sounds_connected_to_this_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Smells_involved_with_this_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Tastes_inolved_with_this_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Time_of_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Polarity_of_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Other_polarity_of_target_if_other", false, "text", "", "2", "10000", "Please enter a valid value.");
$formValidation->addField("Changes_coming_up_soon_surrounding_this_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Actions_to_make_target_better", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Emotional_sensatations_surrounding_this_target", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Other_comments_about_this_subject_or_event", true, "text", "", "2", "10000", "Please enter something above.");
$formValidation->addField("Advanced_Perception_Course_Student", true, "text", "", "2", "10000", "Please choose atleast one option above.");
// End trigger
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
  $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";
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
  return $theValue;
$colname_Recordsetuname = "-1";
if (isset($_SESSION['kt_login_user'])) {
  $colname_Recordsetuname = $_SESSION['kt_login_user'];
mysql_select_db($database_project44, $project44);
$query_Recordsetuname = sprintf("SELECT user_name FROM registration WHERE e_mail = %s", GetSQLValueString($colname_Recordsetuname, "text"));
$Recordsetuname = mysql_query($query_Recordsetuname, $project44) or die(mysql_error());
$row_Recordsetuname = mysql_fetch_assoc($Recordsetuname);
$totalRows_Recordsetuname = mysql_num_rows($Recordsetuname);
$colname_Recordsetfname = "-1";
if (isset($_SESSION['kt_login_user'])) {
  $colname_Recordsetfname = $_SESSION['kt_login_user'];
mysql_select_db($database_project44, $project44);
$query_Recordsetfname = sprintf("SELECT firstname FROM registration WHERE e_mail = %s", GetSQLValueString($colname_Recordsetfname, "text"));
$Recordsetfname = mysql_query($query_Recordsetfname, $project44) or die(mysql_error());
$row_Recordsetfname = mysql_fetch_assoc($Recordsetfname);
$totalRows_Recordsetfname = mysql_num_rows($Recordsetfname);
// Make an insert transaction instance
$ins_exer1 = new tNG_insert($conn_project44);
// Register triggers
$ins_exer1->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "kt_submit");
$ins_exer1->registerTrigger("END", "Trigger_Default_Redirect", 99, "../instruct1/instruct6.php");
$ins_exer1->registerTrigger("BEFORE", "Trigger_Default_FormValidation", 10, $formValidation);
// Add columns
$ins_exer1->addColumn("user_name", "STRING_TYPE", "POST", "user_name", "{Recordsetuname.user_name}");
$ins_exer1->addColumn("exerreference", "STRING_TYPE", "POST", "exerreference", "");
$ins_exer1->addColumn("smallreference", "STRING_TYPE", "POST", "smallreference");
$ins_exer1->addColumn("exerreferfilter", "STRING_TYPE", "POST", "exerreferfilter", "");
$ins_exer1->addColumn("login_user", "STRING_TYPE", "POST", "login_user", "{SESSION.kt_login_user}");
$ins_exer1->addColumn("Shapes_present_with_target", "STRING_TYPE", "POST", "Shapes_present_with_target");
$ins_exer1->addColumn("Other_shapes_linked_to_target", "STRING_TYPE", "POST", "Other_shapes_linked_to_target", "not applicable");
$ins_exer1->addColumn("Colors_present_with_target", "STRING_TYPE", "POST", "Colors_present_with_target");
$ins_exer1->addColumn("Number_of_people_or_objects_around_this_target", "STRING_TYPE", "POST", "Number_of_people_or_objects_around_this_target");
$ins_exer1->addColumn("Any_names_locations_or_initials_connected_to_this_target", "STRING_TYPE", "POST", "Any_names_locations_or_initials_connected_to_this_target");
$ins_exer1->addColumn("Sounds_connected_to_this_target", "STRING_TYPE", "POST", "Sounds_connected_to_this_target");
$ins_exer1->addColumn("Smells_involved_with_this_target", "STRING_TYPE", "POST", "Smells_involved_with_this_target");
$ins_exer1->addColumn("Tastes_inolved_with_this_target", "STRING_TYPE", "POST", "Tastes_inolved_with_this_target");
$ins_exer1->addColumn("Time_of_target", "STRING_TYPE", "POST", "Time_of_target");
$ins_exer1->addColumn("Polarity_of_target", "STRING_TYPE", "POST", "Polarity_of_target");
$ins_exer1->addColumn("Other_polarity_of_target_if_other", "STRING_TYPE", "POST", "Other_polarity_of_target_if_other", "none");
$ins_exer1->addColumn("Changes_coming_up_soon_surrounding_this_target", "STRING_TYPE", "POST", "Changes_coming_up_soon_surrounding_this_target");
$ins_exer1->addColumn("Actions_to_make_target_better", "STRING_TYPE", "POST", "Actions_to_make_target_better");
$ins_exer1->addColumn("Emotional_sensatations_surrounding_this_target", "STRING_TYPE", "POST", "Emotional_sensatations_surrounding_this_target");
$ins_exer1->addColumn("Other_comments_about_this_subject_or_event", "STRING_TYPE", "POST", "Other_comments_about_this_subject_or_event", "none");
$ins_exer1->addColumn("Advanced_Perception_Course_Student", "STRING_TYPE", "POST", "Advanced_Perception_Course_Student");
$ins_exer1->setPrimaryKey("userid", "NUMERIC_TYPE");
// Execute all the registered transactions
// Get the transaction recordset
$rsexer1 = $tNGs->getRecordset("exer1");
$row_rsexer1 = mysql_fetch_assoc($rsexer1);
$totalRows_rsexer1 = mysql_num_rows($rsexer1);

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

You can try this trick

You can check before inserting whether the field contain value or not ?

if its null then set the value 'none' before inserting


Sounds great - know where I might find an example of this type of coding? Thanks.

thank you again
Your help has saved me hundreds of hours of internet surfing.