[SOLVED] Create Table

[jp2php]

I've got to run a basic "CREATE TABLE" statement. I'm most familiar with SQL Server, but this statement needs to also run on MySQL and Microsoft Access.

 

The statement below should create the table, but I don't know how to tell it that the UserID needs to be my primary key. Also, I'm not sure if other table formats use the same variable names. Is "int" standard accepted SQL, or do some tables require "Number" or "Integer"? (The reason I ask is because I opened an Access table in Design View, it lists Integer fields as "Number" and Boolean fields as "Yes/No".)

 

Here's what I want to crank out:

 

CREATE TABLE Users (
  UserID int, -- AutoNumber
  UserName varchar(255),
  Hash1 varchar(255), -- SHA1 Encrypted Password
  Firstname varchar(255),
  Lastname varchar(255),
  Email varchar(255),
  Hint varchar(255), -- Challenge prior to resetting PWD
  Hash2 varchar(255), -- SHA1 Encrypted Hint response
  Active bit, -- Set after person verifies with link sent
  StartDate DateTime, -- Date account was created
  LastUse DateTime, -- Date account was last used
  Level int -- 1=Admin, 2=PowerUser, 3=Regular, 4=Guest
)

 

It is probably obvious from the format I'm using what this table is going to be for.

 

If anyone is thinking, "Hey brother, here's this example that shows everything you want all wrapped up in little package" .. I'm interested!

 

The thought of writing forms for them to create their accounts with, SHA1 their data, send email challenges, activate them on replies, keep track of their dates, etc. seems like a lot of debugging, to me.

[kickstart]

Hi

 

Had a quick play and this will give you the primary key:-

 

CREATE TABLE Users (
  UserID int, -- AutoNumber
  UserName varchar(255),
  Hash1 varchar(255), -- SHA1 Encrypted Password
  Firstname varchar(255),
  Lastname varchar(255),
  Email varchar(255),
  Hint varchar(255), -- Challenge prior to resetting PWD
  Hash2 varchar(255), -- SHA1 Encrypted Hint response
  Active bit, -- Set after person verifies with link sent
  StartDate DateTime, -- Date account was created
  LastUse DateTime, -- Date account was last used
  Level int -- 1=Admin, 2=PowerUser, 3=Regular, 4=Guest
  PRIMARY KEY  (`UserID`)
)

 

Works in both MySQL and Access

 

All the best

 

Keith

[jp2php]

Thanks Keith,

 

I don't have a personal MySQL db to test on. The company as a whole has something for testing, but I don't want to go in there and break it with a simple 'Create Table' query.

 

A quick follow-up before you are gone.

 

In this line:

  PRIMARY KEY  (`UserID`)

 

Do I need to use reverse single quotes (i.e. the key to the left of #1 on the keypad) or is that just a preference?

[kickstart]

Hi

 

For MySQL probably just easiest to download a packages WAMP setup (assuming Windows) such as this

 

The back ticks are a default in phpMyAdmin, and useful if you want a column name which is otherwise a reserved word. Personally I prefer not to use them, and just be careful with column names.

 

All the best

 

Keith

[jp2php]

Hey, thanks for the link! I've been looking for something I could use as an interface like that.

 

So basically, "the back ticks" would be like including the names in "square brackets", which is something I have to do with SQL Server:

SELECT [Name], [Group], [Count]
FROM EmployeeTable

 

Are square brackets OK in MySQL as well as back ticks? It may seem like a stupid question, but I want to make sure I don't fowl up the database.

[kickstart]

Hi

 

Honestly do not know for sure. It doesn't appear to accept square brackets, but not sure if their might be a configuration option somewhere.

 

All the best

 

Keith

[fenway]

No, square brackets are not ok.

[jp2php]

No, square brackets are not ok.

Thanks for the help, fenway.