MSSQL syntax vs. MYSQL Syntax

Mattingly23

Hi,

Is the syntax for MSSQL and MYSQL queries different?

I tried using this query in MSSQL and it did not work. Can someone tell me what's wrong? Thanks.

	$sql = 'CREATE TABLE `quiz` ( `id` INT NOT NULL AUTO_INCREMENT ,'
        . ' `question` TEXT NOT NULL ,'
        . ' `answera` TEXT NOT NULL ,'
        . ' `answerb` TEXT NOT NULL ,'
        . ' `answerc` TEXT NOT NULL ,'
        . ' `answerd` TEXT NOT NULL ,'
        . ' `rightanswer` CHAR( 1 ) NOT NULL ,'
        . ' `explanation` TEXT NOT NULL ,'
        . ' UNIQUE ( `id` ) );'
        . ' ';

  $thetest = mssql_query($sql);

The_Chancer

This is the query I use to insert on MSSQL..

$res=mssql_query("CREATE TABLE [dbo].[form_a] (
		[ref] [int] IDENTITY (1, 1) NOT NULL ,
		[salesperson] [varchar] (50) NULL ,
		[weekending] [varchar] (50) NULL ,
		[prov_new] [varchar] (50) NULL ,
		[prov_exist] [varchar] (50) NULL ,
		[confirmed_new] [varchar] (50) NULL ,
		[confirmed_exist] [varchar] (50) NULL ,
		[conversion] [varchar] (50) NULL ,
		[newcalls] [int] NULL ,
		[existcalls] [int] NULL ,
		[thisactivity] [text] NULL ,
		[nextactivity] [text] NULL 
	) ON [PRIMARY]");

HTH

Mattingly23

Thanks for the reply, I'll try doing something like that. Is [dbo].[form_a] in [database].[table] format?

Does [ref] make the value unique? How would I make it auto increment?

Is there anywhere I can find a tutorial on MS SQL Queries in PHP? I can hardly find anything, it seems like MySQL is so much more popular.

EDIT:
After searching around some more I'm guessing the identity statement is like auto-increment?

The_Chancer

I had previously selected the database I was in then [dbo].[tablename] is the format used from then on.

Using [fieldname] gets round using reserved words as far as I can tell, e.g. using user as a column doesn't work - yet [user] does...

As to making it auto increment... the IDENTITY (1,1) does that for you as far as I know - try it and see. Export the SQL from the Enterprise manager and see what you get.

Mattingly23

Alright thanks for the tips. I just need a couple things explained.

What does [ref] do?

What does ON [PRIMARY] do?

Also, would queries like this work in php?

$result = mssql_query("SELECT * FROM quiz");

It's just that I've only used MYSQL but now I have to use MSSQL for this project, so I'm a little lost.
Thanks again, I really appreicate your help.

The_Chancer

[ref] was the fieldname for one of my columns thats all.

on [PRIMARY] I am unsure.. all I know is it works (MySQL is my normal DB too...)

The general queries will work, SELECT * FROM... UPDATE etc as per MySQL, there are some idiosyncrasies as in you can't use tablename you can't include the ` char...

Mattingly23

Do you know how I would set a default value for a field?

Thanks.