Results 1 to 7 of 7
  1. #1

    error in sql syntax when posting form data

    As part of a university assignment, I have to create a HTML form which will update a MySQL database. The code I'm working with at the moment is below. I've just started with PHP/MySQL etc, so I'm sorry for my cluelessness.

    Here's the code from my ADD EVENT page

    PHP Code:
    include ("admin/connect.php"); 


    $event_add "INSERT INTO $event VALUES('$date','$time','$venue','$cover','$artist','')"
    mysql_query($event_add) or die(mysql_error()); 

    'Details added to the event calendar'); 


    <form name="add" action='<?php echo $_SERVER['PHP_SELF'?>' method="POST"> 
    <b>Date: </b> <input type="text" name="date" size=40> <BR> 
    <b>Time:</b> <input type="text" name="time" size=40><BR> 
    <b>Venue: </b> <input type="text" name="venue" size=40><BR> 
    <b>Cover: </b> <input type="text" name="cover" value="$" rows=5 cols=40><BR> 
    <b>Artist: </b> <input type="text" name="artist" rows=5 cols=40><BR> 
    <p><input type="submit" value="Submit"></p> 
    <input type= 'hidden' name= 'added' value= 'set' > 


    this is my EVENT table structure

    event_id int(11) Not null auto_increment primary key 
    event_date date Not null default: 0000-00-00 
    event_time varchar(5) Not null 
    event_venue varchar(30) Not null 
    event_cover varchar(4) Not null 
    artist_name varchar(10) Not null
    I'm getting the following error when I submit the form data:

    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 'VALUES('2005-12-12','10pm','rics','5','minors','')' at line 1

    I truly have no idea what this means. Could it be something to do with the formatting of the date? I've tried googling the error, but the results were all fairly useless.

  2. #2
    Join Date
    Apr 2005
    syntax for insert in this case is:

    INSERT into table_name (column1, column2....)
    values (value1, value2...)

    So you should have something like:

    INSERT into event (event_date, event_time....) values ('$date', '$time',...)

  3. #3
    Join Date
    Dec 2004
    The column list is optional, omiting it is acceptable, just somewhat silly in case the order of columns in the database ever changes..

    I suspect the problem lies in that your input data columns are in the wrong order. They need to be in the same order as the database schema (unless you specify the columns you want to update as the above poster suggests).

  4. #4
    Join Date
    Apr 2005
    The primary key is auto_increment. Column list is not optional, unless you want to generate the primary key every time.....

  5. #5
    Join Date
    Jul 2003
    In other words, change your VALUES part to VALUES ('','$date',...

    The first '' is for the event_id, which you omitted in your code.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  6. #6
    Join Date
    Apr 2005
    That is dreadful programming practice. If this was code that needed to be worked on by a group or team nobody would have any clue what was being inserted where unless they had a db schema.

  7. #7
    Problem is "INSERT INTO $event"

    Should be "INSERT INTO event"

    event is the table name

Posting Permissions

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