Whats wrong with my syntax?

Discussion in 'MySQL' started by rushy, Sep 4, 2006.

0
  1. #1
    Hi Guys,

    First up im not even what you'd call familiar with mysql. Im only looking into it now because one of my sites' backup database isnt restoring properly (guessing due to a download error) and I'm being forced to manually go through everything to figure out whats up. Anyway i have the following query:

    DROP TABLE IF EXISTS contact;
    CREATE TABLE contact (
    id int(5) NOT NULL auto_increment,
    from varchar(150) NOT NULL default '',
    date int(10) NOT NULL default '0',
    name varchar(150) NOT NULL default '',
    subject varchar(250) NOT NULL default '',
    message text NOT NULL,
    read int(1) NOT NULL default '0',
    PRIMARY KEY (id)
    ) TYPE=MyISAM;

    When I try and run it i get this error:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from varchar(150) NOT NULL default '',
    date int(10) NOT NULL default '0',
    ' at line 3

    Can anyone tell me what the correct syntax would be? I have many errors similar to this one throughout the dbase :(

    Thanks,
     
    rushy, Sep 4, 2006 IP
  2. rosiee007

    rosiee007 Notable Member

    Messages:
    3,352
    Likes Received:
    179
    Best Answers:
    0
    Trophy Points:
    230
    #2
    'from' is a command in mysql .. just when you do .. SELECT * FROM table_name

    so try changing the variable name here.
     
    rosiee007, Sep 4, 2006 IP
  3. swirl1980

    swirl1980 Peon

    Messages:
    53
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #3
    i'd defo look at changing the from field to something else as a start. Also, not really familiar with MySQL but u have ur field set as a varchar(150) NOT NULL with a default value which is essentially a NULL value - (''), is this allowed in MySQL??
     
    swirl1980, Sep 5, 2006 IP
  4. bochgoch

    bochgoch Peon

    Messages:
    1,918
    Likes Received:
    67
    Best Answers:
    0
    Trophy Points:
    0
    #4
    You actually have three field names that are reserved words (words that have a meaning within the syntax of the RDBMS) -- from, date and read. If you prefix all your fieldnames with 'contact_' this makes the sql a little neater and gives you valid fieldnames (you don't have to prefix all fields but it does no harm).

    So, this should work...

    DROP TABLE IF EXISTS contact;
    CREATE TABLE contact (
    contact_id int(5) NOT NULL auto_increment,
    contact_from varchar(150) NOT NULL default '',
    contact_date int(10) NOT NULL default '0',
    contact_name varchar(150) NOT NULL default '',
    contact_subject varchar(250) NOT NULL default '',
    contact_message text NOT NULL,
    contact_read int(1) NOT NULL default '0',
    PRIMARY KEY (contact_id)
    ) TYPE=MyISAM;

    bg
     
    bochgoch, Sep 7, 2006 IP