SQL Query gone wrong

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Starcraftmazter
Forum Commoner
Posts: 45
Joined: Mon Apr 24, 2006 11:36 pm

SQL Query gone wrong

Post by Starcraftmazter »

There isn't a thing I've tried to do here, so please tellme whats wrong.

Code: Select all

INSERT INTO sss_users ('active', 'name', 'gender', 'username', 'password', 'email', 'regstr', 'regtime', 'tickets', 'ip') VALUES ('0', 'Max', 'm', 'Starcraftmazter', '2e81f355049b5861d4d17cf470cb2b8a1538b418', 'starcraftmazter@gmail.com', 'I8L6I0daJT21fQly5M50e0j2uAog22wWDlC83Iz85S27PgReD7az9AZ877J5J68WM5hM80322dV7MKlXFPyj2nCy29Q30n3i30z6', '1152023040', '0', '2130706433 ');
This is the table

Code: Select all

$sql = 'CREATE TABLE `sss_users` ('
        . ' `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, '
        . ' `active` ENUM(\'0\',\'1\') NOT NULL, '
        . ' `name` VARCHAR(100) NOT NULL, '
        . ' `gender` ENUM(\'m\',\'f\') NOT NULL, '
        . ' `username` VARCHAR(32) NOT NULL, '
        . ' `password` VARCHAR(40) NOT NULL, '
        . ' `email` VARCHAR(255) NOT NULL, '
        . ' `regstr` VARCHAR(100) NOT NULL, '
        . ' `regtime` INT UNSIGNED NOT NULL, '
        . ' `tickets` SMALLINT UNSIGNED NOT NULL, '
        . ' `ip` INT UNSIGNED NOT NULL, '
        . ' `type` TINYINT UNSIGNED NOT NULL, '
        . ' `staff` ENUM(\'0\',\'1\') NOT NULL DEFAULT \'0\''
        . ' )'
        . ' TYPE = innodb;';
It's my first time using the enum field, so I sence that's where the trouble is, but I don't know of any special stuff which applies to it, when inserting.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: SQL Query gone wrong

Post by timvw »

[quote="Starcraftmazter"]There isn't a thing I've tried to do here, so please tellme whats wrong.

Code: Select all

INSERT INTO sss_users ('active', 'name', 'gender', 'username', 'password', 'email', 'regstr', 'regtime', 'tickets', 'ip') VALUES ('0', 'Max', 'm', 'Starcraftmazter', '2e81f355049b5861d4d17cf470cb2b8a1538b418', 'starcraftmazter@gmail.com', 'I8L6I0daJT21fQly5M50e0j2uAog22wWDlC83Iz85S27PgReD7az9AZ877J5J68WM5hM80322dV7MKlXFPyj2nCy29Q30n3i30z6', '1152023040', '0', '2130706433 ');
You should use `` to escape column names instead of '' (but i don't see reserved keywords in them, so you could simply do without them too)

Code: Select all

INSERT INTO sss_users (`column name`) VALUES ('john doe');
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It would help to know what your database is saying is wrong and why you haven't tried anything.
Starcraftmazter
Forum Commoner
Posts: 45
Joined: Mon Apr 24, 2006 11:36 pm

Post by Starcraftmazter »

Oh right sorry - the most random of thing,
MySQL Reported: 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 ''active', 'name', 'gender', 'username', 'password', 'email', 'regstr', 'regtime'' at line 2
Whatever fields I take out / put in, it always goes from the start with it's "near" clause of error message. If you get that.

Edit: Thanks timvw! using `` worked, although I cannot understand why, I've used '' all the time before.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

standard quotes, both single and double, are strings in SQL. Backticks are database, table and field references.
Starcraftmazter
Forum Commoner
Posts: 45
Joined: Mon Apr 24, 2006 11:36 pm

Post by Starcraftmazter »

I'll always use those from now on :wink:
Post Reply