CODEWITHSUNDEEP
phpmysqlnull
Jonah Katz
Jonah Katz

Reputation: 5288

Insert NULL variable into database

I have variable set to NULL that im trying to insert into a database but for some reason they keep getting submitted as '0'. Im positive that column im trying to inset into allows NULL and that the default is set to to NULL. Heres my code:

$insert = NULL;
$query = mysql_query("INSERT INTO `table1` (column1) VALUES ('$insert')") or die(mysql_error());

Upvotes: 0

Views: 3454

Answers (3)

Naftali
Naftali

Reputation: 146302

Warning:

Please, don't use mysql_* functions for new code. They are no longer maintained and the community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi.

IF you want it to be NULL (and you really really still want to use mysqli_*) in the database you can do the following:

$insert = NULL;
$query = mysql_query("INSERT INTO `table1` (column1) VALUES ("
                         .(($insert===NULL)?
                                 "NULL":
                                 "'".mysql_real_escape_string($insert)."'").
                     ")") or die(mysql_error());

But this could lead to nefarious SQL injection and is not recommended.

See Bobby Tables

So: all in all you should be using prepared statements.

You can use MySQLi like so:

        $dbHandle = new mysqli(...);
        $query = "INSERT INTO `table1` (column1) VALUES (?)";
        $statement = $dbHandle->prepare($query);
        if($statement){
            $statement->bind_param('s', $insert);
            if(!$statement->execute()){
                echo "Statement insert error: {$statement->error}";
            }
            $statement->close();
        }
        else {
            echo "Insert error: {$dbHandle->error}";
        }

Upvotes: 5

Mahesh Meniya
Mahesh Meniya

Reputation: 2605

Try this for static query:

$query = mysql_query("INSERT INTO `table1` (column1) VALUES (NULL)")  or die(mysql_error());

Using Variable :

$insert= NULL;
$insert = ($insert===NULL)? 'NULL' : "'$insert'";
mysql_query("INSERT INTO `table1` (column1) VALUES ($insert)") or die(mysql_error());

Upvotes: 1

craig1231
craig1231

Reputation: 3867

Try without the quotes;

$query = mysql_query("INSERT INTO `table1` (`column1`) VALUES (".$insert.")") or die(mysql_error());

The query should be;

INSERT INTO table1 (column1) VALUES (NULL);

Upvotes: 0

Related Questions