If no value is posted insert NULL into SQL database

Question

I know there are many questions in this subject, but I just can't understand. I'm making an insert into a database with a post with php. Whenever my fields are empty it is inserting a '0' in the database instead of a NULL value... My code is as follows:

<?php
$host=""; // Host name 
$username=""; // Mysql username 
$contra=""; // Mysql password 
$db_name=""; // Database name 

// Connect to server and select database.
$dbh= mysql_connect("$host", "$username", "$contra")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from form 
$var1=$_POST["var1"]; 
$var2=$_POST["var2"]; 
$var3=$_POST["var3"]; 
$var4=$_POST["var4"]; 

// To protect MySQL injection
$var1=stripslashes($var1);
$var2=stripslashes($var2);
$var3=stripslashes($var3);
$var4=stripslashes($var4);

$var1 = mysql_real_escape_string($var1);
$var2 = mysql_real_escape_string($var2);
$var3 = mysql_real_escape_string($var3);
$var4 = mysql_real_escape_string($var4);

            $sql="INSERT INTO PREGUNTAS (Var1, Var2, Var3, Var4) VALUES('$var1', '$var2', '$var3', '$var4');";
            $table= mysql_query($sql, $dbh) or die ("you've got a problem");            
                    }
    break;
}
 ?>

I tried starting the variable like this

$var2 = isset($_POST["var2"]) ? "'$var2'" : "NULL";

and

if($_POST['var2'] == "") $_POST['var2'] = "NULL";

but none of them worked, the query is still adding 0's.

and I am getting the following error/notice:

Notice: Undefined index: var2 in /.....php on line ...

Could you please help me?

Thanks!

because `'NULL'` is a string while `NULL` (without the quote) indicates null value — Fabricator, Jun 11 '14 at 19:37
Probably the default values set in your database for these columns is 0. So when NULL is passed 0 (the default) is entered. — Nawed Khan, Jun 11 '14 at 19:38

score 2 · Accepted Answer · answered Jun 11 '14 at 19:38

You've quoted your field values in the query, so even if you set a particular variable to be the string NULL in PHP, you'd still be trying to do

INSERT ... VALUES ('NULL', ...)

An SQL NULL is a keyword, It cannot be quoted if you want the DB to treat it as an actual null value, e.g.

INSERT ... VALUES(NULL, 'NULL', ...)
                  ^^^^---actual SQL null
                        ^^^^^^--- a string which contains the letters N, U, L, L

That means you need to rebuild your query differently:

if ($_POST['var1'] == '') {
    $db_var1 = 'NULL';
} else {
    $db_var1 = "'" . mysql_real_escape_string($_POST['var1']) . "'";
}

INSERT ... VALUES($db_var1, ...)

OP -> consider use of mysqli to better help avoid sql injection — Dave, Jun 11 '14 at 20:02

score 0 · Answer 2 · answered Jun 11 '14 at 19:36

0

Use empty() function instead

$var2 = !empty($_POST["var2"]) ? "'$var2'" : NULL;

isset() will be always true since the input field is submitted, also avoid the use of double quotes with NULL otherwise you will insert a string

answered Jun 11 '14 at 19:36

Fabio

23,183
12
55
64

1

This will continue inserting NULL as string.. – hlscalon Jun 11 '14 at 19:38

score 0 · Answer 3 · answered Jun 11 '14 at 19:44

try this:

$fields = array('var1','var2','var3','var4');
$values = array();
foreach($fields as $field) :
    $field = isset($_POST[$field])? trim($_POST[$field]) : null;
    if(is_null($field)) continue;
    $values[$field] = "'".mysql_real_escape_string($field)."'";
endforeach;

if(is_array($values) AND !empty($values)) :
    $query = "INSERT INTO PREGUNTAS (".implode(',', array_keys($values)).") VALUES (".implode(',', array_values($values)).")";
    $table= mysql_query($query, $dbh) or die ("you've got a problem");  
endif;

OR THIS:

try this:

$fields = array('var1','var2','var3','var4');
$values = array();
foreach($fields as $field) :
    $field = isset($_POST[$field])? trim($_POST[$field]) : null;
    if(is_null($field)) {
        $values[$field] = 'NULL',
        continue;
    }
    $values[$field] = "'".mysql_real_escape_string($field)."'";
endforeach;

if(is_array($values) AND !empty($values)) :
    $query = "INSERT INTO PREGUNTAS (".implode(',', array_keys($values)).") VALUES (".implode(',', array_values($values)).")";
    $table= mysql_query($query, $dbh) or die ("you've got a problem");  
endif;

If no value is posted insert NULL into SQL database

3 Answers3