CODEWITHSUNDEEP
phpmysqlpdo
user1818517
user1818517

Reputation: 59

Error with PDO INSERT statement

I've been staring at this statement and its error message for about half an hour without being able to see what's wrong with it.

Here is my statement:

try{
    $stmt = $conn->prepare("INSERT INTO dashboardsearchdates (userID, from, to) VALUES (?,?,?)");
    $result = $stmt->execute(array($userID, $frmFrom, $frmTo));
}
    catch(PDOException $e){
    echo 'ERROR: ' . $e->getMessage();
    $queryString = $stmt->queryString;
    $page = $_SERVER['SERVER_NAME'] . $_SERVER['REQUEST_URI'];
    mail(ADMIN_EMAIL, 'SQL ERROR: ' . $page, 'Error Page: ' . $page . '     //     Error: ' . $e->getMessage() . '     //     Query String: ' . $queryString);
}

And this is the table structure i'm trying to insert the values into:

Table structure for table `dashboardsearchdates`

--

CREATE TABLE IF NOT EXISTS `dashboardsearchdates` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `from` datetime NOT NULL,
  `to` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Here is the exception i'm seeing:

ERROR: 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 'from, to) VALUES ('9','2012-12-12','2013-01-01')' at line 1

As you can see from the exception, the three values i'm trying to insert into the DB are present at runtime (otherwise they wouldn't be shown in the exception)

Things i've tried:

I have other queries using $conn above this in the code, so i know $conn is present and working.

I've rewritten the statement as so:

$stmt = $conn->prepare("INSERT INTO dashboardsearchdates (userID, from, to) VALUES (:userID,:from,:to)");
$stmt->bindParam(':userID', $userID);
$stmt->bindParam(':from', $frmFrom);
$stmt->bindParam(':to', $frmTo);                        
$result = $stmt->execute();

I've tried backquotes around the table name, all the field names, only the integer field name, and only the date fields.. All with the same error message shown above - as far as i can see there's nothing wrong with the construction of the SQL.

I'd be really grateful for any suggestions of other things to try.

Upvotes: 0

Views: 100

Answers (2)

zkanoca
zkanoca

Reputation: 9918

From is a reserved word for SQL, so if you want to use it you have to use before and after back ticks.

INSERT INTO dashboardsearchdates (userID, `from`, to) VALUES (?,?,?)"

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157872

'from' is a key word you need to enclose it in backticks

`from`

'to' is a keyword as well

Note: this problem has nothing to do with PDO. A developer ought to test their query in mysql client before starting for build it dynamically.

Upvotes: 2

Related Questions