CODEWITHSUNDEEP
phpmysqlpdo
Andrew M
Andrew M

Reputation: 4288

SQL Syntax Error with PDO and MySQL, Not Anywhere Else

I created a table in Sequel Pro and exported the create table syntax. To my eye, everything looks fine. The issue is, I'm trying to run the query using PDO and am encountering a syntax error: 

General error: 1 near "UNSIGNED": syntax error

The query works just fine if I run it in Sequel Pro again, or if I run it in PHPMyAdmin. Any clues as to why this query is failing in PDO only?

CREATE TABLE `users` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `name` varchar(30) NOT NULL,
  `email` varchar(50) NOT NULL,
  `password` varchar(60) NOT NULL,
  `passwordResetKey` varchar(8) DEFAULT NULL,
  `activationKey` varchar(8) DEFAULT NULL,
  `permissions` smallint(5) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Note: I included line breaks above for clarity, but there's no line breaks in the query that's run.

The code that runs the query:

$query = 'CREATE TABLE `users` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, `parent_id` int(11) UNSIGNED DEFAULT NULL, `name` varchar(30) NOT NULL, `email` varchar(50) NOT NULL, `password` varchar(60) NOT NULL, `passwordResetKey` varchar(8) DEFAULT NULL, `activationKey` varchar(8) DEFAULT NULL, `permissions` smallint(5) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`), CONSTRAINT `users_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1';
$statement = $conn->prepare($query);
$statement->execute();

Upvotes: 1

Views: 251

Answers (1)

Andrew M
Andrew M

Reputation: 4288

In my case, it turns out that the issue was that I was not properly connecting to the database prior to running the query. The code is for a setup script, so it checked database credentials on a page previous to the page that ran the queries.

However, in the setup pages, the app is setup so that the database must be explicitly connected to (which occurred when I was checking credentials, but not running these queries). All I had to do was connect to the database properly, and the queries started functioning.

Upvotes: 2

Related Questions