[Resolved] Can't Insert Empty Field, Has To Be NULL

partypete

Hey all,

Ok this has me quite confused. I don't know if it's the software I use now to manage my MySQL databases or what. I use to be able to insert EMPTY fields into the database - ie: If a field was blank in a web form and the form was submitted to insert a record into the database, the empty form field would have inserted an empty string into the database field.

I could have then searched the database with a query like:

SELECT * FROM my_table WHERE table_field = ''

.. thus returning a all records where table_field is EMPTY. This is what I want!! I don't want to have NULL inserted into the database, I want truly EMPTY fields. But I can't seem to get this to work. After just creating a new database, for some reason now it seems like I have only two options... insert something to the field... or allow NULL values.

I mainly use MySQL Manager by EMS-HiTech. When creating the database, or adding fields to a table to be more exact... there is a check box that reads "Not NULL". If I check this box and try to insert a record with a web form with empty fields, I get the error:

" Column 'table_field' cannot be null "

If I uncheck the "Not NULL" box, it allows me to insert empty form fields, but fills those fields with NULL values.

How can I create/insert truly EMPTY fields in my database??? With NO NULL values??

Thanks a lot for any help. This is driving me batty!!!

partypete

Also strange... Using MySQL Manager to directly edit the table fields, if I enter information into a NULL field and save my changes, then go edit that same field and delete the information I put into it, the field is now truly empty and does not return to a NULL state.

Also I inserted a new record using MySQL Manager. I left most of the fields blank and it inserted the record fine with NO NULLS, but if I attempt to insert a record using a web form, I get the error that whatever fields cannot be NULL.

So for some reason MySQL Manager can insert a record with blank fields to "Not NULL" fields, but a web form cannot.

AAAAHHHHHHHHHHHHHHHHHHHHH!!!!!!!!!!

Enough to drive yourself crazy...

mrhappiness

if it's your webform and your code which inserts the data from the webform i think you're doing something wrong maybe?

please post the INSERT you try to execute

partypete

Hey mrhappiness,

The insert is generated by Dreamweaver:

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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 = $HTTP_SERVER_VARS['PHP_SELF'];
if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($HTTP_SERVER_VARS['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO av_products (product_status, product_status_info, product_cat, product_model, product_jnumber, product_brand, product_title, product_description, product_features, product_dimensions, product_length, product_width, product_height, product_depth, product_weight, product_price, product_sale_price, product_added) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['product_status'], "text"),
                       GetSQLValueString($_POST['product_status_info'], "text"),
                       GetSQLValueString($_POST['product_cat'], "int"),
                       GetSQLValueString($_POST['product_model'], "text"),
                       GetSQLValueString($_POST['product_jnumber'], "text"),
                       GetSQLValueString($_POST['product_brand'], "int"),
                       GetSQLValueString($_POST['product_title'], "text"),
                       GetSQLValueString($_POST['product_description'], "text"),
                       GetSQLValueString($_POST['product_features'], "text"),
                       GetSQLValueString($_POST['product_dimensions'], "text"),
                       GetSQLValueString($_POST['product_length'], "text"),
                       GetSQLValueString($_POST['product_width'], "text"),
                       GetSQLValueString($_POST['product_height'], "text"),
                       GetSQLValueString($_POST['product_depth'], "text"),
                       GetSQLValueString($_POST['product_weight'], "text"),
                       GetSQLValueString($_POST['product_price'], "double"),
                       GetSQLValueString($_POST['product_sale_price'], "double"),
                       GetSQLValueString($_POST['product_added'], "date"));

  mysql_select_db($database_AV_Store, $AV_Store);
  $Result1 = mysql_query($insertSQL, $AV_Store) or die(mysql_error());

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

Actually, this code contains NULL's. Is this insert telling the database to use NULLs if a form field is blank??

$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";

Thanks

partypete

Everyone else stumped too? 😕

partypete

Actually the problem ended up being what I thought it might be. The INSERT statement was sending NULL if a form field was left blank. I replaced the "NULL" with either a string "''" or a default value "0".

Thanks all!

Peter