Trouble with MYSQL Syntax

Rollo_Tamasi

Hi guys, i'm following a tutorial on php/mysql and i'm attempting to input this sql code into a db in phpmyadmin

CREATE TABLE `databasename`.`cmsarticles` (
`ID` int(6) unsigned NOT NULL auto_increment COMMENT 'The unique ID of the article',
`title` varchar(200) NULL COMMENT 'The article title',
`tagline` varchar(255) NULL COMMENT 'Short summary of the article',
`section` int(4) NULL DEFAULT 0 COMMENT 'The section of the article',
`thearticle` text NULL COMMENT 'The article itself',
PRIMARY KEY (`ID`)
);

The writer of the tutorial said a simple copy and paste just see the MYSQL db setup, but i have attempted to alter the code and remove some of the " ' " from the code and i keep getting erorr's. The error i get when i attemp to run that code is

You have an error in your SQL syntax near 'COMMENT 'The unique ID of the article', title varchar(200) NULL COMMENT 'The a

Any help is much appriciated.
Rollo

dp777

The problem is on the ID column creation line. Before "COMMENT" you have to declare the ID column as a key. In MySQL to use auto_increment, that column must also be set as a key. Try replacing your ID creation with this line:
ID int(6) unsigned NOT NULL auto_increment PRIMARY KEY COMMENT 'The unique ID of the article',
I didn't test that, but it should do the trick, or at least give you a better error message.

AIS4U

Rollo Tamasi:

Have you already created your database?

If so then put the name of your database in place of "databasename" on the first line.

If you haven't created your database yet you will need to do this first.

Your code appears to be creating the table named "cmsarticles"

To start with, I think you need to delete all references to comments such as:
COMMENT 'The unique ID of the article'.

I believe these are just explanations that indicate what the particular field is and should not be part of your code.

Also, I do not use the backtick ( ` ), but it may work using that.

Try making these changes and see if it works.

If not then it would probably be a good idea to post all of your code so those trying to help you can see just what you are doing.

Good Luck

edwardsbc

The backticks are most likely your problem. The full CREATE TABLE syntax is documented here: http://dev.mysql.com/doc/refman/5.1/en/create-table.html. Except for the backticks, all the rest of your code looks fine (including nameing the ID column as primary key after the column definitions). MySQL does allow COMMENTS to be attached to most elements (Columns, tables, and databases).

Rollo_Tamasi

thanks for the speedy replies lads. I have followed your directions and modified the MYSQL to this...

  CREATE TABLE cmsarticles(
ID int( 6 ) unsigned NOT NULL auto_increment,
title varchar( 200 ) NULL ,
tagline varchar( 255 ) NULL ,
section int( 4 ) NULL DEFAULT 0,
thearticle text NULL ,
PRIMARY KEY ( ID ) ,
)

to which i get this error..

You have an error in your SQL syntax near ')' at line 1

for anyone that is interested the tutorial i'm following is a tutorial on creating your own CMS which can be located here . That is page 3 btw, where the first mention of any database code is mentioned.

edwardsbc

I believe you simply need a space between the end of your table name and the starting parenthesis.

thread_PHP

hi
u need to remove the comma after this line PRIMARY KEY ( ID ) ,

will work for u