Jump to content

MySql Error with UPDATE query.


DaVuLf

Recommended Posts

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
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

[!--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\" /]
Link to comment
Share on other sites

[!--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.
Link to comment
Share on other sites

[!--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 ;).
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.