CODEWITHSUNDEEP
phpmysql
aacanakin
aacanakin

Reputation: 2913

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

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

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 ?

Upvotes: 2

Views: 555

Answers (3)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

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;

Upvotes: 1

ben
ben

Reputation: 1936

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

Upvotes: 2

kingmaple
kingmaple

Reputation: 4310

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?

Upvotes: 1

Related Questions