Jump to content


Photo

MySql Error with UPDATE query.


  • Please log in to reply
5 replies to this topic

#1 DaVuLf

DaVuLf
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 27 May 2006 - 02:39 PM

I've been having an error with an update query, and I'm not sure why. Here is the code:

//Does the team have any records?
$result = mysql_query("SELECT count(team) FROM portfolio WHERE stock='$stock' AND team='$team'");
$count= mysql_result($result,0,0);
echo 'Count'.$count;
if($count>0){
    //Update our portfolio
    $query = "UPDATE portfolio SET team, stock, avg, quantity, value VALUES ('$team','$stock','$stock_average','$stock_quantity','$stock_worth')";
    $result = mysql_query($query) or die("Error ". mysql_error(). " <br>with query ". $query);
} else {
    //Create our portfolio
    $query = "INSERT INTO portfolio VALUES ('','$team','$stock','$stock_average','$stock_quantity','$stock_worth')";
    $result = mysql_query($query) or die("Error ". mysql_error(). " <br>with query ". $query);

} 

Basically, I check the 'portfolio' table for a record where a team owns a certain stock. If they do, then I run the update query, otherwise, I create the entry. Unfortunately, when it tries to run the update query, I get this error:

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 ' stock, avg, quantity, value VALUES ('Team1','GOOG','149.65','4
with query UPDATE portfolio SET team, stock, avg, quantity, value VALUES ('Team1','GOOG','149.65','4','598.59999999999')

I'm not sure what the issue is. Any help would be appreciated.

Thanks,
DaVuLf

#2 DaVuLf

DaVuLf
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 27 May 2006 - 04:40 PM

After some intensive searching, I came by this fix:

if($count>0){
    //Update our portfolio
    $query = "UPDATE portfolio SET avg='$stock_average', quantity='$stock_quantity', value='$stock_worth' WHERE stock='$stock' AND team='$team'";
    $result = mysql_query($query) or die("Error ". mysql_error(). " <br>with query ". $query);
} else {
    //Create our portfolio
    $query = "INSERT INTO portfolio VALUES ('','$team','$stock','$stock_average','$stock_quantity','$stock_worth')";
    $result = mysql_query($query) or die("Error ". mysql_error(). " <br>with query ". $query);

} 

I think I may have just written the UPDATE query incorrectly.

Thanks!

#3 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 27 May 2006 - 04:49 PM

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]I think I may have just written the UPDATE query incorrectly.[/quote]

Just as well - without the WHERE conditions it might have updated EVERY row of the table [img src=\"style_emoticons/[#EMO_DIR#]/unsure.gif\" style=\"vertical-align:middle\" emoid=\":unsure:\" border=\"0\" alt=\"unsure.gif\" /]
Legend has it that reading the manual never killed anyone.
My site

#4 DaVuLf

DaVuLf
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 27 May 2006 - 05:10 PM

[!--quoteo(post=377613:date=May 27 2006, 12:49 PM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ May 27 2006, 12:49 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Just as well - without the WHERE conditions it might have updated EVERY row of the table [img src=\"style_emoticons/[#EMO_DIR#]/unsure.gif\" style=\"vertical-align:middle\" emoid=\":unsure:\" border=\"0\" alt=\"unsure.gif\" /]
[/quote]

Right you are. Luckily I'm just using test tables right now [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /].

That could have been a big issue, lol.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 May 2006 - 06:11 PM

Indeed.. my custom DB wrapper yells if I have no where clause for any statement; keeps me honest.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 DaVuLf

DaVuLf
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 27 May 2006 - 08:57 PM

[!--quoteo(post=377635:date=May 27 2006, 02:11 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 27 2006, 02:11 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Indeed.. my custom DB wrapper yells if I have no where clause for any statement; keeps me honest.
[/quote]

I hear ya man. I've noticed that a few people on this forum are from Tdot. Pretty nifty ;).




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users