Syntax error in simple statement

[johno77]

Hi

i have no real knowledge of databases but am trying to make a simple table but keep getting a syntax error with the following code:

mysql> create table enterprise_programming(Submitter VARCHAR(20) , URL
VARCHAR(20) , Date submitted VARCHAR(12) , Version INT , Task-ref-no IN
T , Page number INT , Status VARCHAR(10) , Approval/Rejection Date VARC
HAR(12) , Chief-Developer Ref INT);

the not so helpfull error message i get is:

You have an error in your SQL syntax near 'submitted VARCHAR(12) , Version INT , Task-ref-no INT , Page number INT , Status' at line 1

If anyone has any ideas they would be appreciated.

many thanks

 

[r937]

column names shouldn't have special characters in them such as spaces (from a "best practices" point of view) but if you really need them for some reason, then you must always escape the entire column name in backticks

[tt]create table enterprise_programming
( Submitter VARCHAR(20)
, URL VARCHAR(20)
, `Date submitted` VARCHAR(12)
, Version INT
, `Task-ref-no` INT
, `Page number` INT
, Status VARCHAR(10)
, `Approval/Rejection Date` VARCHAR(12)
, `Chief-Developer Ref` INT
)[/tt]

much better, however, are column names that don't require escaping, because it is a real pain having to remember to code the backticks in your queries all the time

also, for your dates, use datetime datatype, you will thank me later

[tt]create table enterprise_programming
( Submitter VARCHAR(20)
, URL VARCHAR(20)
, DateSubmitted datetime
, Version INT
, TaskRefNo INT
, PageNumber INT
, Status VARCHAR(10)
, ApprovalRejectionDate datetime
, ChiefDeveloperRef INT
)[/tt]

rudy

http://r937.com/

 

[cold1too]

You have spaces in your field names. It's not advisable to create field names with spaces. I believe you can still do it by using single quotes around the field names containing spaces, but it's not considered good practice. You'll want to stay away from using special characters as well.

 

[cold1too]

The commas should follow the column names when using the

CREATE

statement, not precede them. Also, you might want to think about lengthening the URL column's data type as shown below.

create table enterprise_programming
   (
     Submitter VARCHAR(20),
     URL VARCHAR(255), 
     DateSubmitted datetime,
     Version INT, 
     TaskRefNo INT, 
     PageNumber INT, 
     Status VARCHAR(10), 
     ApprovalRejectionDate datetime,
     ChiefDeveloperRef INT
   )

 

[r937]

> "The commas should follow the column names
> when using the CREATE statement, not precede them."

oh, they do

look again

better yet, run my create statement and see if it works

;-)


happy holidays