SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question about MySQL column names...

    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:
    Code:
    $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?
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  2. #2
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    		$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?
    Last edited by Defender1; Aug 9, 2002 at 10:05.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  5. #5
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please do not write
    desktop_id tinyint(3) NOT NULL default '0'

    It should be:

    desktop_id TINYINT NOT NULL DEFAULT 0


  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  8. #8
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yea, i never put quotes around numbers

    And yes, I renamed the columns.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •