Trouble creating table

[bazz]

Hi,

OK so I am trying to create a new table for a new Db.

This is what I have 'told' phpMyAdmin to do

CREATE TABLE `tbl_Login` (
`loginID` INT( 11 ) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL AUTO_INCREMENT ,
`UserName` TEXT CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL ,
`StoredPassword` TEXT CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL ,
`ContactID` TINYINT CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL ,
`Status` TINYINT CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL ,
`Privileges` TINYINT NOT NULL
) TYPE = innodb CHARACTER SET latin1 COLLATE latin1_general_cs


and this is the error, which I don't understand and ask for you help please.



MySQL said: Documentation
#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 'CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL AUTO_INCREMENT, `UserNam' at line 1


what I have seen is that I named the field 'userName' but it seems to have dropped the last 'e' :(

I am new to this so please go easy-ish ;)

bazz

[thesavior]

Im not sure if this would work, but try it this is what my phpmyadmin is telling me:

**EDIT**
This works, and is the correct format:

CREATE TABLE `tbl_Login` (
`loginID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`UserName` TEXT NOT NULL ,
`StoredPassword` TEXT NOT NULL ,
`ContactID` TINYINT NOT NULL ,
`Status` TINYINT NOT NULL ,
`Privileges` TINYINT NOT NULL
) ENGINE = INNODB CHARACTER SET latin1 COLLATE latin1_general_cs

You dont need to repeat character set and collate throughout, only at the end. Also 'type' is deprecated, we use engine now. You also didnt define a primary key so I made loginID your primary.

[Daemonspyre]

In your SQL syntax error, reported by MySQL -- there is a character limit on the error reporting. It did not include the 'e' only because it was truncated, not because it wasn't included in your statement.

Look at the beginning of the error report, not the end. :)

Try this:


CREATE TABLE `tbl_Login` (
`loginID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`UserName` TEXT NOT NULL,
`StoredPassword` TEXT NOT NULL,
`ContactID` TINYINT(3) UNSIGNED NOT NULL,
`Status` TINYINT(3) UNSIGNED NOT NULL,
`Privileges` TINYINT(3) UNSIGNED NOT NULL
) ENGINE = innodb CHARACTER SET latin1


Now, couple of points:

1) Why are you allowing your UserName to be 255 characters long? You might want to limit that to no more than 16 or so. Use VARCHAR(16) instead of TEXT.

2) StoredPassword: Same as above, but you may want to consider storing this field encrypted, by using PASSWORD(), MD5(), or AES_ENCRYPT()/AES_DECRYPT(). Each field has their own size limitations.

See: http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html

3) You will notice a few changes on your fields with numbers. Unless you want to allow negative numbers, always add UNSIGNED to your numerical field types.

4) INT(11). Guelph has a good article on his site about that... You are NOT restricting your INT field to 11 characters! You are only telling the field how many Zeros to put in when using ZEROFILL. Just an FYI.

HTH!

[bazz]

Thank you both. That is proving to be very helpful.

@Daemonspyre

re. point 4... does this mean that a number such as '1' would be entered as '00000000001'? I'm off to look for Guelph's site to read his article(s).

Thanks again,
bazz

[Daemonspyre]

Here's the page on Guelph's site I was talking about:

http://www.guelphdad.wefixtech.co.uk/sqlhelp/numericdata.shtml


It does mean that IF you were to use ZEROFILL.

Since you aren't using that particular attribute on your field, your INT column will return '1', '2', '3'..etc

ZEROFILL does exactly what it says -- "On my integer, please prepend xx number of zeros to match this exact field length".

[bazz]

Tank you, I have just read through most of his articles to get the generall idea. I'lll read them in greater depth as I build.

I am curious though as to when I might use zerofilll and when I might not.

Could I ask for an example of each please?

bazz

[guelphdad]

use zerofill when you need to show your numbers with a certain number of values. for instance if you were using a part number and they should always display with at least 6 digits and you use zerofill int(6) then they would show a minimum of 6 digits. So you would enter the number 7 on its own but it would display as 000007. there aren't many times you need to use zerofill though.