Best way to insert NULL into db

[BuyAnswersLinks]

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.

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.

[jimmy85]

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.

[Cups]

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?

[jimmy85]

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.

[AnthonySterling]

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.

[risoknop]

Originally Posted by SilverBulletUK

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:

foreach ($variables as $v) {

    if (true === empty(trim($v))) {

        $v = null;

    }

}



// now query the database 

[BuyAnswersLinks]

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

[BuyAnswersLinks]

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

[Cups]

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.

[BuyAnswersLinks]

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.

[decowski]

In order to insert a NULL value, rather than the string "NULL", it mustn't be enclosed in quotes, therefore:

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).

[Cups]

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.

// 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

[BuyAnswersLinks]

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

[Cups]

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.

var_dump( NULL ) ;
// = null

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

[BuyAnswersLinks]

Originally Posted by Cups

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

    $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

[danNL]

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 ...

[BuyAnswersLinks]

Originally Posted by danNL

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