Question about MySQL column names...

[Defender1]

I have a table that has numerica column names.

Now, i created the table in phpMyAdmin and it worked fine.

But when I used the script i made to restore my db structure, it won't create the table saying

desktops_sizes table creation failed!
You have an error in your SQL syntax near '640 tinyint(1) NOT NULL default '0', 640_img varchar(150) default 'http://ww' at line 4

Here's the sql:

$sql_desktops_sizes = "CREATE TABLE desktops_sizes (
  size_id tinyint(3) NOT NULL auto_increment,
  desktop_id tinyint(2) NOT NULL default '0',
  640 tinyint(1) NOT NULL default '0',
  640_img varchar(150) default 'http://www.webinfractions.com/images/desktops/',
  800 tinyint(1) NOT NULL default '0',
  800_img varchar(150) default 'http://www.webinfractions.com/images/desktops/',
  1024 tinyint(1) NOT NULL default '0',
  1024_img varchar(150) default 'http://www.webinfractions.com/images/desktops/',
  1280 tinyint(1) NOT NULL default '0',
  1280_img varchar(150) default 'http://www.webinfractions.com/images/desktops/',
  PRIMARY KEY  (size_id),
  UNIQUE KEY desktop_id (desktop_id)
) TYPE=MyISAM;";

Any idea why i'm getting this error?

[shane]

It's probably the name of the '680' field. Either change the field name to begin with a non-numeric character or enclose the field name in square brackets.

Personally, I'd change the name but it's up to you.

[Defender1]

		$sql_desktops_sizes = "CREATE TABLE desktops_sizes (
		  size_id tinyint(3) NOT NULL auto_increment,
		  desktop_id tinyint(2) NOT NULL default '0',
		  [640] tinyint(1) NOT NULL default '0',
		  [640_img] varchar(150) default 'http://www.webinfractions.com/images/desktops/',
		  [800] tinyint(1) NOT NULL default '0',
		  [800_img] varchar(150) default 'http://www.webinfractions.com/images/desktops/',
		  [1024] tinyint(1) NOT NULL default '0',
		  [1024_img] varchar(150) default 'http://www.webinfractions.com/images/desktops/',
		  [1280] tinyint(1) NOT NULL default '0',
		  [1280_img] varchar(150) default 'http://www.webinfractions.com/images/desktops/',
		  PRIMARY KEY  (size_id),
		  UNIQUE KEY desktop_id (desktop_id)
		) TYPE=MyISAM;";

Square brackets don't work.

desktops_sizes table creation failed!
You have an error in your SQL syntax near '[640] tinyint(1) NOT NULL default '0', [640_img] varchar(150) default 'http:' at line 4

Though I assume mysql indeed doesn't like numeric column names right?

[DR_LaRRY_PEpPeR]

eww, numeric column names! you need to use backticks around the column names -- `640` -- which is what POS phpMyAdmin does. but you should really rename the columns.

[shane]

Apologises for putting you on the wrong path Defender but I assumed things would probably be the same as for MS SQL Server (I'm assuming you're using mySql).

[MattR]

Please do not write
desktop_id tinyint(3) NOT NULL default '0'

It should be:

desktop_id TINYINT NOT NULL DEFAULT 0

[DR_LaRRY_PEpPeR]

Originally posted by MattR
Please do not write
desktop_id tinyint(3) NOT NULL default '0'

It should be:

desktop_id TINYINT NOT NULL DEFAULT 0

i'm 99.99% certain that that was taken from phpMyAdmin, so it's not really his fault. well, actually there'd be the same thing if it was from mysqldump. the problem is with MySQL's SHOW CREATE TABLE command. it quotes the numbers like that.

[Defender1]

Yea, i never put quotes around numbers

And yes, I renamed the columns.