CODEWITHSUNDEEP
phpsqlsql-servernullsqlsrv
kristapmon
kristapmon

Reputation: 21

Inserting PHP NULL variable value into MSSQL query

I have this PHP IF statement that is working 'fine':

if(EMPTY($_POST['review_date'])){
    $review_date = NULL;
}
else {
    $review_date = $_POST['review_date'];
}

The problem starts when I pass on the variable to the sqlsrv_query: 

sqlsrv_query($connect,"UPDATE goals SET review_date='$review_date' WHERE id='$department'") or die(sqlsrv_errors());

In the case where IF statement is TRUE, the value in the review_date cell shows up as 1900-01-01, instead of NULL.

If I try to put quotes around the NULL value in the IF statement, I get the following error:

Notice: Array to string conversion in C:\xampp\htdocs\project\edit.php on line 30

If NULL is inserted into query directly, it executes as it should.

The Column Properties in the goals Table have Allow Nulls enabled and Default Value is set to (NULL).

Swifted through other questions but didn't really get anywhere.

What am I doing wrong here?

UPDATE 1:

As requested, here is the HTML code:

<form method="POST" action="edit.php">

Review date <i>(optional)</i>: <input type="date" name="review_date" value="" placeholder="dd/mm/yyyy" > 

<p><button type="submit">Save</button></p>

</form>

With regards to var_dumps:

var_dump($review_date);

Returned: NULL

var_dump($department);

Returned: string(2) "36" 

var_dump(sqlsrv_query($connect,"UPDATE goals SET review_date='$review_date' WHERE id='$department'"));

Returned: resource(7) of type (SQL Server Statement)

Upvotes: 1

Views: 4770

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133380

assign the NULL to var implies an empty date and not a null date so

you could use different condition for generate dinamically the code you need 

if(EMPTY($_POST['review_date'])){
    $code = ' SET review_date = NULL ' ;
}
else {
    $code = ' SET review_date = ' $_POST['review_date'];
}


sqlsrv_query($connect,"UPDATE goals ". $code . " WHERE id='$department'") or die(sqlsrv_errors());

be careful using php var in sql code you could be victim of sqlinjection

Upvotes: 0

coding Bott
coding Bott

Reputation: 4367

You code is dangerous because it contains SQL-injections!

You should do this:

if(EMPTY($_POST['review_date'])){
    $review_date = NULL;
}
else {
    $review_date = $_POST['review_date'];
}

$sql = "UPDATE goals SET review_date=? WHERE id=?";
$params = array($review_date, $department);

$stmt = sqlsrv_query( $conn, $sql, $params);

By using parameters in queries you can transfer this null value without problems.

ALWAYS USE PARAMETERS!

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

It is because whenever there is a missing value, send it as NULL and not as empty string. Empty string will be default to 1900-01-01

ex

declare @d datetime
set @d=''
select @d as date

result

1900-01-01 00:00:00

Upvotes: 0

Related Questions