I have the following code. It queries my database, looking for the one record (There are no repeats of the auto-numbered userid field, so there will only be one record) which has a value of my variable, $actionrecordnumber. How do I edit another field (numshares)? I would like to subtract the value $actionumshares from the current numshares number in the field of the one record. Confusing?

PHP Code:

mysql_query("UPDATE users WHERE userid='$actionrecordnumber' SET numshares=numshares-$actionnumshares") or die("Can't Perform Query Two"); 


The $actionrecordnumber variable is set in the URL of the PHP script. It works great.
The $actionnumshares variable is set earlier in the script.


Thanks,

Andrew

First, it is typical SQL structure to put the SET statement before the qualifying WHERE, so I would recomment you restructure your existing query.

Also, is the userid field a VARCHAR or a numeric type like INT? By putting your value $actionrecordnumber in single quotes, you are implying that it is a VARCHAR. If that is the case, I would recommend you changing it to an auto incrementing numeric type, such as int. This makes indexing vastly more efficient and depending on the size of your table, you may notice performance improvements.




The query you asked about should look like this (I am writing it assuming that your userid field is a VARCHAR as in your previous query):

PHP Code:


$sql = "SELECT numshares FROM users WHERE userid='$actionrecordnumber' ";
$result = mysql_query( $sql );

if( mysql_num_rows( $result ) != 1 )
{
    // ...... we have some kind of error here, no?
}

$row = mysql_fetch_array( $result );

$numberOfShares = (int) $row[ numshares ];
$numberOfShares -= $actionumshares;

$sql = "UPDATE users SET numshares=$numberOfShares WHERE userid='$actionrecordnumber' ";
mysql_query( $sql ); 


Thank you!
It's not working right now, and I think it's because my variables (Specifically, the $actionrecordnumber) are not being set. If I have the URL below, will the variable $actionrecordnumber be set to '2"? Right now, when I call this variable it is at '0'.

PHP Code:

http://longurletcetcetcetc.com/pages/sell.php?actionrecordnumber=2&globalvariable=200 


Of course, because the number of records returned != '1', I'm getting the error message I put in the code you provided for me.

By the way: It's nice to see a Marylander - I lived in Frederick and Mt. Airy for several years, before I was "dragged" away from the East.....

Andrew

Andrew,

I put that check in there about the 1 records because you had said the userid was unique -- so if we're pulling the records WHERE userid is equal to something, you wouldn't want more than one records..... So yea, check on that and let me know.

Yes, I used to live in Carroll County (New Windsor) then moved to Washington County last October and I love it. I commute through Frederick and past Mt. Airy on I-70 everyday. I'm originally from NC, but MD has been home for 12 years. It grows on you and I like living in the foothills of the Blue Ridge now.

I corrected it. It wasn't the check - Your code works wonders. I just didn't correctly set the values of my variables. (I'm not getting the hang of the setting variables in the URL thing.) I ended up just using a hidden field with a POST action to the php file.

MD is a great state. Of the many places I've lived, (I can claim enough states to run for President) I think it is the one I want to return to. ..And a perfect location...

Andrew