Page 1 of 1

stock update only updating first record

Posted: Sun Feb 01, 2015 11:18 am
by jonnyfortis
i have information sent back from paypal IPN. i have it updating the orders table to fulfill the order and i have managed to get the SQL to update the first record to deduct by one but if there are more than one item purchased it only updates the first item

Code: Select all

// note: additional IPN variables also available -- see IPN documentation
$itemNumber = $_POST['item_number'];
$paymentStatus = $_POST['payment_status'];
$payerStatus = $_POST['payer_status'];
$pendingReason = $_POST['pending_reason'];
$txnID = $_POST['txn_id'];
$receiverEmail = $_POST['receiver_email'];

///above is part of data sent back from PAYPAL IPN///

mysql_select_db($database_wiz, $wiz);
$query_rsUpdate = "SELECT * FROM wiz_orders, wiz_orderdetails, wiz_Stock WHERE wiz_orderdetails.OrderID = wiz_orders.OrderID AND wiz_orderdetails.StockID = wiz_Stock.StockID AND wiz_orders.TransactID = '" . $txnID . "'";
$rsUpdate = mysql_query($query_rsUpdate, $beau) or die(mysql_error());
$row_rsUpdate = mysql_fetch_assoc($rsUpdate);
$totalRows_rsUpdate = mysql_num_rows($rsUpdate);

//new stock update
if ($row_rsUpdate['Fulfilled'] == 1)  {  
// UPDATE THE DATABASE      

$oldStock = $row_rsUpdate['Stock'];
$stockSold = $row_rsUpdate['Quantity'];
$newStock = $oldStock - $stockSold;
$stockID = $row_rsUpdate['StockID'];

$UpdateQuery="UPDATE wiz_Stock SET Stock = '$newStock' WHERE wiz_Stock.StockID = '$stockID'";

$result=mysql_query($UpdateQuery);
$result=mysql_query($UpdateQuery) or die(mysql_error());

}
}
does it need a loop at the end?

Re: stock update only updating first record

Posted: Mon Feb 02, 2015 12:15 am
by Christopher
If there are possibly multiple rows selected then is should be something like:

Code: Select all

// note: additional IPN variables also available -- see IPN documentation
$itemNumber = $_POST['item_number'];
$paymentStatus = $_POST['payment_status'];
$payerStatus = $_POST['payer_status'];
$pendingReason = $_POST['pending_reason'];
$txnID = $_POST['txn_id'];
$receiverEmail = $_POST['receiver_email'];

///above is part of data sent back from PAYPAL IPN///

mysql_select_db($database_wiz, $wiz);
$query_rsUpdate = "SELECT * FROM wiz_orders, wiz_orderdetails, wiz_Stock WHERE wiz_orderdetails.OrderID = wiz_orders.OrderID AND wiz_orderdetails.StockID = wiz_Stock.StockID AND wiz_orders.TransactID = '" . $txnID . "'";
$rsUpdate = mysql_query($query_rsUpdate, $beau) or die(mysql_error());
while ($row_rsUpdate = mysql_fetch_assoc($rsUpdate)) {

    //new stock update
    if ($row_rsUpdate['Fulfilled'] == 1)  {  
        // UPDATE THE DATABASE      

        $oldStock = $row_rsUpdate['Stock'];
        $stockSold = $row_rsUpdate['Quantity'];
        $newStock = $oldStock - $stockSold;
        $stockID = $row_rsUpdate['StockID'];

        $UpdateQuery="UPDATE wiz_Stock SET Stock = '$newStock' WHERE wiz_Stock.StockID = '$stockID'";

        $result=mysql_query($UpdateQuery);
    }
}
PS - you should really use the mysqli or PDO extensions. The mysql extension is deprecated.

Re: stock update only updating first record

Posted: Mon Feb 02, 2015 3:24 am
by jonnyfortis
Christopher wrote:If there are possibly multiple rows selected then is should be something like:

Code: Select all

// note: additional IPN variables also available -- see IPN documentation
$itemNumber = $_POST['item_number'];
$paymentStatus = $_POST['payment_status'];
$payerStatus = $_POST['payer_status'];
$pendingReason = $_POST['pending_reason'];
$txnID = $_POST['txn_id'];
$receiverEmail = $_POST['receiver_email'];

///above is part of data sent back from PAYPAL IPN///

mysql_select_db($database_wiz, $wiz);
$query_rsUpdate = "SELECT * FROM wiz_orders, wiz_orderdetails, wiz_Stock WHERE wiz_orderdetails.OrderID = wiz_orders.OrderID AND wiz_orderdetails.StockID = wiz_Stock.StockID AND wiz_orders.TransactID = '" . $txnID . "'";
$rsUpdate = mysql_query($query_rsUpdate, $beau) or die(mysql_error());
while ($row_rsUpdate = mysql_fetch_assoc($rsUpdate)) {

    //new stock update
    if ($row_rsUpdate['Fulfilled'] == 1)  {  
        // UPDATE THE DATABASE      

        $oldStock = $row_rsUpdate['Stock'];
        $stockSold = $row_rsUpdate['Quantity'];
        $newStock = $oldStock - $stockSold;
        $stockID = $row_rsUpdate['StockID'];

        $UpdateQuery="UPDATE wiz_Stock SET Stock = '$newStock' WHERE wiz_Stock.StockID = '$stockID'";

        $result=mysql_query($UpdateQuery);
    }
}
PS - you should really use the mysqli or PDO extensions. The mysql extension is deprecated.
yes i will start moving to mysqli, before i do though i need to find some tutorials on how to use it.hoping it isnt much different

Re: stock update only updating first record

Posted: Mon Feb 02, 2015 4:36 am
by jonnyfortis
jonnyfortis wrote:
Christopher wrote:If there are possibly multiple rows selected then is should be something like:

Code: Select all

// note: additional IPN variables also available -- see IPN documentation
$itemNumber = $_POST['item_number'];
$paymentStatus = $_POST['payment_status'];
$payerStatus = $_POST['payer_status'];
$pendingReason = $_POST['pending_reason'];
$txnID = $_POST['txn_id'];
$receiverEmail = $_POST['receiver_email'];

///above is part of data sent back from PAYPAL IPN///

mysql_select_db($database_wiz, $wiz);
$query_rsUpdate = "SELECT * FROM wiz_orders, wiz_orderdetails, wiz_Stock WHERE wiz_orderdetails.OrderID = wiz_orders.OrderID AND wiz_orderdetails.StockID = wiz_Stock.StockID AND wiz_orders.TransactID = '" . $txnID . "'";
$rsUpdate = mysql_query($query_rsUpdate, $beau) or die(mysql_error());
while ($row_rsUpdate = mysql_fetch_assoc($rsUpdate)) {

    //new stock update
    if ($row_rsUpdate['Fulfilled'] == 1)  {  
        // UPDATE THE DATABASE      

        $oldStock = $row_rsUpdate['Stock'];
        $stockSold = $row_rsUpdate['Quantity'];
        $newStock = $oldStock - $stockSold;
        $stockID = $row_rsUpdate['StockID'];

        $UpdateQuery="UPDATE wiz_Stock SET Stock = '$newStock' WHERE wiz_Stock.StockID = '$stockID'";

        $result=mysql_query($UpdateQuery);
    }
}
PS - you should really use the mysqli or PDO extensions. The mysql extension is deprecated.
yes i will start moving to mysqli, before i do though i need to find some tutorials on how to use it.hoping it isnt much different
hello i tried your suggestions

Code: Select all

mysql_select_db($database_wiz, $wiz);
$query_rsUpdate = "SELECT * FROM wiz_orders, wiz_orderdetails, wiz_Stock WHERE wiz_orderdetails.OrderID = wiz_orders.OrderID AND wiz_orderdetails.StockID = wiz_Stock.StockID AND wiz_orders.TransactID = '" . $txnID . "'";
$rsUpdate = mysql_query($query_rsUpdate, $wiz) or die(mysql_error());
while ($row_rsUpdate = mysql_fetch_assoc($rsUpdate)); {

//$totalRows_rsUpdate = mysql_num_rows($rsUpdate);
//end recordset

//new stock update
if ($row_rsUpdate['Fulfilled'] == 1)  {  
// UPDATE THE DATABASE      

$oldStock = $row_rsUpdate['Stock'];
$stockSold = $row_rsUpdate['Quantity'];
$newStock = $oldStock - $stockSold;
$stockID = $row_rsUpdate['StockID'];

$UpdateQuery="UPDATE wiz_Stock SET Stock = '$newStock' WHERE wiz_Stock.StockID = '$stockID'";

$result = mysql_query($UpdateQuery);
}
//$result = mysql_query($UpdateQuery);
//$result=mysql_query($UpdateQuery) or die(mysql_error());

}
but no records are being updated, am i missing something?
thanks in advance

Re: stock update only updating first record

Posted: Mon Feb 02, 2015 3:12 pm
by jonnyfortis
jonnyfortis wrote:
jonnyfortis wrote:
Christopher wrote:If there are possibly multiple rows selected then is should be something like:

Code: Select all

// note: additional IPN variables also available -- see IPN documentation
$itemNumber = $_POST['item_number'];
$paymentStatus = $_POST['payment_status'];
$payerStatus = $_POST['payer_status'];
$pendingReason = $_POST['pending_reason'];
$txnID = $_POST['txn_id'];
$receiverEmail = $_POST['receiver_email'];

///above is part of data sent back from PAYPAL IPN///

mysql_select_db($database_wiz, $wiz);
$query_rsUpdate = "SELECT * FROM wiz_orders, wiz_orderdetails, wiz_Stock WHERE wiz_orderdetails.OrderID = wiz_orders.OrderID AND wiz_orderdetails.StockID = wiz_Stock.StockID AND wiz_orders.TransactID = '" . $txnID . "'";
$rsUpdate = mysql_query($query_rsUpdate, $beau) or die(mysql_error());
while ($row_rsUpdate = mysql_fetch_assoc($rsUpdate)) {

    //new stock update
    if ($row_rsUpdate['Fulfilled'] == 1)  {  
        // UPDATE THE DATABASE      

        $oldStock = $row_rsUpdate['Stock'];
        $stockSold = $row_rsUpdate['Quantity'];
        $newStock = $oldStock - $stockSold;
        $stockID = $row_rsUpdate['StockID'];

        $UpdateQuery="UPDATE wiz_Stock SET Stock = '$newStock' WHERE wiz_Stock.StockID = '$stockID'";

        $result=mysql_query($UpdateQuery);
    }
}
PS - you should really use the mysqli or PDO extensions. The mysql extension is deprecated.
yes i will start moving to mysqli, before i do though i need to find some tutorials on how to use it.hoping it isnt much different
hello i tried your suggestions

Code: Select all

mysql_select_db($database_wiz, $wiz);
$query_rsUpdate = "SELECT * FROM wiz_orders, wiz_orderdetails, wiz_Stock WHERE wiz_orderdetails.OrderID = wiz_orders.OrderID AND wiz_orderdetails.StockID = wiz_Stock.StockID AND wiz_orders.TransactID = '" . $txnID . "'";
$rsUpdate = mysql_query($query_rsUpdate, $wiz) or die(mysql_error());
while ($row_rsUpdate = mysql_fetch_assoc($rsUpdate)); {

//$totalRows_rsUpdate = mysql_num_rows($rsUpdate);
//end recordset

//new stock update
if ($row_rsUpdate['Fulfilled'] == 1)  {  
// UPDATE THE DATABASE      

$oldStock = $row_rsUpdate['Stock'];
$stockSold = $row_rsUpdate['Quantity'];
$newStock = $oldStock - $stockSold;
$stockID = $row_rsUpdate['StockID'];

$UpdateQuery="UPDATE wiz_Stock SET Stock = '$newStock' WHERE wiz_Stock.StockID = '$stockID'";

$result = mysql_query($UpdateQuery);
}
//$result = mysql_query($UpdateQuery);
//$result=mysql_query($UpdateQuery) or die(mysql_error());

}
but no records are being updated, am i missing something?
thanks in advance
sorry i missed i kept the semicolon in

Code: Select all

while ($row_rsUpdate = mysql_fetch_assoc($rsUpdate)); {
now

Code: Select all

while ($row_rsUpdate = mysql_fetch_assoc($rsUpdate)) {
thanks for you help

Re: stock update only updating first record

Posted: Mon Feb 02, 2015 4:08 pm
by Christopher
On comment. Instead of doing this:

Code: Select all

        $oldStock = $row_rsUpdate['Stock'];
        $stockSold = $row_rsUpdate['Quantity'];
        $newStock = $oldStock - $stockSold;
        $stockID = $row_rsUpdate['StockID'];

        $UpdateQuery="UPDATE wiz_Stock SET Stock = '$newStock' WHERE wiz_Stock.StockID = '$stockID'";
I would recommed doing this:

Code: Select all

        $oldStock = $row_rsUpdate['Stock'];
        $stockSold = $row_rsUpdate['Quantity'];
        $newStock = $oldStock - $stockSold;
        $stockID = $row_rsUpdate['StockID'];

        $UpdateQuery="UPDATE wiz_Stock SET Stock = Stock - {$row_rsUpdate['Quantity']} WHERE wiz_Stock.StockID = '$stockID'";
It is usually better to do these kind of updates in the server because you don't know if the value has been changed by another request. You may get a negative value, but you won't get an incorrect value this way.

Re: stock update only updating first record

Posted: Tue Feb 03, 2015 8:20 am
by jonnyfortis
Christopher wrote:On comment. Instead of doing this:

Code: Select all

        $oldStock = $row_rsUpdate['Stock'];
        $stockSold = $row_rsUpdate['Quantity'];
        $newStock = $oldStock - $stockSold;
        $stockID = $row_rsUpdate['StockID'];

        $UpdateQuery="UPDATE wiz_Stock SET Stock = '$newStock' WHERE wiz_Stock.StockID = '$stockID'";
I would recommed doing this:

Code: Select all

        $oldStock = $row_rsUpdate['Stock'];
        $stockSold = $row_rsUpdate['Quantity'];
        $newStock = $oldStock - $stockSold;
        $stockID = $row_rsUpdate['StockID'];

        $UpdateQuery="UPDATE wiz_Stock SET Stock = Stock - {$row_rsUpdate['Quantity']} WHERE wiz_Stock.StockID = '$stockID'";
It is usually better to do these kind of updates in the server because you don't know if the value has been changed by another request. You may get a negative value, but you won't get an incorrect value this way.
yes that make sense. i will give that a go
thanks again