query synatx

Evulness · 07-18-2008, 04:41 PM

Code:

CREATE TABLE IF NOT EXISTS users (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        username VARCHAR(255) NOT NULL ,
        password VARCHAR(255) NOT NULL ,
        email TEXT NOT NULL ,
        ip VARCHAR(16) NOT NULL ,
        group INT(1) NOT NULL DEFAULT 1 ,
        banned VARCHAR(5) NOT NULL DEFAULT 'n'
        );";

produces this error...

Quote:

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 'group INT(1) NOT NULL DEFAULT 1 , banned VARCHAR(5) NOT NULL DEFAULT 'n' at line 7

why? i've used this exact query string in the past, but now it doesn't work?

drewbee · 07-18-2008, 06:19 PM

Try this:

Code:

CREATE TABLE IF NOT EXISTS `users` ( `id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 255 ) NOT NULL ,
`password` VARCHAR( 255 ) NOT NULL ,
`email` TEXT NOT NULL ,
`ip` VARCHAR( 16 ) NOT NULL ,
`group` INT( 1 ) NOT NULL DEFAULT '1',
`banned` VARCHAR( 5 ) NOT NULL DEFAULT 'n',
PRIMARY KEY ( `id` ) 
) ENGINE = MYISAM

Evulness · 07-18-2008, 06:50 PM

Thank you :)

buggabill · 07-21-2008, 04:04 PM

The reason your query failed is that GROUP is a reserved word in MySQL. Be careful in your queries down the road as they will all require the backticks surrounding that field name.

You may wish to change this now to save headaches later.

Village Idiot · 07-21-2008, 04:22 PM

This is why I always use ticks on my queries, always. It is bad practice to use a keyword for a name, but it just makes things more bug-proof.

Evulness · 07-22-2008, 01:29 PM

OMG LOL i totaly forgot about GROUP.

Sorry it has been a hectic week. been 2 weekends working on my bathroom, and working double time on other side projects during the week, after coming home from working my 9-5....

I would have used ticks ( ` )'s but the server i'm using these scripts on doesn't like them.

Dr John · 07-22-2008, 07:46 PM

I'd suggest replacing group with groups or with grp, then the requirement to add ticks goes away.

Evulness · 07-23-2008, 01:23 PM

Querys been fixed.

now i just need to set up some functions to pull and display my articles.