Using Mysqli bind_param with date and time columns?
PhpMysqlMysqliPhp Problem Overview
How do you insert data into a MySQL date or time column using PHP mysqli and bind_param?
Php Solutions
Solution 1 - Php
Like any other string
$stmt = $mysqli->prepare('insert into foo (dt) values (?)');
$dt = '2009-04-30 10:09:00';
$stmt->bind_param('s', $dt);
$stmt->execute();
Solution 2 - Php
Timestamps in PHP are integers (the number of seconds from the UNIX epoch). An alternative to the above is to use an integer type date/time parameter, and the MySQL functions FROM_UNIXTIME and UNIX_TIMESTAMP
$stmt = $mysqli->prepare("INSERT INTO FOO (dateColumn) VALUES (FROM_UNIXTIME(?))");
$stmt->bind_param("i", $your_date_parameter);
$stmt->execute();
Solution 3 - Php
For the current date/time you can use the MySQL standard routine. You do not have to prepare that.
$query = "INSERT INTO tablename ";
$query .= "VALUES(?,?,?,NOW()) ";
$preparedquery = $dbaselink->prepare($query);
$preparedquery->bind_param("iii",$val1,$val2,$val3);
Solution 4 - Php
I used the date( ) function and this solved me the problem.
$stmt = $mysqli->prepare("INSERT INTO FOO (dateColumn) VALUES ?");
// 6/10/2015 10:30:00
$datetime = date("Y-m-d H:i:s", mktime(10, 30, 0, 6, 10, 2015));
$stmt->bind_param("s", $datetime);
$stmt->execute();
Solution 5 - Php
first set the date & time using date() function-
$date=date("d/m/y");
$time=date("h:i:sa");
then pass it into the prepared statement, just like any other string variable-
$stmt = $mysqli->prepare("INSERT INTO FOO (dateColumn, timeColumn) VALUES (?,?)");
$stmt->bind_param("ss", $date , $time);
$stmt->execute();