如果没有发布值，则将NULL插入SQL数据库[duplicate]

Question

This question already has an answer here:

• Set value to NULL in MySQL 9 answers

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!

</div>

Answer 1

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