时间戳

首页 > 解决方案 > How to update a date value that is blank on MSSQL?

php - How to update a date value that is blank on MSSQL?

问题描述

I am creating a form that extracts user info, and the users can update their info if needs be. On the form there are two date fields, the second field is optional. So when the user clicks UPDATE and the second date field is blank, in MSSQL database it updates the date_two field with "01/01/1970". I am using PHP with MSSQL, so in the code I have:

if(isset($_POST['update']) {

   $date_two = $_POST['date_two'];

   if($date_two == "")
      $date_two == NULL;
   else
     $date_two = date("Y-m-d", strtotime($date_two) );

   UPDATE table SET date_two = CAST('$date_two' as DATE2);

}

The resulted entry is 01/01/1970, instead of NULL.

标签： phpsql-server

解决方案

你应该检查你的输入日期值是否是一个空字符串（''）并管理为空，因为如果你的 var 的值不为空（也是一个空字符串），更新会产生一个日期值

    UPDATE table SET date_two = (case when  '$date_two' = '' then NULL 
                          else  CAST('$date_two' as DATE2)
                          end);

无论如何，你不应该在你的 sql 命令中使用 php var 你在 rusk 进行 sqlinjection .. 看看你的数据库驱动程序 fior 准备好的命令和绑定参数 ..

推荐阅读