SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    May 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question SQL Syntax error... I Cant seem to debug this

    Hi All, I am writing a script to enter values into a database and i keep getting the following error:

    Code:
    Error: 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 '-con-fname, pri-con-lname, pri-con-mi, pri-con-pphone, pri-con-pphone-ext, pri-c' at line 1
    Code PHP:
    <?php
    session_start();
    $con = mysql_connect($_SESSION['dbhost'],$_SESSION['dbuser'],$_SESSION['dbpass']);
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
     
    mysql_select_db("c2_dj", $con);
    ///===PRIMARY CONTACT===///
    $sqlpri="INSERT INTO events (pri-con-fname, pri-con-lname, pri-con-mi, pri-con-pphone, pri-con-pphone-ext, pri-con-sphone, pri-con-sphone-ext, pri-con-pemail, pri-con-semail, pri-con-hrs)
    VALUES
    ('$_POST[priconfname]', '$_POST[priconlname]', '$_POST[priconmi]', '$_POST[priconpphone]', '$_POST[priconpphext]', '$_POST[priconsphone]', '$_POST[priconsphext]', '$_POST[priconpemail]', '$_POST[priconsemail]', '$_POST[priconhrs1]')";
    ///===SECONDARY CONTACT===///
    $sqlsec="INSERT INTO events (sec-con-lname, sec-con-mi, sec-con-pphone, sec-con-pphone-ext, sec-con-sphone, sec-con-sphone-ext, sec-con-pemail, sec-con-semail, sec-con-hrs)
    VALUES
    ('$_POST[secconfname]', '$_POST[secconlname]', '$_POST[secconmi]', '$_POST[secconpph]', '$_POST[secconpphext]', '$_POST[secconsph]', '$_POST[secconsphext]', '$_POST[secconpemail]', '$_POST[secconsemail]', '$_POST[secconhrs1]')";
    ///===LOCATION INFO===///
    $sqlloc="INSERT INTO events (loc-address, loc-aptunit, loc-town, loc-state, loc-zip, loc-rm-width, loc-rm-length, loc-rm-height, loc-rm-wall, loc-rm-ceiling)
    VALUES
    ('$_POST[locaddress]', '$_POST[locaptunit]', '$_POST[loctown]', '$_POST[locstate]', '$_POST[loczip]', '$_POST[locwidth]', '$_POST[loclength]', '$_POST[locheight]', '$_POST[locwalls]', '$_POST[locceiling]')";
    ///===EVENT INFO===///
    $sqleve="INSERT INTO events (event-theme, event-type, event-attend, event-marketing, event-pep)
    VALUES
    ('$_POST[eventtheme]', '$_POST[eventtype]', '$_POST[eventattendees]', '$_POST[eventmarketing]', '$_POST[eventpep]')";
    ///===MUSIC INFO===///
    $sqlmxk="INSERT INTO events (music-pgenre, music-sgenre, music-clean)
    VALUES
    ('$_POST[musicpgenre]', '$_POST[musicsgenre]', '$_POST[musiclean]')";
    ///===LIGHT INFO===///
    $sqllit="INSERT INTO events (light-scheme, light-list, light-fog, light-fog-pref)
    VALUES
    ('$_POST[lightscheme]', '$_POST[lightlist]', '$_POST[fog]', '$_POST[fogpref]')";
    ///===SOUND & MISC INFO===///
    $sqlsou="INSERT INTO events (sound-scheme, sound-mic, marketing-allowed)
    VALUES
    ('$_POST[soundscheme]', '$_POST[mic]', '$_POST[marketingallowed]')";
    ///==========================================================================================///
    if ((mysql_query($sqlpri,$con)) && mysql_query($sqlsec,$con) && mysql_query($sqlloc,$con) && mysql_query($sqleve,$con) && mysql_query($sqlmxk,$con) && mysql_query($sqllit,$con) && mysql_query($sqlsou,$con))
    {
    echo "<html><center><br><br><BR><BR><BR><BR><BR><BR>1 record added";
    header("location:./new.event.php");
    	}
      die('Error: ' . mysql_error());
    /// $sqlsec,$sqlloc,$sqleve,$sqlmxk,$sqllit,$sqlsou,$con))
    ///header('./new.event.php');
    mysql_close($con)
    ?>

    I Am Using PHP5 on ubuntu server 10.10. with apache 2 7 MySQL

    I Cant seem to figure this one out. Any ideas???

    Thanks,
    JL Griffin

  2. #2
    SitePoint Zealot Cute Tink's Avatar
    Join Date
    Apr 2009
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perhaps the database doesn't like your hyphens. Try enclosing your column names in backquotes: `pri-con-fname`

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no "perhaps" about it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    A curiosity: why do you insert the data for different columns in different insert statements? This way you'll get multiple lines, each with just some columns valorized. Why do you do it that way?
    How do you know what rows contain the data of one event?

  5. #5
    SitePoint Member
    Join Date
    May 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb

    Quote Originally Posted by guido2004 View Post
    A curiosity: why do you insert the data for different columns in different insert statements? This way you'll get multiple lines, each with just some columns valorized. Why do you do it that way?
    How do you know what rows contain the data of one event?
    Ahh.. yeah, I had all the statements one one line originally. The problem was that I kept get ing errors like
    Code:
    error unexpected ' on line13. Unexpected ] on line13  Unexpected ( on line13.
    And with such a long line13 it was near impossible to figure out where the errors were so I split it up. After I get it all debugged I'm going to put it all back into one statement.

  6. #6
    SitePoint Member
    Join Date
    May 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cute Tink View Post
    Perhaps the database doesn't like your hyphens. Try enclosing your column names in backquotes: `pri-con-fname`
    PHP sure as hell doesn't like hyphens does it. I had to rename all my form variables to not have a hyphen in them because it wouldn't accept a post variable with a hyphen.

    OK thanks. I'll do that

  7. #7
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by griffinwebnet View Post
    Ahh.. yeah, I had all the statements one one line originally. The problem was that I kept get ing errors like
    Code:
    error unexpected ' on line13. Unexpected ] on line13  Unexpected ( on line13.
    And with such a long line13 it was near impossible to figure out where the errors were so I split it up. After I get it all debugged I'm going to put it all back into one statement.
    You can
    PHP Code:
    $sql "Start a 
    query and then
    keep pushing
    enter until
    you reach the end"


  8. #8
    SitePoint Member
    Join Date
    May 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Quote Originally Posted by StarLion View Post
    You can
    PHP Code:
    $sql "Start a 
    query and then
    keep pushing
    enter until
    you reach the end"

    Cool I will do that that makes way more sense than the way I did it. Lol a noob mistake. Lol

    Thank you. That will save me about of time in the future.

  9. #9
    SitePoint Member
    Join Date
    May 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry everyone by the way. I forgot to tell you That I won't be around my computer until later. That's why I didn't post if it worked off not.

    Lol.I have been reading And responding via my phone. I'll let you know asap.

    JL Griffin

  10. #10
    SitePoint Member
    Join Date
    May 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    Ok That one fix worked. but now i am getting a seperate error.

    in my original code i posted onece i inserted these " ` " it seemed to work, but it would only execute the query $sqlpri so i put it all back on one line and tell me that i couldnt use the && in my if ststement because it was only expecting 2 values. so i put all the stuff back onto one query and now i get this.
    Code:
    Error: Column count doesn't match value count at row 1
    Please help. Here is my new code:
    PHP Code:
    <?php
    session_start
    ();
    $con mysql_connect($_SESSION['dbhost'],$_SESSION['dbuser'],$_SESSION['dbpass']);
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }

    mysql_select_db("c2_dj"$con);
    ///===PRIMARY CONTACT===///
    $sql="INSERT INTO events (`pri-con-fname`, `pri-con-lname`, `pri-con-mi`, `pri-con-pphone`, `pri-con-pphone-ext`, `pri-con-sphone`, `pri-con-sphone-ext`, `pri-con-pemail`, `pri-con-semail`, `pri-con-hrs`, `sec-con-lname`, `sec-con-mi`, `sec-con-pphone`, `sec-con-pphone-ext`, `sec-con-sphone`, `sec-con-sphone-ext`, `sec-con-pemail`, `sec-con-semail`, `sec-con-hrs`, `loc-address`, `loc-aptunit`, `loc-town`, `loc-state`, `loc-zip`, `loc-rm-width`, `loc-rm-length`, `loc-rm-height`, `loc-rm-wall`, `loc-rm-ceiling`, `event-theme`, `event-type`, `event-attend`, `event-marketing`, `event-pep`, `music-pgenre`, `music-sgenre`, `music-clean`, `light-scheme`, `light-list`, `light-fog`, `light-fog-pref`, `sound-scheme`, `sound-mic`, `marketing-allowed`)
    VALUES
    ('
    $_POST[priconfname]', '$_POST[priconlname]', '$_POST[priconmi]', '$_POST[priconpphone]', '$_POST[priconpphext]', '$_POST[priconsphone]', '$_POST[priconsphext]', '$_POST[priconpemail]', '$_POST[priconsemail]', '$_POST[priconhrs1]', '$_POST[secconfname]', '$_POST[secconlname]', '$_POST[secconmi]', '$_POST[secconpph]', '$_POST[secconpphext]', '$_POST[secconsph]', '$_POST[secconsphext]', '$_POST[secconpemail]', '$_POST[secconsemail]', '$_POST[secconhrs1]', '$_POST[locaddress]', '$_POST[locaptunit]', '$_POST[loctown]', '$_POST[locstate]', '$_POST[loczip]', '$_POST[locwidth]', '$_POST[loclength]', '$_POST[locheight]', '$_POST[locwalls]', '$_POST[locceiling]', '$_POST[locaddress]', '$_POST[locaptunit]', '$_POST[loctown]', '$_POST[locstate]', '$_POST[loczip]', '$_POST[locwidth]', '$_POST[loclength]', '$_POST[locheight]', '$_POST[locwalls]', '$_POST[locceiling]', '$_POST[eventtheme]', '$_POST[eventtype]', '$_POST[eventattendees]', '$_POST[eventmarketing]', '$_POST[eventpep]', '$_POST[musicpgenre]', '$_POST[musicsgenre]', '$_POST[musiclean]', '$_POST[lightscheme]', '$_POST[lightlist]', '$_POST[fog]', '$_POST[fogpref]', '$_POST[soundscheme]', '$_POST[mic]', '$_POST[marketingallowed]')";
    ///==========================================================================================///
    if (!mysql_query($sql,$con))
    {
      die(
    'Error: ' mysql_error());
    }
    echo 
    "<html><center><br><br><BR><BR><BR><BR><BR><BR>1 record added";
    mysql_close($con)
    ?>

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you've definitely messed up your column counts

    sometimes it helps to copy/paste them side by each --
    Code:
    INSERT                                                           
      INTO events                 VALUES                             
         ( `pri-con-fname`             ( '$_POST[priconfname]'       
         , `pri-con-lname`             , '$_POST[priconlname]'       
         , `pri-con-mi`                , '$_POST[priconmi]'          
         , `pri-con-pphone`            , '$_POST[priconpphone]'      
         , `pri-con-pphone-ext`        , '$_POST[priconpphext]'      
         , `pri-con-sphone`            , '$_POST[priconsphone]'      
         , `pri-con-sphone-ext`        , '$_POST[priconsphext]'      
         , `pri-con-pemail`            , '$_POST[priconpemail]'      
         , `pri-con-semail`            , '$_POST[priconsemail]'      
         , `pri-con-hrs`               , '$_POST[priconhrs1]'        
         , `sec-con-lname`             , '$_POST[secconfname]'       
         , `sec-con-mi`                , '$_POST[secconlname]'       
         , `sec-con-pphone`            , '$_POST[secconmi]'          
         , `sec-con-pphone-ext`        , '$_POST[secconpph]'         
         , `sec-con-sphone`            , '$_POST[secconpphext]'      
         , `sec-con-sphone-ext`        , '$_POST[secconsph]'         
         , `sec-con-pemail`            , '$_POST[secconsphext]'      
         , `sec-con-semail`            , '$_POST[secconpemail]'      
         , `sec-con-hrs`               , '$_POST[secconsemail]'      
         , `loc-address`               , '$_POST[secconhrs1]'        
         , `loc-aptunit`               , '$_POST[locaddress]'        
         , `loc-town`                  , '$_POST[locaptunit]'        
         , `loc-state`                 , '$_POST[loctown]'           
         , `loc-zip`                   , '$_POST[locstate]'          
         , `loc-rm-width`              , '$_POST[loczip]'            
         , `loc-rm-length`             , '$_POST[locwidth]'          
         , `loc-rm-height`             , '$_POST[loclength]'         
         , `loc-rm-wall`               , '$_POST[locheight]'         
         , `loc-rm-ceiling`            , '$_POST[locwalls]'          
         , `event-theme`               , '$_POST[locceiling]'        
         , `event-type`                , '$_POST[locaddress]'        
         , `event-attend`              , '$_POST[locaptunit]'        
         , `event-marketing`           , '$_POST[loctown]'           
         , `event-pep`                 , '$_POST[locstate]'          
         , `music-pgenre`              , '$_POST[loczip]'            
         , `music-sgenre`              , '$_POST[locwidth]'          
         , `music-clean`               , '$_POST[loclength]'         
         , `light-scheme`              , '$_POST[locheight]'         
         , `light-list`                , '$_POST[locwalls]'          
         , `light-fog`                 , '$_POST[locceiling]'        
         , `light-fog-pref`            , '$_POST[eventtheme]'        
         , `sound-scheme`              , '$_POST[eventtype]'         
         , `sound-mic`                 , '$_POST[eventattendees]'    
         , `marketing-allowed` )       , '$_POST[eventmarketing]'    
                                       , '$_POST[eventpep]'          
                                       , '$_POST[musicpgenre]'       
                                       , '$_POST[musicsgenre]'       
                                       , '$_POST[musiclean]'         
                                       , '$_POST[lightscheme]'       
                                       , '$_POST[lightlist]'         
                                       , '$_POST[fog]'               
                                       , '$_POST[fogpref]'           
                                       , '$_POST[soundscheme]'       
                                       , '$_POST[mic]'               
                                       , '$_POST[marketingallowed]')
    can you see better now why there's a mismatch?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    70 Post(s)
    Tagged
    0 Thread(s)
    You have 44 field names, and 55 $_POST variables.

    What appears to be unmatched:
    $_POST['secconfname']

    You appear to have put the $_POST['locXXXXX'] entries twice, which accounts for the remaining offset.

  13. #13
    SitePoint Member
    Join Date
    May 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THANK YOU ALL SO MUCH! it finally displayed the "1 record added" !!

    Thank you everyone for your help!


  14. #14
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,023
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cute Tink View Post
    Perhaps the database doesn't like your hyphens. Try enclosing your column names in backquotes: `pri-con-fname`
    Quote Originally Posted by r937 View Post
    no "perhaps" about it
    As to why this is so for eveyone else (I know r937 knows this) -- Hyphens are interpreted as minus signs. SQL thinks you want the value of field "pri" minus value of field "con" minus the value of field "fname"

    Simple solution - never use hyphens in field or table names. Neither PHP nor SQL like it when you do. But as a short term solution backticks will get it in there.

  15. #15
    SitePoint Member
    Join Date
    May 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Quote Originally Posted by Michael Morris View Post
    As to why this is so for eveyone else (I know r937 knows this) -- Hyphens are interpreted as minus signs. SQL thinks you want the value of field "pri" minus value of field "con" minus the value of field "fname"

    Simple solution - never use hyphens in field or table names. Neither PHP nor SQL like it when you do. But as a short term solution backticks will get it in there.
    Cool. that does make alot of sense. thank you. one of the most annoying things about programming is when you have to do something and have no idea why or the logic behind it. i really appreciate the explanation.

    thanks again
    -JL Griffin


Tags for this Thread

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
  •