How to insert NULL Value in PHP

Question

I want to insert NULL Value in Database

$managerId = $_POST['managerId'];

if($managerId == 0)
{
    $managerId = NULL;
}
else
{
    $managerId = 14;
}
$mysql_user_resultset = mysqli_query($con, "INSERT into user (managerId) VALUES ('$managerId')");

I am using like this. Its not working, Value doesn't insert.

How to insert both Null value and some value in Same variable..

Its Because of '$managerId' - single quotation.

How to define for both the types

http://stackoverflow.com/questions/212939/how-do-i-modify-a-mysql-column-to-allow-null — cptnk, Apr 07 '14 at 08:27
You should definitely be using prepared statements for this. — cmbuckley, Apr 07 '14 at 08:30

score 2 · Answer 1 · answered Apr 07 '14 at 08:32

PHP concatenates the null value as an empty string. So, to get it work, try this :

if($managerId == 0)
{
    $managerIdInQuery = 'NULL';
}
else
{
    $managerIdInQuery = 14;
}
$mysql_user_resultset = mysqli_query($con, "INSERT into user (managerId) VALUES ($managerIdInQuery)");

Aioros · Accepted Answer · 2014-04-07T08:55:48.710

1

When you concatenate the $managerId variable, you are getting this query string:

"INSERT into user (managerId) VALUES ('')"

So, you are trying to insert an empty string. This is not what you want. The easiest way is to remove the quotes, like

"INSERT into user (managerId) VALUES ($managerId)"

This will work if your managerId field can be null, and still be valid for integer values.

edited Apr 07 '14 at 08:55

answered Apr 07 '14 at 08:28

Aioros

4,373
1
18
21

1

`(string) NULL == ""`, not `"null"`. – cmbuckley Apr 07 '14 at 08:29
you could check if the value is string using is_string add add the quotes to the variable. – Fadey Apr 07 '14 at 08:30
Thanks cbuckley, I clearly got confused. I edited the answer. – Aioros Apr 07 '14 at 08:56

score 0 · Answer 3 · answered Apr 07 '14 at 08:34

Set the columns default value to NULL
Try this line:

$managerId = (int) $managerId; // Convert to INT to avoid SQL injection $mysql_user_resultset = mysqli_query($con, "INSERT into user (managerId) VALUES (".($managerId?$managerId:'DEFAULT').")");

If $managerId is set, $managerId will be inserted, elsewise the default value NULL.

How to insert NULL Value in PHP

3 Answers3