#1
  1. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,464
    Rep Power
    70

    NULL in Prepared Statements


    I've searched around a bit, and found a few attempted answers, none of which have worked so far.

    The basic idea of my script is as shown below.
    PHP Code:
    $var = isset($_POST['var']) ? $_POST['var'] : NULL;
    $stmt $db->prepare('UPDATE `table` SET `value`=?;');
    $stmt->bind_param('d'$var);
    $stmt->execute(); 
    Now, if the input was not set, $var is to be NULL. I have the MySQL field set to DECIMAL(5,2) with NULL allowed, and as the default. The problem is with the Prepared Statement, it sets it to 0.00 instead of NULL. Is there any suggested way around this? Thanks for any help.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,276
    Rep Power
    9645
    Originally Posted by Triple_Nothing
    Now, if the input was not set, $var is to be NULL.
    If it really wasn't set at all then yes. Are you sure it wasn't? And not that it was set but an empty string? Because AFAIK NULL will work with any bound type.
  4. #3
  5. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,464
    Rep Power
    70
    Well, I have the isset() checking that, because if the INPUT is blank, then variable doesn't even exist. I even added the outcome of the $var to an echo to see if the script set it to be blank, or truly NULL, and the $var was truly set to NULL... If data type is 'd' (double), it processes the execute() successfully, but sets the value to '0.00'. If data type is 's' (string), it fails noting that '' is not a valid value. The echo notes it's truly set to NULL, but due to such an error with the string attempt, it sounds the Prepared Statement is still quoting it, and not defining it truly as NULL...
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  6. #4
  7. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,276
    Rep Power
    9645
    echoing a null will not produce a "NULL". It'll just be a blank string. Same as if you echoed a blank string.

    PHP Code:
    var_dump($var); 
    What does that say?
  8. #5
  9. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,464
    Rep Power
    70
    Well, if I include the var_dump() in my return array, it just shows up blank.

    The true defining:
    PHP Code:
    $adj_value = isset($_POST['adj_value']) ? $_POST['adj_value'] : NULL
    How I tested the NULL:
    PHP Code:
    $echo_val $adj_value == NULL 'Is NULL' 'Is NOT NULL'
    And I was echo-ing that in my return array to test, and was receiving 'Is NULL'...
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  10. #6
  11. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,464
    Rep Power
    70
    I finally got something to get passed through my AJAX call to test this. I did a var_dump() as below and returned the following response.
    PHP Code:
    ob_start();
    var_dump($adj_value);
    $return ob_get_clean();
    echo 
    json_encode(array($return)); 
    My logging to console, which was also alert()'ed...
    Code:
    console.log(data[0]);
    Response:
    Code:
    string(0) ""
    Not sure why this defines a string, unless it's my output buffering, but my if/else successfully recognizes it as NULL, and if I test such straight out, I get the word 'NULL'. I even commented out my other coding, so this is performed right after:
    PHP Code:
    $adj_value = isset($_POST['adj_value']) ? $_POST['adj_value'] : NULL
    That sets it at NULL. I tested it with my if/else right after that, and that's where it stated 'Is NULL'... But not the var_dump()...
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  12. #7
  13. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,761
    Rep Power
    4536
    If your form contained an element named "adj_value" in it, then $_POST['adj_value'] is going to be set when the form is submitted. If nothing was provided, it's going to be an empty string.

    The only way $_POST['adj_value'] would come across as unset is if that element never existed in your form. Meaning, there was some condition controlling whether that HTML element was added to your form or not.

    You may want to look at using empty() instead of isset().

    The following things are considered to be empty:

    "" (an empty string)
    0 (0 as an integer)
    0.0 (0 as a float)
    "0" (0 as a string)
    NULL
    FALSE
    array() (an empty array)
    $var; (a variable declared, but without a value)
    -- Cigars, whiskey and wild, wild women. --
  14. #8
  15. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,072
    Rep Power
    4101
    Originally Posted by Triple_Nothing
    How I tested the NULL:
    PHP Code:
    $echo_val $adj_value == NULL 'Is NULL' 'Is NOT NULL'
    That test is not valid, you need to use === (triple equals) instead. == (double equals) will do type conversions which means more than just NULL (any false-like value) will be considered equal to NULL.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  16. #9
  17. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,464
    Rep Power
    70
    The last 2 responses got me on track. I think my mind was wrapped around my previous action with the checkbox. If I'm correct, that is not a submitted element if not checked, so I was thinking the same for the text INPUTs. The === vs == helped in my checks if truly NULL, which correctly defined such once I switched to ===. Thank you.

    As for the final alteration in case any future individuals come across something similar:
    Original invalid set/check:
    PHP Code:
    $adj_value = isset($_POST['adj_value']) ? $_POST['adj_value'] : NULL;
    $adj_ret $adj_value == NULL 'Is NULL' 'Is NOT NULL'
    Final:
    PHP Code:
    $adj_value = !empty($_POST['adj_value']) ? $_POST['adj_value'] : NULL;
    $adj_ret $adj_value === NULL 'Is NULL' 'Is NOT NULL'
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  18. #10
  19. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,761
    Rep Power
    4536
    Just keep in mind that "0" is considered empty(), along with an empty string. So if a zero value is submitted, you'll end up with a NULL value submitted to your database.
    -- Cigars, whiskey and wild, wild women. --
  20. #11
  21. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,464
    Rep Power
    70
    That will be fine. 0 shouldn't exist in this item in a visual manner, so such can still be set to NULL, and my math actions will work the same either way, but thanks for the note. I'll keep that in mind for future items.

    And just a note to all to go along with this note... My 'check' lines were just for troubleshooting this, and not to exist in the final code. So, the one in the 'Final' is checking the variable $adj_value, not its value, so if it was originally '0', then this will still come out 'Is NULL' since the setting of $adj_value will be FALSE with the actual value of '0', whether '0' is a string or integer.

    Comments on this post

    • Sepodati agrees : Sounds good!
    Last edited by Triple_Nothing; June 27th, 2017 at 09:42 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo