8 Replies - 2825 Views - Last Post: 03 April 2010 - 10:09 PM

#1 gregwhitworth   User is offline

  • Tired.
  • member icon

Reputation: 221
  • View blog
  • Posts: 1,613
  • Joined: 20-January 09

SQL Syntax Error

Posted 03 April 2010 - 08:31 PM

Here is what I am posting:
CREATE TABLE 'users' (
'userid' INTEGER AUTO_INCREMENT DEFAULT NULL ,
'username' VARCHAR(20) DEFAULT NULL ,
'password' VARCHAR(20) DEFAULT NULL ,
'timestamp' TIMESTAMP DEFAULT NULL ,
'userlevel' VARCHAR(20) DEFAULT NULL ,
'email' VARCHAR(50) DEFAULT NULL ,
PRIMARY KEY ('userid', 'username')
);

CREATE TABLE 'Active Guests' (
'id' INTEGER AUTO_INCREMENT DEFAULT NULL ,
'timestamp' TIMESTAMP DEFAULT NULL ,
PRIMARY KEY ('id')
);

CREATE TABLE 'Active users' (
'username' VARCHAR(10) AUTO_INCREMENT DEFAULT NULL ,
'timestamp' TIMESTAMP DEFAULT NULL ,
PRIMARY KEY ('username')
);

CREATE TABLE 'Banned Users' (
'username' VARCHAR(20) AUTO_INCREMENT DEFAULT NULL ,
'new field' TIMESTAMP DEFAULT NULL ,
PRIMARY KEY ('username')
);

CREATE TABLE 'items' (
'id' INTEGER AUTO_INCREMENT DEFAULT NULL ,
'title' MEDIUMTEXT DEFAULT NULL ,
'price' DECIMAL(10) DEFAULT NULL ,
'description' BLOB DEFAULT NULL ,
'images' MEDIUMTEXT DEFAULT NULL ,
'links' MEDIUMTEXT DEFAULT NULL ,
'status' MEDIUMTEXT DEFAULT NULL ,
'tags' MEDIUMTEXT DEFAULT NULL ,
'purchased' INTEGER DEFAULT NULL ,
'rating' INTEGER DEFAULT NULL ,
'wgPick' MEDIUMTEXT DEFAULT NULL ,
'username' INTEGER DEFAULT NULL ,
'medium' MEDIUMTEXT DEFAULT NULL ,
'style' MEDIUMTEXT DEFAULT NULL ,
'sizes' VARCHAR(100) DEFAULT NULL ,
'timestamp' TIMESTAMP DEFAULT NULL ,
PRIMARY KEY ('id')
);

CREATE TABLE 'userInfo' (
'id' INTEGER AUTO_INCREMENT DEFAULT NULL ,
'firstName' MEDIUMTEXT DEFAULT NULL ,
'lastName' MEDIUMTEXT DEFAULT NULL ,
'city' MEDIUMTEXT DEFAULT NULL ,
'state' MEDIUMTEXT DEFAULT NULL ,
'status' MEDIUMTEXT DEFAULT NULL ,
'website' MEDIUMTEXT DEFAULT NULL ,
'username' MEDIUMTEXT DEFAULT NULL ,
PRIMARY KEY ('id', 'username')
);

ALTER TABLE 'Active users' ADD FOREIGN KEY (username) REFERENCES 'users' ('username');
ALTER TABLE 'Banned Users' ADD FOREIGN KEY (username) REFERENCES 'users' ('username');
ALTER TABLE 'items' ADD FOREIGN KEY (username) REFERENCES 'userInfo' ('username');
ALTER TABLE 'userInfo' ADD FOREIGN KEY (username) REFERENCES 'users' ('username');



But I am getting this error from PHP MyAdmin:

Quote

#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 ''users' ( 'userid' INTEGER AUTO_INCREMENT DEFAULT NULL , 'username' VARCHAR(20' at line 1


I have glanced at it over and over but I can't see the syntax error, anybody got any suggestions?

--

Greg

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Syntax Error

#2 no2pencil   User is offline

  • Professor Snuggly Pants
  • member icon

Reputation: 6833
  • View blog
  • Posts: 31,498
  • Joined: 10-May 07

Re: SQL Syntax Error

Posted 03 April 2010 - 08:33 PM

How can it auto increment a null value? I would think that you want to default to zero.
Was This Post Helpful? 1
  • +
  • -

#3 gregwhitworth   User is offline

  • Tired.
  • member icon

Reputation: 221
  • View blog
  • Posts: 1,613
  • Joined: 20-January 09

Re: SQL Syntax Error

Posted 03 April 2010 - 09:05 PM

Ok, so I tried default 0 and default zero, now I've removed all defaults - and it still won't go through.

CREATE TABLE 'users' (
'userid' INTEGER AUTO_INCREMENT ,
'username' VARCHAR(20) ,
'password' VARCHAR(20) ,
'timestamp' TIMESTAMP ,
'userlevel' VARCHAR(20) ,
'email' VARCHAR(50) ,
PRIMARY KEY ('userid', 'username')
);

CREATE TABLE 'Active Guests' (
'id' INTEGER AUTO_INCREMENT ,
'timestamp' TIMESTAMP ,
PRIMARY KEY ('id')
);

CREATE TABLE 'Active users' (
'username' VARCHAR(10) AUTO_INCREMENT ,
'timestamp' TIMESTAMP ,
PRIMARY KEY ('username')
);

CREATE TABLE 'Banned Users' (
'username' VARCHAR(20) AUTO_INCREMENT ,
'new field' TIMESTAMP ,
PRIMARY KEY ('username')
);

CREATE TABLE 'items' (
'id' INTEGER AUTO_INCREMENT ,
'title' MEDIUMTEXT ,
'price' DECIMAL(10) ,
'description' BLOB ,
'images' MEDIUMTEXT ,
'links' MEDIUMTEXT ,
'status' MEDIUMTEXT ,
'tags' MEDIUMTEXT ,
'purchased' INTEGER ,
'rating' INTEGER ,
'wgPick' MEDIUMTEXT ,
'username' INTEGER ,
'medium' MEDIUMTEXT ,
'style' MEDIUMTEXT ,
'sizes' VARCHAR(100) ,
'timestamp' TIMESTAMP ,
PRIMARY KEY ('id')
);

CREATE TABLE 'userInfo' (
'id' INTEGER AUTO_INCREMENT ,
'firstName' MEDIUMTEXT ,
'lastName' MEDIUMTEXT ,
'city' MEDIUMTEXT ,
'state' MEDIUMTEXT ,
'status' MEDIUMTEXT ,
'website' MEDIUMTEXT ,
'username' MEDIUMTEXT ,
PRIMARY KEY ('id', 'username')
);

ALTER TABLE 'Active users' ADD FOREIGN KEY (username) REFERENCES 'users' ('username');
ALTER TABLE 'Banned Users' ADD FOREIGN KEY (username) REFERENCES 'users' ('username');
ALTER TABLE 'items' ADD FOREIGN KEY (username) REFERENCES 'userInfo' ('username');
ALTER TABLE 'userInfo' ADD FOREIGN KEY (username) REFERENCES 'users' ('username');



Thanks for the help, pencil.
Was This Post Helpful? 0
  • +
  • -

#4 no2pencil   User is offline

  • Professor Snuggly Pants
  • member icon

Reputation: 6833
  • View blog
  • Posts: 31,498
  • Joined: 10-May 07

Re: SQL Syntax Error

Posted 03 April 2010 - 09:07 PM

Try INT rather than INTEGER

Quote

'userid' INTEGER AUTO_INCREMENT ,
'username' VARCHAR(20) ,


Was This Post Helpful? 0
  • +
  • -

#5 gregwhitworth   User is offline

  • Tired.
  • member icon

Reputation: 221
  • View blog
  • Posts: 1,613
  • Joined: 20-January 09

Re: SQL Syntax Error

Posted 03 April 2010 - 09:17 PM

Ok, changed that as well as removed auto_increment from varchar items. But still there is an error, here is the updated statement:

CREATE TABLE 'users' (
'userid' INT AUTO_INCREMENT ,
'username' VARCHAR(20) ,
'password' VARCHAR(20) ,
'timestamp' TIMESTAMP ,
'userlevel' VARCHAR(20) ,
'email' VARCHAR(50) ,
PRIMARY KEY ('userid', 'username')
);

CREATE TABLE 'Active Guests' (
'id' INT AUTO_INCREMENT ,
'timestamp' TIMESTAMP ,
PRIMARY KEY ('id')
);

CREATE TABLE 'Active users' (
'username' VARCHAR(10) ,
'timestamp' TIMESTAMP ,
PRIMARY KEY ('username')
);

CREATE TABLE 'Banned Users' (
'username' VARCHAR(20) ,
'new field' TIMESTAMP ,
PRIMARY KEY ('username')
);

CREATE TABLE 'items' (
'id' INT AUTO_INCREMENT ,
'title' MEDIUMTEXT ,
'price' DECIMAL(10) ,
'description' BLOB ,
'images' MEDIUMTEXT ,
'links' MEDIUMTEXT ,
'status' MEDIUMTEXT ,
'tags' MEDIUMTEXT ,
'purchased' INT ,
'rating' INT ,
'wgPick' MEDIUMTEXT ,
'username' INT ,
'medium' MEDIUMTEXT ,
'style' MEDIUMTEXT ,
'sizes' VARCHAR(100) ,
'timestamp' TIMESTAMP ,
PRIMARY KEY ('id')
);

CREATE TABLE 'userInfo' (
'id' INT AUTO_INCREMENT ,
'firstName' MEDIUMTEXT ,
'lastName' MEDIUMTEXT ,
'city' MEDIUMTEXT ,
'state' MEDIUMTEXT ,
'status' MEDIUMTEXT ,
'website' MEDIUMTEXT ,
'username' MEDIUMTEXT ,
PRIMARY KEY ('id', 'username')
);

ALTER TABLE 'Active users' ADD FOREIGN KEY (username) REFERENCES 'users' ('username');
ALTER TABLE 'Banned Users' ADD FOREIGN KEY (username) REFERENCES 'users' ('username');
ALTER TABLE 'items' ADD FOREIGN KEY (username) REFERENCES 'userInfo' ('username');
ALTER TABLE 'userInfo' ADD FOREIGN KEY (username) REFERENCES 'users' ('username');


Was This Post Helpful? 0
  • +
  • -

#6 no2pencil   User is offline

  • Professor Snuggly Pants
  • member icon

Reputation: 6833
  • View blog
  • Posts: 31,498
  • Joined: 10-May 07

Re: SQL Syntax Error

Posted 03 April 2010 - 09:18 PM

By chance, is the database selected? &/or is there already a table named users?
Was This Post Helpful? 1
  • +
  • -

#7 gregwhitworth   User is offline

  • Tired.
  • member icon

Reputation: 221
  • View blog
  • Posts: 1,613
  • Joined: 20-January 09

Re: SQL Syntax Error

Posted 03 April 2010 - 09:30 PM

Yes the db is selected an there are no tables in the db - here's a screenshot:
Attached Image
Was This Post Helpful? 0
  • +
  • -

#8 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6259
  • View blog
  • Posts: 24,028
  • Joined: 23-August 08

Re: SQL Syntax Error

Posted 03 April 2010 - 09:55 PM

Are the single quotes around the table and data names something that's required for phpmyadmin? That's not syntax I am accustomed to seeing.
Was This Post Helpful? 1
  • +
  • -

#9 gregwhitworth   User is offline

  • Tired.
  • member icon

Reputation: 221
  • View blog
  • Posts: 1,613
  • Joined: 20-January 09

Re: SQL Syntax Error

Posted 03 April 2010 - 10:09 PM

Ok, did some searching and I had changed what I thought were 'not true' apostrophes so I re-did it and then fixed some errors but finally got it to go through alright. Here's the final code:

CREATE TABLE `users` (
`userid` INTEGER AUTO_INCREMENT,
`username` VARCHAR(20) DEFAULT NULL ,
`password` VARCHAR(20) DEFAULT NULL ,
`timestamp` VARCHAR(20),
`userlevel` VARCHAR(20) DEFAULT NULL ,
`email` VARCHAR(50) DEFAULT NULL ,
PRIMARY KEY (`userid`, `username`)
);

CREATE TABLE `Active Guests` (
`id` INTEGER AUTO_INCREMENT,
`timestamp` VARCHAR(20),
PRIMARY KEY (`id`)
);

CREATE TABLE `Active users` (
`username` VARCHAR(10),
`timestamp` VARCHAR(20),
PRIMARY KEY (`username`)
);

CREATE TABLE `Banned Users` (
`username` VARCHAR(20),
PRIMARY KEY (`username`)
);

CREATE TABLE `items` (
`id` INTEGER AUTO_INCREMENT,
`title` MEDIUMTEXT DEFAULT NULL ,
`price` DECIMAL(10) DEFAULT NULL ,
`description` BLOB DEFAULT NULL ,
`images` MEDIUMTEXT DEFAULT NULL ,
`links` MEDIUMTEXT DEFAULT NULL ,
`status` MEDIUMTEXT DEFAULT NULL ,
`tags` MEDIUMTEXT DEFAULT NULL ,
`purchased` INTEGER DEFAULT NULL ,
`rating` INTEGER DEFAULT NULL ,
`wgPick` MEDIUMTEXT DEFAULT NULL ,
`username` VARCHAR(20) ,
`medium` MEDIUMTEXT DEFAULT NULL ,
`style` MEDIUMTEXT DEFAULT NULL ,
`sizes` VARCHAR(100) DEFAULT NULL ,
`timestamp` VARCHAR(20),
PRIMARY KEY (`id`)
);

CREATE TABLE `userInfo` (
`id` INTEGER AUTO_INCREMENT,
`firstName` MEDIUMTEXT DEFAULT NULL ,
`lastName` MEDIUMTEXT DEFAULT NULL ,
`city` MEDIUMTEXT DEFAULT NULL ,
`state` MEDIUMTEXT DEFAULT NULL ,
`status` MEDIUMTEXT DEFAULT NULL ,
`website` MEDIUMTEXT DEFAULT NULL ,
`username` VARCHAR(20) DEFAULT NULL ,
PRIMARY KEY (`id`, `username`)
);

ALTER TABLE `Active users` ADD FOREIGN KEY (username) REFERENCES `users` (`username`);
ALTER TABLE `Banned Users` ADD FOREIGN KEY (username) REFERENCES `users` (`username`);
ALTER TABLE `items` ADD FOREIGN KEY (username) REFERENCES `userInfo` (`username`);
ALTER TABLE `userInfo` ADD FOREIGN KEY (username) REFERENCES `users` (`username`);


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1