update datetime with null value in mysql by php

Question

I'm using this code to update my datatime field to NULL, php version 7.3.7

    if($_POST['value']=='0000-00-00 00:00:00'){
      $timestamp=NULL;
    }else {
      $dateTime = $_POST['value'];
      $timestamp = date('Y-m-d H:i', strtotime($_POST['value']));
   }
$query="update forms set $_POST[limitInputField]='$timestamp' where  formid='$_POST[formId]'";
$result=$dbCnn->query($query)or die($dbCnn->error);

it gives error when $timestamp is null:

Incorrect datetime value: '' for column farsifor_m.forms.enddate at row 1

But when I quote "NULL" and remove quotations around $timestamp in the query, if $timestamp is null it works properly but if $timestamp value is not null it gives error.

   if($_POST['value']=='0000-00-00 00:00:00'){
     $timestamp="NULL";
   }else {
     $dateTime = $_POST['value'];
     $timestamp = date('Y-m-d H:i', strtotime($_POST['value']));
   }
  $query="update forms set $_POST[limitInputField]=$timestamp where       formid='$_POST[formId]'";
    $result=$dbCnn->query($query)or die($dbCnn->error);

Use prepared statements. NULL is not the same as an empty string, so it's erroring out. Using prepared statements bypasses this issue. Here are the instructions for PDO and mysqli — aynber
– aynber, Commented Sep 3, 2019 at 14:58
is there any way else? my codes work properly in php 5.6. but not in 7.3.7 — jmix
– jmix, Commented Sep 3, 2019 at 15:04
I'm surprised it works in 5.6. Prepared statements and parameter binding is the safest way to pass data into SQL queries, and prevents a ton of headaches. — aynber
– aynber, Commented Sep 3, 2019 at 15:10

Salman Arshad · Accepted Answer · 2019-09-03 18:38:07Z

2

Learn to use prepared statements; and do not inject post variables to "build" the query. Having said that:

You can use NULLIF for convenience. In the following example the specific value 0000-00-00 00:00:00 will be converted to NULL:

UPDATE forms SET col = NULLIF(:timestamp, '0000-00-00 00:00:00') WHERE formid = :formid

Or you can simply:

UPDATE forms SET col = :timestamp WHERE formid = :formid

And use PHP to pass a variable containing string or null.

edited Sep 3, 2019 at 18:38

answered Sep 3, 2019 at 15:17

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jmix Over a year ago

how to use this kind of query in prapare statement?

DunBoaSA · Accepted Answer · 2019-09-03 15:12:12Z

0

You can resolve this with some slight changes to your code (take note of single and double quotes):

    if($_POST['value']=='0000-00-00 00:00:00'){
      $timestamp="NULL";
    }else {
      $dateTime = $_POST['value'];
      $timestamp = "'".date('Y-m-d H:i', strtotime($_POST['value']))."'";
   }
$query="update forms set $_POST[limitInputField]=$timestamp where  formid='$_POST[formId]'";

This way if you're setting a NULL value in timestamp the query would look something like this:

update forms set limitInputField=NULL where formid='123'

But if you're putting a value in there it would look like this:

update forms set limitInputField='2019-09-03 17:08' where formid='123'

Important to note there are no quotes around the NULL when setting the value, but there are around the date.

edited Sep 3, 2019 at 15:12

answered Sep 3, 2019 at 15:11

DunBoaSA

265 bronze badges

3 Comments

Cid Over a year ago

This answer may be downvoted since you left SQL Injections vulnerabilities.

DunBoaSA Over a year ago

I'm just basing this on his code for ease of understanding. Also - it would have to be a damn clever hacker to get SQL injection working through that date function. :P (But thanks for pointing that out. I'm new to contributing here.)

Cid Over a year ago

What about $_POST[limitInputField] ? $_POST[formId] ? Always use prepared statement.

Pakornsan Panpaen · Accepted Answer · 2021-08-25 08:45:17Z

0

$date = $_POST['Date']; //get date from form
$time = $_POST['Time']; //get time from form

if(strtotime($date.$time)==0){ // check if null or not 
    $AboutDate = "0000-00-00 00:00:00"; 
}else{                        
    $AboutDate = date("Y-m-d H:i", strtotime($date.$time)); 
}

Then Update/Insert into SQL

UPDATE xxx(table) SET xxx(column) = NULLIF('$AboutDate','0000-00-00 00:00:00') WHERE xxxx = xxxxx

Let's go BABY

answered Aug 25, 2021 at 8:45

Pakornsan Panpaen

1

Collectives™ on Stack Overflow

update datetime with null value in mysql by php

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related