SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with database table

    Hi I am getting a mysql syntax error when I try to insert into a mysql table.
    I think the problem I have is that my table structure is not set up properly and have had litle experience in what i should use and when to use null etc.
    When I list the field names in my sql insert query i get syntax error on line 1 but if I just list the values it updates.
    The table i am inserting into is as follows:

    CREATE TABLE `walks` (
    `walk_id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(256) NOT NULL,
    `heading` varchar(256) NOT NULL,
    `subheading` varchar(256) NOT NULL,
    `description` text NOT NULL,
    `region` varchar(256) NOT NULL,
    `walk-option1` text NOT NULL,
    `walk-price1` text NOT NULL,
    `walk-supplement1` text NOT NULL,
    `walk-option2` text NOT NULL,
    `walk-price2` text NOT NULL,
    `walk-supplement2` text NOT NULL,
    `walk-option3` text NOT NULL,
    `walk-price3` text NOT NULL,
    `walk-supplement3` text NOT NULL,
    `rest1` text CHARACTER SET latin2 NOT NULL,
    `rest1price` text NOT NULL,
    `rest2` text NOT NULL,
    `rest2price` text NOT NULL,
    `img1` varchar(256) NOT NULL,
    `img2` varchar(256) NOT NULL,
    `img3` varchar(256) NOT NULL,
    `map` varchar(256) NOT NULL,
    PRIMARY KEY (`walk_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

    the error I get is :

    INSERT INTO walks (NULL, title, heading, subheading, description, region, walk-option1, walk-price1, walk-supplement1, walk-option2, walk-price2, walk-supplement2, walk-option3, walk-price3, walk-supplement3, rest1, restprice1, rest2, restprice2, img1, img2, img3, map) VALUES ('n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', '', '', '', '', '', '', '', '', '', 'No Image', '', '', '' )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 'NULL, title, heading, subheading, description, region, walk-option1, walk-price1' at line 1

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    In your INSERT, you can't use NULL instead of a column name. Since the first column is an autoincrement field, try eliminating the NULL and see what happens.

  3. #3
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I get the same pretty much

    INSERT INTO walks (title, heading, subheading, description, region, walk-option1, walk-price1, walk-supplement1, walk-option2, walk-price2, walk-supplement2, walk-option3, walk-price3, walk-supplement3, rest1, restprice1, rest2, restprice2, img1, img2, img3, map) VALUES ('n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', '', '', '', '', '', '', '', '', '', 'No Image', '', '', '' )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 'title, heading, subheading, description, region, walk-option1, walk-price1' at line 1

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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 '-option1, walk-price1, walk-supplement1, walk-option2, walk-pric

    You can't use hyphens (-) in your column names, or you must use backticks around them, I think.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    And then I get

    #1054 - Unknown column 'restprice1' in 'field list'


  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    This works:
    Code MySQL:
    INSERT INTO walks ( title, heading, subheading, description, region, `walk-option1` , `walk-price1` , `walk-supplement1` , `walk-option2` , `walk-price2` , `walk-supplement2` , `walk-option3` , `walk-price3` , `walk-supplement3` , rest1, rest1price, rest2, rest2price, img1, img2, img3, map )
    VALUES (
    'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', '', '', '', '', '', '', '', '', '', 'No Image', '', '', ''
    )

  7. #7
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much for your help that's great thanks. So do you think it would be better to replace all - to _? would that sort the problem out aswell as the 2 fields be named incorrectly?

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Using _ instead of - at least saves you from having to use backticks. You can also use non of them, and write everything as one long word, though it might become a bit difficult to read.
    I always avoid having to use backticks, because I don't have them on my keyboard.


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
  •