SQL Syntax Error with PDO and MySQL, Not Anywhere Else

Question

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();

I have to ask - are you connecting to a MySQL database via PDO, or some other RDBMS driver? — Michael Berkowski
– Michael Berkowski, Commented Apr 17, 2014 at 16:37
I'm connecting to MySQL via PDO using the MySQL DSN (mysql:host=...). — Andrew M
– Andrew M, Commented Apr 17, 2014 at 16:39
The variable $query was posted in the original question, minus the line breaks. — Andrew M
– Andrew M, Commented Apr 17, 2014 at 16:41

Andrew M · Accepted Answer · 2014-04-17 17:00:53Z

2

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.

answered Apr 17, 2014 at 17:00

Andrew M

4,29811 gold badges46 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

SQL Syntax Error with PDO and MySQL, Not Anywhere Else

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