Unable to insert datetime value into a MySQL table using PHP

Question

I'm trying to run the following code in 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:

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?

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

Arseni Mourzenko · Accepted Answer · 2015-05-09 18:53:50Z

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.

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?

answered May 9, 2015 at 18:53

Arseni Mourzenko

52.7k35 gold badges119 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to insert datetime value into a MySQL table using PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related