SitePoint Sponsor

User Tag List

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

    SQL syntax error on insert

    Hi all,

    I know this is simple but I am new to PHP and MySQL so be patient.
    I am inserting into a local database using PHP and MySQL and when I try to insert into a table I am getting the following error any idea?
    the field id is auto increment primary key
    Thanks in advance

    INSERT INTO walks (id, 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 (NULL, 'n', 'n', 'n', 'n', 'North', 'n', 'n', 'n', 'n', 'n', 'nn', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'No Image', 'n', 'n', 'n' )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-price2, walk-supplem' at line 1

  2. #2
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You do not need to add the automatically incrementing field. That is added for you by the system. Having NULL in place of the id field will be sufficient. So your insert sql should start like this

    INSERT INTO walks (NULL, title, heading,......
    ------------------

  3. #3
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I have tried that and seem to be gettign the same thing:

    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

  4. #4
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, noe you need to run SHOW CREATE TABLE, and post here your db table structure. Then we can assess it in relation to what you are trying to insert.
    ------------------

  5. #5
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here are the list of field in the table

    walk_id
    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

  6. #6
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should restructure your insert statement as follows:

    Code MySQL:
    INSERT INTO walks VALUES(NULL, 'n', 'n', 'n', 'n', 'n', 'n', 'n'.....);

    Do away listing all the fields of the table. Just do the insertion as described above. It would be simpler. Just make sure that you insert the 23 fields you have in your walks table. You will be fine. Keep in mind that the NULL is in place of the walk_id. So you don't have to insert that manually.
    ------------------

  8. #8
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help I will let you know how I get on with that. Thanks again

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    rageh, that's terrible advice, no offence to you personally (and i really like your slogan about creativity)

    if you have a whole whack of columns defined as NOT NULL and then you just stick 'n' into them all, what the heck good is that?

    further, let's take these columns for example...

    `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,

    what happens if a particular walk doesn't actually have 3 sets of options and prices? well, since they're all NOT NULL, you have to shove ~something~ in there, with the result that the table will eventually fill up with crap

    far better would be to take this opportunity (as the app has not yet really gotten started) to design the table properly
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I have not had much experience with designing sql tables can you give me some advice and tell me what i should be setting each one to be.

    Thanks

  11. #11
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I do what Rageh advised it works and inserts into the sql database but when i have all the field names before it returns a sql syntax error on line 1 really don't understand why this is happening

  12. #12
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anyone got any idea?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by rcrutchley101 View Post
    Hi, I have not had much experience with designing sql tables can you give me some advice and tell me what i should be setting each one to be.
    do a search for first normal form

    any time you have several columns with the same name but with numeric suffixes, that's an indication that they should probably be normalized into their own table

    and please have patience waiting to get replies in this forum -- some of us gots to sleep sometime, and i haven't even had breakfast yet
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    May 2007
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thank you

  15. #15
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you notice these ````` marks in the table dump you've posted?
    It is nesessary in the query too. Around field names

  16. #16
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    rageh, that's terrible advice, no offence to you personally (and i really like your slogan about creativity)

    if you have a whole whack of columns defined as NOT NULL and then you just stick 'n' into them all, what the heck good is that?
    I take your point, rudy.

    But the question was never about the database design issue. The original poster wanted to know how to properly insert the data into the table and he was getting errors. Errors relating to the data insertion. And I advised him on that point.
    ------------------

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i'm sorry, i realize that it came out different than i intended

    you were illustrating the syntax and i wasn't paying attention
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is ok Rudy. No worries.
    ------------------


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
  •