SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting EMPTY variables into MySQL NOT NULL columns?

    Hello everyone!

    I'm just curious about mySQL database set up. I set several columns as NOT NULL, figuring that there would be an error if I tried to put any blanks into the database for those fields.

    But i set up a form that gets submitted to a PHP page for handling and sometimes I leave the form fields blank (just for testing, i will set up validation later). But I figured that if I tried to insert blank variables in a SQL statement to the database it would have an error putting them into a column that is labeled NOT NULL, but it still puts a blank space in their for it?

    Why is this happening? Thanks!!

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    A blank isn't a null. The NOT NULLS stops you from inserting records where you don't reference those fields at all and therefore are trying to put nothing in them rather than a blank or empty text string.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    A blank isn't a null. The NOT NULLS stops you from inserting records where you don't reference those fields at all and therefore are trying to put nothing in them rather than a blank or empty text string.
    Thanks buddy... but I'm confused that I inserted some variables that didn't even pass the isset($_POST['formvar1']);

    Are PHP variables that aren't even set, do they contain blank spaces?

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,862
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    A PHP variable always contains a value even if that value is an empty string or is undefined. NULLS means that you don't have a value at all not even one that is empty or undefined.

    Say you have two fields in a table fieldA and fieldB and they are the only fields in the table with fieldA being defined as NOT NULL and fieldA not being so defined. Just looking at the field definitions in an insert statement we might specify:

    INSERT fieldA, fieldB VALUES ('a','b')
    INSERT fieldA VALUES ('a')

    are both valid as in the second insert the unreferenced fieldB will be set to NULL.

    INSERT fieldB VALUES ('b')

    is invalid since fieldA must be given a value

    INSERT fieldA, fieldB Values ('','b')

    is valid because fieldA is being set to an empty string rather than to NULL.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Member
    Join Date
    Nov 2005
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Wizard, much appreciated explanation.


Bookmarks

Posting Permissions

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