Problems with updating value

[OriginalSunny]

Hi i am trying to update the value for my stock when this page is run. The value of the stock should be deducted by the quantityAs you can see the value of stockID is output and this is output correctly so why doesnt the rest of it work?? And am i better of using REPLACE INTO???

while($row1 = mysql_fetch_array($result1))
{
echo "<tr><td><b>";
echo ''.$row1['stockID'].'';
echo "</b></td>";
echo "<td><b>";
echo ''.$row1['quantity'].'';
echo "</b></td>";
$sql = "SELECT stockAmt FROM Stock
WHERE stockID = $row1['stockID']";
$result = mysql_query($sql,$connect)
or die("sql: ".mysql_error($connect));
$result1 = $result - $row1['quantity'];
echo "$result1";
UPDATE Stock set stockAmt = "result1" WHERE
stockID = $row1['stockID'] ;
}


Its the bit in bold thats causing the problem. I am just using echo to test if the output for the value i want to replace it with is ok.

[sanfly]

Im assuming you havent finished writing your update query because the rest of the code isnt working?

I think the problem may be that your trying to do maths on an array. I have a couple of suggestions

The first im not sure will work but change this line:

[code] $result1 = $result - $row1['quantity'];[/code]

to this:

[code] $result1 = $result[0] - $row1['quantity'];[/code]

The second and most likely answer is to just add an extra line

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]while($row1 = mysql_fetch_array($result1))
{
echo "<tr><td><b>";
echo ''.$row1['stockID'].'';
echo "</b></td>";
echo "<td><b>";
echo ''.$row1['quantity'].'';
echo "</b></td>";
$sql = "SELECT stockAmt FROM Stock
WHERE stockID = $row1['stockID']";
$result = mysql_query($sql,$connect)
or die("sql: ".mysql_error($connect));
[!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]$row = mysql_fetch_array($result);[!--colorc--][/span][!--/colorc--]
$result1 = [!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--]$row['stockAmt'][!--colorc--][/span][!--/colorc--] - $row1['quantity'];
echo "$result1";
UPDATE Stock set stockAmt = "result1" WHERE
stockID = $row1['stockID'] ;
}[/quote]

[OriginalSunny]

Tried it but it keeps comming up with a blank page. Here it is again. Can anyone spot any errors causing it to come up with a blank page? Or find out why it isnt working? The bit in bold is causing the problem. Thanks.

while($row1 = mysql_fetch_array($result1))
{
echo "<tr><td><b>";
echo ''.$row1['stockID'].'';
echo "</b></td>";
echo "<td><b>";
echo ''.$row1['quantity'].'';
echo "</b></td>";

[b] $sql = "SELECT stockAmt FROM Stock
WHERE stockID = $row1['stockID']";
$result = mysql_query($sql,$connect)
or die("sql: ".mysql_error($connect));
$row2 = mysql_fetch_array($result);
$result1 = $row['stockAmt'] - $row1['quantity'];
UPDATE Stock set stockAmt = $result1 WHERE
stockID = $row1['stockID'];[/b]
...
}
echo '</table>';

[sanfly]

How about this

[code]while($row1 = mysql_fetch_array($result1))
{
$stockID = $row1['stockID'];
$quantity = $row1['quantity'];
echo "<tr><td><b>";
echo "$stockID";
echo "</b></td>";
echo "<td><b>";
echo "$quantity";
echo "</b></td>";

$sql = "SELECT stockAmt FROM Stock WHERE stockID = '$stockID'";
$result = mysql_query($sql,$connect) or die("sql: ".mysql_error($connect));
$row2 = mysql_fetch_array($result);
$stockAmt = $row2['stockAmt'];

echo "$stockAmt (stock amount)<br>";

$newStock = $stockAmt - $quantity;


echo "$newStock (new stock amount)";



//$q = "UPDATE Stock set stockAmt = '$newStock' WHERE stockID = '$stockID';
...
}[/code]

[OriginalSunny]

The correct values are now being output but as soon as i use update it come up with a blank white screen again.

$q = UPDATE Stock SET stockAmt = '$newStock' WHERE
stockID = '$stockID';
$q_res = mysql_query($q,$connect)
or die("sql: ".mysql_error($connect));

I cant see where i am going wrong here.