PDO::PARAM for dates?
PhpMysqlDateTypesPdoPhp Problem Overview
Does some PDO::PARAM_??? exist which can be used for dates or timestamps?
Sample code:
$sql = "UPDATE my_table SET current_date = :date WHERE id = 43";
$statement = $pdo->prepare ($sql);
$statement->bindValue (":date", strtotime (date ("Y-m-d H:i:s")), PDO::PARAM_STR);
$statement->execute ();
Php Solutions
Solution 1 - Php
When writing a date in an SQL query, you are writing it as a string; you have to do the same with prepared statements, and use PDO::PARAM_STR, like you did in the portion of code you proposed.
And for the "timestamp", if by "timestamp" you mean:
- The MySQL timestamp data-type: it's the same, you'll pass it as a
string - The PHP Unix timestamp, which is an integer: you'll pass it an
int.
Solution 2 - Php
Simply creating the date using php date function should fix this issue for you.
$handle->execute(array(":date"=>date("Y-m-d H:i:s", strtotime($date)), PDO::PARAM_STR));
>
> Edit: Please note though, that strtotime
> (<http://php.net/manual/en/function.strtotime.php>;) can't handle every
> kind of date formats.
Solution 3 - Php
Nope. Treat date as a string.
Solution 4 - Php
You have to treat the date as string, but you can create a function to check if is a valid date before pass it as a param. Like this:
function checkValidDate($date, $format = "dd-mm-yyyy"){
if($format === "dd-mm-yyyy"){
$day = (int) substr($date,0,2);
$month = (int) substr($date, 3,2);
$year = (int) substr($date, 6,4);
}else if($format === "yyyy-mm-dd"){
$day = (int) substr($date,8,2);
$month = (int) substr($date, 5,2);
$year = (int) substr($date, 0,4);
}
return checkdate($month, $day, $year);
}
Solution 5 - Php
A complete section to properly store a DateTime value (as a string) in a RDBMS:
/** @const string app_date_format expected date format in the PHP domain (Swiss) */
define( 'app_date_format', 'd.m.Y' );
/** @var PDOConnection $db */
$db = new \PDO( $dsn, $db_user, $db_pass, $db_options );
/** @var DateTime $date */
$date = \DateTime::createFromFormat( app_date_format, '30.11.2020' );
$stmt = $db-> prepare(
"UPDATE `test`
SET `test_date` = STR_TO_DATE(:date, '%Y-%m-%d %H:%i:%s' )
WHERE `test`.`test_id` = :id"
);
$id = 1;
$stmt->bindValue( ':id', $id );
$stmt->bindValue( ':date', $date-> format( 'Y-m-d H:i:s'));
$stmt->execute() or die( $stmt-> errorInfo()[2] );
Tested with PHP 7.4.25; MariaDB 10.6.4
Solution 6 - Php
This worked for me.
//MS SQL
$sql = "UPDATE my_table SET current_date = GETDATE() WHERE id = 43";
$statement = $pdo->prepare ($sql);
//$statement->bindParam (":date", strtotime (date ("Y-m-d H:i:s")), PDO::PARAM_STR);
$statement->execute ();