this is too complicated

I am really irritated right now. I am try to create a table called users, with these fields - options:
- UserID - auto increment and is unique and the primary
- Email - indexed
- Password
- FirstName
- LastName
- Company
- Address1
- Address2
- City
- State
- ZipCode
- Country
- HomePhoneArea
- HomePhone
- WorkPhoneArea
- WorkPhone
- FaxNumberArea
- FaxNumber
- Verified

i used this query:

mysql> use database;
Database changed <-- Program added
mysql> create table users
-> (
-> UserID int(7) not null auto_increment,
-> Email text(100) not null,
-> Password text(25) not null,
-> FirstName text(25) not null,
-> LastName text(25) not null,
-> Company text(100),
-> Address1 text(100) not null,
-> Address2 text(100),
-> City text(50),
-> State text(2) not null,
-> ZipCode text(5) not null,
-> Country text(100) not null,
-> HomePhoneArea text(3) not null,
-> HomePhone text(7) not null,
-> WorkPhoneArea text(3) not null,
-> WorkPhone text(7) not null,
-> FaxNumberArea text(3) not null,
-> FaxNumber text(7) not null,
-> Verified int(1) default '0'
-> );


And i got this error:
ERROR 1064: You have an error in your SQL syntax near '(100) not null,
Password text(25) not null,
FirstName text(25) not null,
LastNam' at line 4
mysql>


PLEASE HELP ME!!!!!!!!!! I WILL NOT FORGET IT!!!!!!!!!!
AP

Hey Arthur,
Firstly, you need to define UserId as a primary key and unique identiifer. Secondly, you should use varchar (variable character) instead of text, coz text is too big.

Anyway, I made some changes and here's the working MySQL code:

create table users
(
UserID int auto_increment not null,
Email varchar(100) not null,
Password varchar(25) not null,
FirstName varchar(25) not null,
LastName varchar(25) not null,
Company varchar(100),
Address1 varchar(100) not null,
Address2 varchar(100),
City varchar(50),
State char(2) not null,
ZipCode char(5) not null,
Country varchar(100) not null,
HomePhoneArea varchar(3) not null,
HomePhone varchar(7) not null,
WorkPhoneArea varchar(3) not null,
WorkPhone varchar(7) not null,
FaxNumberArea varchar(3) not null,
FaxNumber varchar(7) not null,
Verified tinyint default 0,

primary key(UserID),
unique id(UserID)
);

ok ok, take 2 deep breaths, we feeling a bit better now?? ok, now to your problem

first point, you should only use lower_case, for your field names, ive had problems before with caps on

when you have text i dont think you can give a field lenght, change all the text fields to varchar,

try this

create table users (
user_id tinyint(7) not null auto_increment,
email varchar(100) not null,
password varchar(25) not null,
firstname varchar(25) not null,
lastname varchar(25) not null,
company varchar(100),
address1 varchar(100) not null,
address2 varchar(100),
city varchar(50),
state varchar(2) not null,
zipcode tinyint(5) not null,
country varchar(100) not null,
homephonearea tinyint(3) not null,
homephone varchar(7) not null,
workphonearea tinyint(3) not null,
workphone varchar(7) not null,
faxnumberarea tinyint(3) not null,
faxnumber varchar(7) not null,
verified int(1) default '0' );

just a few things, i made the phone numbers varchar's incase they add a - between the numbers

let me know if that works?? i cant test it cos im at work, but once i get home, if your still having problems with it reply with the error msg, and id be happy to help

copeland we gotta stop answering questions at the same time, lol oh yeah i did forget to mention the primary and unique keys,

arthurparker with all this information you should be able to get your table working.'

thanks ben and copeland.

i tried both and they both work.

Thank you so much.

AP

np, anytime, theirs nothing to big or small (i think) that we cant handle [evil laugh]ahahahahah[/evil laugh]