mysql doesn't like me (i don't understand why i get this error)

Can you have that many primary keys? I've only done that in lookup tables.

I would set the auto-increment to PRIMARY and the username, email to UNIQUE instead.

I'm not sure what the error is, I'm a hapless PhpMyAdmin user.

i'm not sure.

there's instances there's searches by email, the username is used there to find the user info, and then the uid is used to call the user in other tables.

that's how it's done inthe profile to mask the uid, and in the case of control panels and other restricted pages to check that your username and password match, then hte page is displayed, if you access other tables i then use the uid.

welll.. it's not the number of primary keys.
it was suto_incrememnt... but i dunno what it is now....

Quote: Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.56

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES
-> ;
+----------------+
| Database |
+----------------+
| findyourdesire |
| mysql |
+----------------+
2 rows in set (0.15 sec)

mysql> USE findyourdesire
Database changed
mysql> CREATE TABLE users(
-> uid int auto_increment unsigned NOT NULL PRIMARY KEY,
-> username varchar(15) NOT NULL PRIMARY KEY,
-> password varchar(15) NOT NULL,
-> email tinytext NOT NULL PRIMARY KEY,
-> gmt_offset tinyint default '-5' NOT NULL,
-> site_access tinyint unsigned default '0' NOT NULL,
-> admin_comment tinytext,
-> approved tinyint unsigned default '12' NOT NULL,
-> last_login_ip tinytext NOT NULL,
-> last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
-> end_level date default '0000-00-00' NOT NULL,
-> login_duration tinyint default '0' NOT NULL,
-> join datetime default '0000-00-00 00:00:00' NOT NULL,
-> gender char(1) NOT NULL,
-> last_activity datetime default '0000-00-00 00:00:00' NOT NULL
-> ) TYPE=MyISAM;
ERROR 1064: You have an error in your SQL syntax near 'unsigned NOT NULL PRIMARY KEY,
username varchar(15) NOT NULL PRIMARY KEY,
passwo' at line 2
mysql> SHOW TABLES
-> ;
Empty set (0.18 sec)

mysql> CREATE TABLE users(
-> uid int auto_increment unsigned NOT NULL PRIMARY KEY,
-> username varchar(15) NOT NULL PRIMARY KEY,
-> password varchar(15) NOT NULL,
-> email tinytext NOT NULL PRIMARY KEY,
-> gmt_offset tinyint default '-5' NOT NULL,
-> site_access tinyint unsigned default '0' NOT NULL,
-> admin_comment tinytext,
-> approved tinyint unsigned default '12' NOT NULL,
-> last_login_ip tinytext NOT NULL,
-> last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
-> end_level date default '0000-00-00' NOT NULL,
-> login_duration tinyint default '0' NOT NULL,
-> join datetime default '0000-00-00 00:00:00' NOT NULL,
-> gender char(1) NOT NULL,
-> last_activity datetime default '0000-00-00 00:00:00' NOT NULL) TYPE=MyISAM;
ERROR 1064: You have an error in your SQL syntax near 'unsigned NOT NULL PRIMARY KEY,
username varchar(15) NOT NULL PRIMARY KEY,
passwo' at line 2
mysql> CREATE TABLE users(
->
-> uid int unsigned auto_increment NOT NULL PRIMARY KEY,
-> username varchar(15) NOT NULL PRIMARY KEY,
-> password varchar(15) NOT NULL,
-> email tinytext NOT NULL PRIMARY KEY,
-> gmt_offset tinyint default '-5' NOT NULL,
-> site_access tinyint unsigned default '0' NOT NULL,
-> admin_comment tinytext,
-> approved tinyint unsigned default '12' NOT NULL,
-> last_login_ip tinytext NOT NULL,
-> last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
-> end_level date default '0000-00-00' NOT NULL,
-> login_duration tinyint default '0' NOT NULL,
-> join datetime default '0000-00-00 00:00:00' NOT NULL,
-> gender char(1) NOT NULL,
-> last_activity datetime default '0000-00-00 00:00:00' NOT NULL) TYPE=MyISAM;
ERROR 1064: You have an error in your SQL syntax near 'join datetime default '0000-00-00 00:00:00' NOT NULL,
gender char(1) NOT NULL,
l' at line 14
mysql> CREATE TABLE users(
-> uid int unsigned auto_increment NOT NULL PRIMARY KEY,
-> username varchar(15) NOT NULL PRIMARY KEY,
-> password varchar(15) NOT NULL,
-> email tinytext NOT NULL PRIMARY KEY,
-> gmt_offset tinyint default '-5' NOT NULL,
-> site_access tinyint unsigned default '0' NOT NULL,
-> admin_comment tinytext,
-> approved tinyint unsigned default '12' NOT NULL,
-> last_login_ip tinytext NOT NULL,
-> last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
-> end_level date default '0000-00-00' NOT NULL,
-> login_duration tinyint default '0' NOT NULL,
-> join datetime default '0000-00-00 00:00:00' NOT NULL,
-> gender char(1) default 'F' NOT NULL,
-> last_activity datetime default '0000-00-00 00:00:00' NOT NULL
-> ) TYPE=MyISAM;
ERROR 1064: You have an error in your SQL syntax near 'join datetime default '0000-00-00 00:00:00' NOT NULL,
gender char(1) default 'F'' at line 14
mysql>

You broke up the data type declaration in your CREATE TABLE syntax. AUTO_INCREMENT cannot come between INT and UNSIGNED.

uid int unsigned NOT NULL auto_increment PRIMARY KEY,

why did it move onto

Quote: ERROR 1064: You have an error in your SQL syntax near 'join datetime default '0000-00-00 00:00:00' NOT NULL,
gender char(1) default 'F'' at line 14

when i did my change if it's still the positoin of auto_increment?

You can't name a field join as you did since that is a SQL statement.

lol. didn't even think of that. it's when the user joined, so it made sense logically to call it that. hmmm...

how's enroll. it's the best synonym i see in bookshelf's theasaurus

You can name a field whatever you like. But if the name you want is also a reserved word (as join is), then you must enclose the word in quotes (`join`).

suzanne: i just tried again... i got an error.... "multiple primary key defined"

doh! I guess that showed up after fixing the other errors?

yep... and created a situation where i have these questions....

i need a set of things to be unique in two tables.. i've noticed i can call things either "unique" or "unique key"

i have questions....

1: what's the difference between those?
2: why have i seen examples where you end a line with "primary key" but none like that with "unique" or "unique key", and the only ones with unique are indexes?

3: why does "unique" have to have a name and a feild? is there a way to get it like making a primary key like when you define it inline (and why do i get the feeling that is what the difference between "unique" and "unique key" is)?

Table with a primary key containing 3 combined fields (highly recommended against).

'
Table with a primary key, and 2 seperate unique keys (recommended).
'Do you know how to use the MySQL Documentation? These are simple syntax errors that are easily identified while studying the examples in the CREATE TABLE documentation.

current tables that will offend (reccommendations on which feilds to turn into unique ...

'note: most searches done via username or uid. however, for password retrieval, lookup is done via email

and the others, actually 3, and wont be used or made for a little bit...

'

the online documentation and the book i have are what i was looking through. although i can't seem to find as much online. i just keep finding the TOC unable to direct me where to go to find what i'm looking for

'Fields do not need to be a primary key to be searchable. They only need to be indexed, and any of the three index types (primary, unique, index) will do.

have you ever declared keys in theline? all the examples i've found in books are like that. i don't get why this doesn't work because of that.

Quote: mysql> CREATE TABLE users(
-> uid int unsigned NOT NULL auto_increment PRIMARY KEY,
-> username varchar(15) NOT NULL UNIQUE KEY,
-> password varchar(15) NOT NULL,
-> email tinytext NOT NULL UNIQUE KEY,
-> gmt_offset tinyint default '-5' NOT NULL,
-> site_access tinyint unsigned default '0' NOT NULL,
-> admin_comment tinytext,
-> approved tinyint unsigned default '12' NOT NULL,
-> last_login_ip tinytext NOT NULL,
-> last_login_date datetime default '0000-00-00 00:00:00' NOT NULL,
-> end_level date default '0000-00-00' NOT NULL,
-> login_duration tinyint default '0' NOT NULL,
-> enroll datetime default '0000-00-00 00:00:00' NOT NULL KEY,
-> gender char(1) default 'F' NOT NULL KEY,
-> last_activity datetime default '0000-00-00 00:00:00' NOT NULL
-> ) TYPE=MyISAM;
ERROR 1064: You have an error in your SQL syntax near 'KEY,
gender char(1) default 'F' NOT NULL KEY,
last_activity datetime default '00' at line 14
mysql>

Do you do any research on your own? Obviously your book(s) aren't providing enough help for you to solve your own problems. But I've already linked you to the CREATE TABLE section of the MySQL Documentation in this post.

The documentation gives examples of only setting the primary key inline. The docs do not give examples of setting any other keys inline. By testing this myself, I discoved that setting unique keys inline does work. By testing this myself, I discovered that setting index keys inline does not work.

It took me 2 minutes of my time to connect to my mysql server and learn this. It took me 5 minutes to write up this post.

i have tried the online manual and find the toc that's online makes it hard to find things. the orielly book i have has an examples with unique keys inline, which is why i didn't understand why it's not working and sought the advice of those more experienced.

Try using the search instead. It gives faster and better results than the TOC at mysql.com -- also, there is no better teacher than experience.

Have you looked into using PHPMyAdmin? It's a great way to learn about table creation as it shows you the queries that produce the tables when it does so, and you can, additionally, export the table and see how it's put together.

nope. i heard about it and looked at the site. it sounds like it'd be obscuring it and i want to learn it. like i said, i had found an example that was inline and that wasn't working. since i realize the books aren't always right wanted to know if the erorr was me or the book. that's why i wasked about it here.

i also know that i'm using a 20 gig drive on hardware that doesn't support drives greater than 8 gigs and seems to work fine as long as partitions are under 8 gigs, so i knew it could have been a quirk with my system, so i wanted to know it wasn't just me.

PHPMyAdmin allows you to enter the queries raw. It doesn't obscure anything and helps illustrate things nicely.