New to mySQL

Need help creating a table

[dougmcc1]

This is more of a question for my web hosting company but they won't do mySQL troubleshooting so I figured I would give WW a shot. I've done a lot of researching and I havent come up with much but I'll keep looking as I wait for replies. Here is my problem:

My web hosting company offers a service in the control panel that automatically creates a table when I fill in the information but it keeps giving me the same error.

There are 3 fields:
Field, Length/Values, and Default

There are 4 drop down menus:
Type, Attributes, Null, and Extra

And there are 5 radio buttons:
Primary, Index, Unique, ---, and Fulltext

I can only choose 1 out of the 5 radio buttons and --- is selected by default. As for the drop down menus, for Type there is 25 variable types to choose from. For Attributes I can leave it blank or choose BINARY, UNSIGNED, or UNSIGNED ZEROFILL. For Null, I can either choose NULL or NOT NULL and for Extra I can either leave it blank or choose auto_increment.

Based on that, can someone give me directions on how to create a valid table by telling me what to type in the fields, what to select from the drop down menus and which radio button to select?

Here is a table I created.

CREATE TABLE `Business` ( 
`Name` TEXT( 55 ) NOT NULL 
) COMMENT = 'Why won't it work?'

And here is the error message:

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 '(55) NOT NULL) COMMENT = 'Why won't it work?'' at line 1

My web hosting company uses mySQL 4. Thanks for any help. I'll keep reading through the mySQL 4 manual in the meantime.

[wruk999]

Hi dougmcc1,

The problem with the SQL that your are currently trying to execute is that you are specifying 50 for the length of the text. If you change that field to a VARCHAR field, everything works fine. A VARCHAR field can contain upto 255 characters, so 50 is not a problem.

When I excecuted the folowing, everything worked fine.

CREATE TABLE `Business` (

`Name` VARCHAR( 50 ) NOT NULL
) COMMENT = 'Why wont it work?';

Note that I have changed the field type to VARCHAR in the statement! ;)

Field, Length/Values, and Default

Field: The field name. in your example you used: 'Name'.
Length/Values: This is the amount of characters your field can contain. For example, VARCHAR fields can accept upto 255 chars.
Default: This is set if you want a default value. ie: If when you enter a record you want a field to always want to be 1, then you can set it in this field.

Type, Attributes, Null, and Extra

Type: Your field type. Check out this page for more details on all the types: MySQL - Field Types [mysql.com]
Null: If you want a value to default to null, this is different to entering an empty value. On large databases, you will probably want NULL values as they use less space.
Extra: If you select auto_increment, it will mean that when you enter a number into the field, it will be the last one plus 1. This means you can use this type for id keys etc.

Primary, Index, Unique, ---, and Fulltext

these are used for indexing the particular field in the table.

For more information, you may want to check out: [mysql.com...]

If you try to create a table, and have problems, paste here your sql and someone can take a look to see why it doesn't work.

Regards,
wruk999

[dougmcc1]

Thanks, that worked.

I will also check out those pages you suggested.

[jamesa]

The MySQL Tutorial [mysql.com] might be a great place to start as well.