php mysql查询不发送要插入的日期和时间类型null

Question

I looked at similar questions but I can't solve the problem. I have 2 different columns with types date and time in MySql. I use explode to split date from time. The split code is the following ;

$task_date_time = $data['task_date_time'];

if($task_date_time != "")
{           
    $date_time = explode(" ", $task_date_time); // split according to the delimiter
    $task_date = $date_time[0];         
    $task_time = $date_time[1];         
}
else
{           
    $task_date = NULL;
    $task_time = NULL;          
}

After that, I am calling the php function that inserts this variables to MySQL database. My query inside the data function is the following ;

$query = "call insertProcedure(...some variables...,  ".$task_date.", ".$task_time.")";

However, query gives the following error ;

Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':48)' at line 1

• The ':48)' field corresponds to minute field of time type.

Notes :

1) I also tried the query string below. it doesn't work either

$query = "call insertProcedure(...some variables...,  '".$task_date."', '".$task_time."')";

2) $task_date and $task_time are nullable in MySQL database.

Any ideas ? Solutions ?

Answer 1

When it should be NULL:

$task_date = 'NULL';
$task_time = 'NULL';

You're setting the variables to NULL, which when cast to a string will produce an empty string. And mysql fails to parse it.

If you set the variables to the string 'NULL' mysql will correctly set the columns to be NULL

And when it should have a real value, the date and time need to be surrounded by quotes. e.g.:

$task_date = "'".$date_time[0]."'";         
$task_time = "'".$date_time[1]."'";