SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot BuyAnswersLinks's Avatar
    Join Date
    Aug 2008
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to insert NULL into db

    Hello,

    I have a script which needs users to fill out certain info and leave certain info blank. When they leave a certain field blank I need to add NULL into the db for that column. Below is my current way of doing it, but I bet there is a better way, what is a better way of what I am doing below, I am looking for something more secure if the user tries to play with it.

    PHP Code:
    if(empty($_POST['keyword_two'])) {
    $keywordbase_one "`keyword_two`= NULL,";
    }else{
    $keyword_main_one strtolower(trim($_POST['keyword_two']));
    $keywordbase_one "`keyword_two`= '$keyword_main_one',";

    dont worry I have mysql_real_escape_string and all that on it, I took it out to make it look prettier.

    Thanks everyone for there help.

  2. #2
    SitePoint Zealot jimmy85's Avatar
    Join Date
    Aug 2009
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand that that condition up there is part of your code to make the query string for insertion into the database?

    It looks fine.

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    if( empty( trim$_POST['keyword_two'] ) ) ) { 
    or use:

    array_map( "trim" , $_POST ) ;

    earlier on.

    Otherwise $keyword_two = " "; will get through.

    But really, wouldn't keyword_two already be a NULL field in your database in the first place?

  4. #4
    SitePoint Zealot jimmy85's Avatar
    Join Date
    Aug 2009
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That won't be a NULL value anymore if he puts quotes in there. And I think he has to explicitly put a null as value for the variable, otherwise the query would go wrong. The way I see it, code is trying filter out blanks from the form, then concatenate strings to form a query from those variables.

    Another way would be, have another bit of logic in the code, determine whether a column has a value or not, then skip that column from the query. It's just gonna make the code longer though.

    So if TABLE x has A, B and C columns, if it can be determined that COL B has no value, query is like

    "INSERT INTO x (A, B) VALUES ('y', 'z')"

    Of course COL B has NULL as default.

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    If the fields are optional, and you require them to be null if omitted, let the database handle this and set the field NULL my default.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post
    If the fields are optional, and you require them to be null if omitted, let the database handle this and set the field NULL my default.
    Wouldn't work for values such as "", " "... they would be saved as empty string or string with spaces into database instead. You must set variable to null to make sure it will be saved in the database as null.


    To the OP - something like this is just fine:

    PHP Code:
    foreach ($variables as $v) {
        if (
    true === empty(trim($v))) {
            
    $v null;
        }
    }

    // now query the database 

  7. #7
    SitePoint Zealot BuyAnswersLinks's Avatar
    Join Date
    Aug 2008
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes it works by seeing if the var is set, if it is set then it adds it to the db, if it isnt set then it adds NULL into the db.

    I like risoknop's code, I dont understand how to add it into the db tho. Could you show the db query part? That code would save a lot of time heh. But I do not understand the inserting into the db part, if you could reply with a small amount of example code I could get it from there, thanks everyone for the help

  8. #8
    SitePoint Zealot BuyAnswersLinks's Avatar
    Join Date
    Aug 2008
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello, sorry to double post. I need to figure this out and I like risoknops' code. How would I get that code to work with:

    insert into `whatever` SET `keyword_one` = '$var', `url_one` = '$var2', `whatever` = '$var3' where id = 'blah'

    And say if `url_one` was NULL, how would I use the foreach to insert a NULL into the `url_one`. I get that the var for the NULL would be $v, but what if the variable isnt null, I dont understand how that would work.

    Thanks again for the help

  9. #9
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Imagine database table should already contain NULL fields by default.

    For arguments sake :

    table
    ====
    id INT auto-increment
    var1 varchar( 30 ) default NULL
    var2 varchar( 30 ) default NULL

    When you insert a new row;

    insert into table ( id, var1 ) values ( 0, "thing" );

    Your entire row values will automatically turn into:

    1, "thing", NULL ;

    Now if you want to update this table:

    update table set var1 = "new thing" where id = 1;

    Your entire row values will be:

    1, "new thing", NULL

    See the syntax diffs for insert and update?

    Now, if you explain what kind of form elements are sending data, or empty data to this form handler someone will show you how to potentially loop through the incoming vars and create an sql statement on the fly, but you have to tell if it is to insert a new or update an existing row?

    Trying to insert NULL for a form element which is not set is probably a sign you have misunderstood something fundamental - unless you using this in order to skirt round another issue which you have not yet explained.

  10. #10
    SitePoint Zealot BuyAnswersLinks's Avatar
    Join Date
    Aug 2008
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello Cups, I get all of that part. I am looking to find out how to make the loop to update the database. The form is for a customers order in which they will need to update certain details and leave certain details blank. I need a easy yet safe way to update the database with either a variable or NULL.

    Some of the db columns would be:

    keyword_one
    url_one
    order_description
    product_title
    another_example
    etc

    I like risoknops' code but I do not know how to get it into the sql. If you could show me how to loop it within the sql that would be awesome. What I am needing to do is what I wrote above:

    update `whatever` SET `keyword_one` = '$var', `url_one` = '$var2', `whatever` = '$var3' where id = 'blah'

    So my question is how do I get risoknops' code into the above sql example?

    Here is an example with it filled out:

    update `product` SET `keyword_one` = 'test keyword, `url_one` = 'sitepoint.com', `whatever` = NULL where id = '787'

    Thanks for the help.

  11. #11
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In order to insert a NULL value, rather than the string "NULL", it mustn't be enclosed in quotes, therefore:

    PHP Code:
    if ($var == "") {
        
    $var "NULL";
    } else {
        
    $var "'$var'";
    }

    $sql "INSERT INTO `table` (`col`) VALUES ($var)"
    You may need to change the condition in order to test for the value you want to be saved as NULL (empty string in my example).

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    OK.

    1. Call your form elements the same name as the database columns, its less confusing, not doing so does not protect your database in any way, and it opens up the way to think of your code as arrays.

    2. Have an array of elements you expect to find coming from your form

    3. Create the start and end of the sql query

    4. Loop through the elements, check they are permitted, check they are valid, if so add then to a string, and add between the start and end of the sql query.

    This is a simplified version with just 2 elements.
    PHP Code:
    // 1

    <form method=post action="">
    <input type=hidden id="id" value=27>
    <input id=keyword_one value="soup" />
    <input id=url_one value="www.soup.com" />
    <input type =submit></form>

    <?php

    // 2

    $permitted = array(
      
    'keyword_one'
    'url_one'
    );

    $_POST array_map"trim" ,  $_POST ) ;

    if( isset( 
    $_POST['id'] ) ) {

    // 3

    $sql_start "update food set " ;
    $sql_end "where id = " . (int)$_POST['id']  ;
    $sql _middle "" ;

    // 4

    foreach( $_POST as $key=>$value ) {

    // LOOK at the values if you are not sure, uncomment here
    // echo $key . "=" . $value . PHP_EOL ;

       
    if( in_array$key$permitted ) && !empty( $value ) ) { // one security check 
     
             
    $sql_middle .= $key = '" mysql_real_escape_string$value) . "'," ;

       }

    }


    // you have to chop off the , from the last element
    $sql_middle rtrim($sql_middle "," )  ;

    // you could test that $sql_middle is != ""

    // then stick the bits back together, echo them out

    echo $sql_start $sql_middle $sql_end ;

    // copy and paste the outcome into PhpMyAdmin to 
    // test it actually is well formed, and that you have 
    // data in your database which permits the query to go ahead

    }
    That is untested by the way.

    There are other things to be done, and I left it verbose so you can hopefully see what is going on and see what else has to be done like check there is more than just "id" set in the POST vars but this should give you an idea of how to build up the query string.

    The key is to marry var names to column names, and start to think about your code as arrays as spelt out by this guy in loops are good

  13. #13
    SitePoint Zealot BuyAnswersLinks's Avatar
    Join Date
    Aug 2008
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cups code works great!

    However I am having one problem, the code isnt inseting NULL like I need. If the customer does not fill in a text feild then the script will not update that column with NULL (well it doesnt update at all because of the array).

    Someone suggested:

    $sql_middle = ” $key = ” . ($value ? “‘” . mysql_real_escape_string($value) . “‘” : “NULL”) . “”;

    However that code isnt working either, can someone take a look and see what I am doing wrong? thanks

  14. #14
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Are you saying you have a field in a dbase which defaults to NULL (that was my premise after all) and you then set it to something, say "Mary" and you THEN want to reset it to NULL?

    If so, I don't think you quote NULL, otherwise it is a string "NULL" - not the same thing.
    PHP Code:
    var_dumpNULL ) ;
    // = null

    var_dump"NULL" ) ;
    // = string 'NULL' (length=4) 

  15. #15
    SitePoint Zealot BuyAnswersLinks's Avatar
    Join Date
    Aug 2008
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    Are you saying you have a field in a dbase which defaults to NULL (that was my premise after all) and you then set it to something, say "Mary" and you THEN want to reset it to NULL?

    If so, I don't think you quote NULL, otherwise it is a string "NULL" - not the same thing.

    Yes the column default is NULL, so when the customer submits a field that is empty then I would like to add NULL into the db to make it null.

    Right now the script is only set up to update the fields that were non empty when the customer submitted the info, so if the customer leaves a field blank then the form will not change that particular field.

    I need it to add NULL into the db when a field is left empty on the form. Please bare with me because I am a newb, but I made the change below and it did not work

    PHP Code:
        $sql_middle $key = " . ($value "'" mysql_real_escape_string($value) . "'" NULL) . ""
    I took the quotes out of the NULL part, still doesnt work

    If you could please suggest how to fix the null part that would help me out so much thanks

  16. #16
    SitePoint Addict
    Join Date
    May 2006
    Location
    Amsterdam
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    foreach ( $_POST as $key => $value ) {

        
    $null_middle $key = NULL,";
        
        if (
            !empty( 
    $value )
            && 
    in_array$key$permitted )
        ) {
            
            
    $sql_middle .= $key = '" mysql_real_escape_string$value) . "',";
           
        } else {
           
           
    $sql_middle .= $null_middle;
           
        }


    The problem, as far as I understand it, is that PHP NULL and db NULL are different, although they look the same to our eyes . Passing the PHP NULL to the db results in an empty string, if I'm not mistaken. The db needs the word NULL without quotes in the query as you've got in your example in #10 above; thus the code here should do the trick ...

  17. #17
    SitePoint Zealot BuyAnswersLinks's Avatar
    Join Date
    Aug 2008
    Posts
    123
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by danNL View Post
    PHP Code:
    foreach ( $_POST as $key => $value ) {

        
    $null_middle $key = NULL,";
        
        if (
            !empty( 
    $value )
            && 
    in_array$key$permitted )
        ) {
            
            
    $sql_middle .= $key = '" mysql_real_escape_string$value) . "',";
           
        } else {
           
           
    $sql_middle .= $null_middle;
           
        }


    The problem, as far as I understand it, is that PHP NULL and db NULL are different, although they look the same to our eyes . Passing the PHP NULL to the db results in an empty string, if I'm not mistaken. The db needs the word NULL without quotes in the query as you've got in your example in #10 above; thus the code here should do the trick ...

    Yes that works great! thank you and th rest for helping me with this! It is worken great now


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
  •