Prepare seems to be stripping quotes from string so that it is not accepted by the mysql datetime field

Question

I would like to update a DATETIME mysql field using a php STRING in a prepared statement

$stmt = $mysqli->prepare("UPDATE TABLE1 SET DATETIME1 = ? where ID = ?");
$stmt->bind_param('si',$date,$id);
$date = "2013-12-04 00:00:00";  /*string '2013-12-04 00:00:00' (length=19)*/
$id = 4;
$stmt->execute();

I had expect that mysql should treat the statement as

UPDATE TABLE1 SET DATETIME1 = '2013-12-04 00:00:00' where ID = ?; 
/*which works when directly entered*/

However I assume it is treating like

UPDATE TABLE1 SET DATETIME1 = 2013-12-04 00:00:00 where ID = ?; 
/*giving the result of null*/

I have tried adding using the STR_TO_DATE mysql function to force it to treat the $date as a string and then convert it to DATETIME. ie

$stmt = $mysqli->prepare("UPDATE TABLE1 SET DATETIME1 = STR_TO_DATE(?,'%Y-%m-%d %T') where ID = ?"); 
/*again the result is null*/

Do I need to bind a quoted string? what am I missing?

Yeah, what mamdouh says. You are binding an empty value here — Pekka, Dec 04 '13 at 00:40
thanks for your replies @mamdouhalramadan They bind first and then define here? http://php.net/manual/en/mysqli-stmt.bind-param.php What have I misunderstood? — rosscosack, Dec 04 '13 at 00:51

score 0 · Accepted Answer · edited May 23 '17 at 12:13

It makes no much sense to prepare it, bind it and then execute it in such an obscure way. Besides the problems outlined on the comments, consider changing it to:

$date = "2013-12-04 00:00:00";  /*string '2013-12-04 00:00:00' (length=19)*/
$id = 4;

$stmt = $mysqli->prepare("UPDATE TABLE1 SET DATETIME1 = ? where ID = ?");
$stmt->execute(array($date, $id));

Besides, you were binding them wrong. You were using the prepare statement but then binding three values (or two values and a wrongly set parameter). Please refer to the documentation for more info about binding parameters.

Note that with PHP >= 5.4 you can simply do:

$date = "2013-12-04 00:00:00";  /*string '2013-12-04 00:00:00' (length=19)*/
$id = 4;

$stmt = $mysqli->prepare("UPDATE TABLE1 SET DATETIME1 = ? where ID = ?");
$stmt->execute([$date, $id]);

Prepare seems to be stripping quotes from string so that it is not accepted by the mysql datetime field

1 Answers1