Go Back   SitePoint Forums > Forum Index > Program Your Site > PHP
Newsletter FAQ Members List Calendar Mark Forums Read

New to SitePoint Forums? Register here for free!

SitePoint Sponsor
 
Reply
 
Thread Tools Display Modes
Old Sep 7, 2009, 16:44   #1
BuyAnswersLinks
SitePoint Zealot
 
BuyAnswersLinks's Avatar
 
Join Date: Aug 2008
Posts: 109
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.
BuyAnswersLinks is offline   Reply With Quote
Old Sep 8, 2009, 05:06   #2
jimmy85
SitePoint Zealot
 
jimmy85's Avatar
 
Join Date: Aug 2009
Posts: 188
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.
jimmy85 is offline   Reply With Quote
Old Sep 8, 2009, 05:58   #3
Cups
So Prized A Nitwit
 
Cups's Avatar
 
Join Date: Oct 2006
Posts: 4,102
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?
Cups is offline   Reply With Quote
Old Sep 9, 2009, 07:01   #4
jimmy85
SitePoint Zealot
 
jimmy85's Avatar
 
Join Date: Aug 2009
Posts: 188
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.
jimmy85 is offline   Reply With Quote
Old Sep 9, 2009, 07:09   #5
AnthonySterling
Previously, SilverBulletUK.
 
AnthonySterling's Avatar
 
Join Date: Apr 2008
Location: North-East, UK.
Posts: 2,917
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 is offline   Reply With Quote
Old Sep 9, 2009, 09:13   #6
risoknop
SitePoint Guru
 
risoknop's Avatar
 
Join Date: Feb 2008
Location: end($world)
Posts: 736
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
risoknop is offline   Reply With Quote
Old Sep 9, 2009, 18:16   #7
BuyAnswersLinks
SitePoint Zealot
 
BuyAnswersLinks's Avatar
 
Join Date: Aug 2008
Posts: 109
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 is offline   Reply With Quote
Old Sep 13, 2009, 23:51   #8
BuyAnswersLinks
SitePoint Zealot
 
BuyAnswersLinks's Avatar
 
Join Date: Aug 2008
Posts: 109
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
BuyAnswersLinks is offline   Reply With Quote
Old Sep 14, 2009, 05:10   #9
Cups
So Prized A Nitwit
 
Cups's Avatar
 
Join Date: Oct 2006
Posts: 4,102
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.
Cups is offline   Reply With Quote
Old Sep 14, 2009, 09:46   #10
BuyAnswersLinks
SitePoint Zealot
 
BuyAnswersLinks's Avatar
 
Join Date: Aug 2008
Posts: 109
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.
BuyAnswersLinks is offline   Reply With Quote
Old Sep 14, 2009, 10:15   #11
decowski
Web Professional
 
Join Date: Oct 2008
Location: London
Posts: 765
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).
decowski is offline   Reply With Quote
Old Sep 14, 2009, 15:15   #12
Cups
So Prized A Nitwit
 
Cups's Avatar
 
Join Date: Oct 2006
Posts: 4,102
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
Cups is offline   Reply With Quote
Old Oct 9, 2009, 21:54   #13
BuyAnswersLinks
SitePoint Zealot
 
BuyAnswersLinks's Avatar
 
Join Date: Aug 2008
Posts: 109
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
BuyAnswersLinks is offline   Reply With Quote
Old Oct 10, 2009, 10:42   #14
Cups
So Prized A Nitwit
 
Cups's Avatar
 
Join Date: Oct 2006
Posts: 4,102
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_dump( NULL ) ;
// = null

var_dump( "NULL" ) ;
// = string 'NULL' (length=4)
Cups is offline   Reply With Quote
Old Oct 13, 2009, 22:29   #15
BuyAnswersLinks
SitePoint Zealot
 
BuyAnswersLinks's Avatar
 
Join Date: Aug 2008
Posts: 109
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
BuyAnswersLinks is offline   Reply With Quote
Old Oct 14, 2009, 16:30   #16
danNL
SitePoint Zealot
 
Join Date: May 2006
Location: Amsterdam
Posts: 183
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 ...
danNL is offline   Reply With Quote
Old Oct 14, 2009, 17:13   #17
BuyAnswersLinks
SitePoint Zealot
 
BuyAnswersLinks's Avatar
 
Join Date: Aug 2008
Posts: 109
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
BuyAnswersLinks is offline   Reply With Quote
Reply

Bookmarks

« Previous Thread | Next Thread »

Thread Tools
Display Modes

 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Sponsored Links
 
Forum Jump


All times are GMT -7. The time now is 18:43.


Powered by vBulletin® Version 3.7.1
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
Copyright 1998-2009, SitePoint Pty Ltd. All Rights Reserved