MySql Error with UPDATE query.

[DaVuLf]

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

[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);

}
[/code]

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:

[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 ' 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')
[/code]

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

Thanks,
DaVuLf

[DaVuLf]

After some intensive searching, I came by this fix:

[code]
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);

}
[/code]

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

Thanks!

[AndyB]

[!--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\" /]

[DaVuLf]

[!--quoteo(post=377613:date=May 27 2006, 12:49 PM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ May 27 2006, 12:49 PM) [snapback]377613[/snapback][/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.

[fenway]

Indeed.. my custom DB wrapper yells if I have no where clause for any statement; keeps me honest.

[DaVuLf]

[!--quoteo(post=377635:date=May 27 2006, 02:11 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ May 27 2006, 02:11 PM) [snapback]377635[/snapback][/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 ;).