UPDATE error

[harkly]

Getting an error but I cannot figure out why, can some help?

 

This is the error msg

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 '1', r_b='1', rap='1', oldCountry='', country='', metalRock='', classical='', jaz' at line 1

 

this is my code for it

 

  $rock=$_POST['rock'];
   $r_b=$_POST['r_b'];
   $rap=$_POST['rap'];
   $oldCountry=$_POST['oldCountry'];
   $country=$_POST['country'];
   $metalRock=$_POST['metalRock'];
   $classical=$_POST['classical'];
   $jazz=$_POST['jazz'];
   $opera=$_POST['opera'];
   $punkRock=$_POST['punkRock'];
   $ot_music=$_POST['ot_music'];
   $otMusic_txt=$_POST['otMusic_txt'];

$music = "UPDATE music SET rock='$rock', r_b='$r_b', rap='$rap', oldCountry='$oldCountry', country='$country', metalRock='$metalRock', classical='$classical', jazz='$jazz', opera='$opera', punkRock='$punkRock', ot_music='$ot_music', otMusic_txt='$otMusic_txt'
          WHERE userID='kelly'";
$result = mysql_query($music) or die(mysql_error());

 

 

It is updating the table and I have identical code but a different table that works just fine.

[ngreenwood6]

it could be that you are putting in a ' or a " in the field? you should always run data that is going into a database through mysql_real_escape_string() ex:

 

$id = mysql_real_escape_string($_POST['somethinghere']);

$query = "INSERT INTO table (id) VALUES ('$id')";

 

That code will escape all ' or " making it "safe" for the database. it also prevents errors when users try to use a ' or a " for data going into the database.

[harkly]

The only thing that is being passed is a 1 or null. And really is being passed, when I check the form my selection is there. Which is why I am so confused about the msg.

 

 

My form code

 

               \n";


                 if ($classical == 1){
                   echo "
                 <br><input type='checkbox' name='classical' value='1' checked>Classical  \n";}
                 else {
                   echo "
                 <br><input type='checkbox' name='classical' value='1'>Classical \n";}


                 if ($jazz == 1){
                   echo "
                 <br><input type='checkbox' name='jazz' value='1' checked>Jazz  \n";}
                 else {
                   echo "
                 <br><input type='checkbox' name='jazz' value='1'>Jazz \n";}

                 if ($opera == 1){
                   echo "
                 <br><input type='checkbox' name='opera' value='1' checked>Opera \n";}
                 else {
                   echo "
                 <br><input type='checkbox' name='opera' value='1'>Opera \n";}

                 if ($punkRock == 1){
                   echo "
                 <br><input type='checkbox' name='punkRock' value='1' checked>Punk Rock  \n";}
                 else {
                   echo "
                 <br><input type='checkbox' name='punkRock' value='1'>Punk Rock \n";}                  

                 if ($ot_music == 1){
                   echo "
                 <br><input type='checkbox' name='ot_music' value='1' checked>Other <input type='text' name='otMusic_txt' size='12' class='zip' value='$otMusic_txt'>\n";}
                 else {
                   echo "
                 <br><input type='checkbox' name='ot_music' value='1'>Other <input type='text' name='otMusic_txt' size='12' class='zip' value='$otMusic_txt'>\n";}
                 
                    echo "

[PFMaBiSmAd]

The only way the posted query statement could produce the posted error is if $rock contains a single-quote or something else that would break the syntax of the query statement.

 

Echo the $music variable to see exactly what is in it.

 

Best guess is that your code between where you set $rock and where you put it into the query statement is screwing up the value in it.

[harkly]

I redid everything making sure they all work on there own, but still getting the error when I put the all together.

 

I verified that the database is being update inspite of the error msg - seems strange that it would.

 

I'm not even sure where I should be looking.

 

I am updating data to 5 different tables, here is my update code

 

<?php
  mysql_select_db('bariatric'); 


   $userID=$_POST["userID"];
   $sports=$_POST["sports"];
   $exercise=$_POST["exercise"];
   $outdoors=$_POST["outdoors"];
   $artistic=$_POST["artistic"];
   $tv_shows=$_POST["tv_shows"];
   $free_time=$_POST["free_time"];
   $favorite_places=$_POST["favorite_places"];
   $favorite_things=$_POST["favorite_things"];
   $aerobics=$_POST["aerobics"];
   $cycling=$_POST["cycling"];
   $jog_run=$_POST["jog_run"];
   $bike=$_POST["bike"];
   $racquetball=$_POST["racquetball"];
   $rock_climb=$_POST["rock_climb"];
   $rollerblade=$_POST["rollerblade"];
   $swim=$_POST["swim"];
   $walk_hike=$_POST["walk_hike"];
   $weights_machine=$_POST["weights_machine"];
   $yoga=$_POST["yoga"];
   $ot_exer=$_POST["ot_exer"];
   $otExer_txt=$_POST["otExer_txt"];
   $acting=$_POST["acting"];
   $dance=$_POST["dance"];
   $design=$_POST["design"];
   $music=$_POST["music"];
   $multimedia=$_POST["multimedia"];
   $paint=$_POST["paint"];
   $photo=$_POST["photo"];
   $sculpture=$_POST["sculpture"];
   $sing=$_POST["sing"];
   $writing=$_POST["writing"];
   $ot_art=$_POST["ot_art"];
   $otArt_txt=$_POST["otArt_txt"];
   $exploring=$_POST["exploring"];
   $book_club=$_POST["book_club"];
   $camping=$_POST["camping"];
   $coffee=$_POST["coffee"];
   $business_networking=$_POST["business_networking"];
   $cooking=$_POST["cooking"];
   $dining_out=$_POST["dining_out"];
   $fishing_hunting=$_POST["fishing_hunting"];
   $garden_landscape=$_POST["garden_landscape"];
   $hobbies_craft=$_POST["hobbies_craft"];
   $movies=$_POST["movies"];
   $museums_art=$_POST["museums_art"];
   $music_concerts=$_POST["music_concerts"];
   $nightclubs_dance=$_POST["nightclubs_dance"];
   $perform_art=$_POST["perform_art"];
   $cards=$_POST["cards"];
   $sports_play=$_POST["sports_play"];
   $politics=$_POST["politics"];
   $religion=$_POST["religion"];
   $shop_antiques=$_POST["shop_antiques"];
   $travel_sightseeing=$_POST["travel_sightseeing"];
   $video_games=$_POST["video_games"];
   $volunteer=$_POST["volunteer"];
   $sports_watch=$_POST["sports_watch"];
   $wine=$_POST["wine"];
   $ot_hobbies=$_POST["ot_hobbies"];
   $ot_hobbies_txt=$_POST["ot_hobbies_txt"];
   $rock=$_POST["rock"];
   $rnb=$_POST["rnb"];
   $rap=$_POST["rap"];
   $oldCountry=$_POST["oldCountry"];
   $country=$_POST["country"];
   $metalRock=$_POST["metalRock"];
   $classical=$_POST["classical"];
   $jazz=$_POST["jazz"];
   $opera=$_POST["opera"];
   $punkRock=$_POST["punkRock"];
   $ot_music=$_POST["ot_music"];
   $otMusic_txt=$_POST["otMusic_txt"];


$query = "UPDATE interest SET sports='$sports', exercise='$exercise', outdoors='$outdoors', 
          artistic='$artistic', tv_shows='$tv_shows', free_time='$free_time', favorite_places='$favorite_places',
          favorite_things='$favorite_things' WHERE userID='kelly'";
$result = mysql_query($query) or die(mysql_error());


$music = "UPDATE music SET rock='$rock', rnb='$rnb', rap='$rap', oldCountry='$oldCountry', country='$country', metalRock='$metalRock', classical='$classical', jazz='$jazz', opera='$opera', punkRock='$punkRock', ot_music='$ot_music', otMusic_txt='$otMusic_txt' WHERE userID='kelly'";
$result = mysql_query($music) or die(mysql_error());


$hobbies = "UPDATE hobbies SET exploring='$exploring', book_club='$book_club', camping='$camping', coffee='$coffee', business_networking='$business_networking', cooking='$cooking', dining_out='$dining_out', fishing_hunting='$fishing_hunting', garden_landscape='$garden_landscape', hobbies_craft='$hobbies_craft', movies='$movies', museums_art='$museums_art', music_concerts='$music_concerts', nightclubs_dance='$nightclubs_dance', perform_art='$perform_art', cards='$cards', sports_play='$sports_play', politics='$politics', religion='$religion', shop_antiques='$shop_antiques', travel_sightseeing='$travel_sightseeing', video_games='$video_games', volunteer='$volunteer', sports_watch='$sports_watch', wine='$wine', ot_hobbies='$ot_hobbies', ot_hobbies_txt='$ot_hobbies_txt' WHERE userID='kelly'";
$result = mysql_query($hobbies) or die(mysql_error());


$exercise = "UPDATE exercise SET aerobics='$aerobics', cycling='$cycling', jog_run='$jog_run', bike='$bike', racquetball='$racquetball', rock_climb='$rock_climb', rollerblade='$rollerblade', swim='$swim', walk_hike='$walk_hike', weights_machine='$weights_machine', yoga='$yoga', ot_exer='$ot_exer', otExer_txt='$otExer_txt' WHERE userID='kelly'";
$result = mysql_query($exercise) or die(mysql_error());


$art ="UPDATE art SET acting='$acting', dance='$dance', design='$design', music='$music', multimedia='$multimedia', paint='$paint', photo='$photo', sculpture='$sculpture', sing='$sing', writing='$writing', ot_art='$ot_art', otArt_txt='$otArt_txt' WHERE userID='kelly'";
$result = mysql_query($art) or die(mysql_error());


      if ($result)
      {
         echo "<div id='aboutForm'>Information changed.</div> \n";

      }
      else
      {
         echo "<h2>Sorry, I could not change the information.</h2>\n";
      }



?>

[PFMaBiSmAd]

The query that is failing is the last one because you are reusing the $music variable in one of the earlier queries, so when you put $music into the last query it actually contains a whole query statement, not the contents from the form field.

 

I recommend always building your query statement in a variable named $query (there is no need to make up a different variable name for each independent query.)

 

 

[harkly]

THANKS! That was it!