无法使用PHP将日期时间值插入MySQL表

I'm trying to run the following code in PHP:

我正在尝试在PHP中运行以下代码:

<?php
require_once 'Admin/Connector.php';

$name = 'A Schandillia';
$number = '12345678910';
$latitude = '73.897687';
$longitude = '11.958271';
$sql = 'INSERT INTO distilled_contacts (PHONE, POPULARNAME, PREFERREDNAME, LATITUDE, LONGITUDE, LASTSEEN) VALUES ';
$sql .= '(:num, :name, :name, :lat, :lon:, CURRENT_TIMESTAMP) ON DUPLICATE KEY UPDATE PREFERREDNAME = :name, LATITUDE = COALESCE(:lat, LATITUDE), LONGITUDE = COALESCE(:lon, LONGITUDE);';

$connect = dbconn(PROJHOST,PROJDB,PROJDBUSER,PROJDBPWD);

$query = $connect->prepare($sql);

$query->bindParam(':num', $number);
$query->bindParam(':name', $name);
$query->bindParam(':lat', $latitude);
$query->bindParam(':lon', $longitude);

$query->execute();

?>

However, it refuses to recognize CURRENT_TIMESTAMP and throws this error:

但是,它拒绝识别CURRENT_TIMESTAMP并抛出此错误:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 ':, "CURRENT_TIMESTAMP") ON DUPLICATE KEY UPDATE PREFERREDNAME = 'A Schandillia',' at line 1' in /Users/Amit/Sites/project/test.php:24 Stack trace: #0 /Users/Amit/Sites/project/test.php(24): PDOStatement->execute() #1 {main} thrown in /Users/Amit/Sites/project/test.php on line 24

I have even tried replacing it with NOW() but the problem persists. Is there no way to programmatically enter datetime value into a MySQL table?

我甚至尝试用NOW()替换它,但问题仍然存在。有没有办法以编程方式将日期时间值输入MySQL表?

P.S. Please don't suggest using timestamp data type instead of datetime on my table because that will not work in my scenario.

附:请不要建议在我的桌面上使用timestamp数据类型而不是datetime,因为这在我的场景中不起作用。

1 个解决方案

#1

The error is very explicit: you have an error in your SQL syntax. The error goes even into details: the wrong syntax is located at ":" character preceding CURRENT_TIMESTAMP.

错误非常明确:您的SQL语法中有错误。错误甚至细节:错误的语法位于CURRENT_TIMESTAMP之前的“:”字符处。

Now look closely at your query:

现在仔细查看您的查询:

                              ↴
:num, :name, :name, :lat, :lon:

What do you see at the right of lon? Is it somehow different from lat or name?

你在lon的右边看到了什么?它与lat或名字有什么不同吗?

#1