php mysql query doesn't send date and time types null to insert

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 ?

Could you echo out $query of the one you posted in 'Notes' point 1? I am curious what the query itself is, maybe you're looking for the problem in the wrong place. — kingmaple
– kingmaple, Commented Feb 20, 2012 at 21:03
task_date_time is simply a json variable. for instance, "20/02/2012 22:48" — aacanakin
– aacanakin, Commented Feb 20, 2012 at 21:03

ben · Accepted Answer · 2012-02-20 21:06:54Z

2

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]."'";

answered Feb 20, 2012 at 21:06

ben

1,9362 gold badges18 silver badges26 bronze badges

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

1 Comment

aacanakin Over a year ago

it's almost correct but date field is null when I enter a date which is not null.

kingmaple · Accepted Answer · 2012-02-20 21:00:58Z

1

Your query string is not properly surrounded by quotes, I believe you need to place quotes around your $task_date and $task_time, since they currently seem to not validate in the query string. Try these:

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

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

EDIT: Apparently you already tried that. But the error should be different in that case, was it?

answered Feb 20, 2012 at 21:00

kingmaple

4,3705 gold badges35 silver badges44 bronze badges

Comments

Simon at The Access Group · Accepted Answer · 2012-02-20 21:30:43Z

1

MySQL expects a time to be in format "HH:mm:ss", you are not including seconds, and so need to append the ':00' on the end i.e.

$query = "CALL insertProcedure(...some variables...,  '".$task_date."'), '".$task_time.":00')";

You can see this yourself by doing the following query manually

SELECT TIME('12:34') AS fails, TIME('12:34:00') AS succeeds;

edited Feb 20, 2012 at 21:30

answered Feb 20, 2012 at 21:03

Simon at The Access Group

7,0251 gold badge25 silver badges47 bronze badges

5 Comments

aacanakin Over a year ago

I tried that query. if date and time both not null, date is correct, but time is 00:00. if they're both null, date is correct, time is 00:00

Simon at The Access Group Over a year ago

Added logic to deal with the timestamp conversion + added falling back to MySQL to split TIME and DATE segments

aacanakin Over a year ago

I don't need to split date field again. my sql procedure handles that with STR_TO_DATE format conversion, but thank you :D

aacanakin Over a year ago

Moreover, the type isn't DATETIME. We use date & time types seperately

Simon at The Access Group Over a year ago

It doesn't matter if it's DATETIME, DATE() extracts the date portion, and TIME() extracts the time portion, just saves splitting it out in the first place but if you've STR_TO_DATE doing that already it's not an issue. Will update it to just have the :00 then, as I did a test and TIME('12:34') returns NULL while TIME('12:34:00') returns '12:34:00' TIME value

Collectives™ on Stack Overflow

php mysql query doesn't send date and time types null to insert

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related