PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Sun Feb 01, 2015 11:18 am
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?
Christopher
Site Administrator
Posts: 13596 Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US
Post
by Christopher » Mon Feb 02, 2015 12:15 am
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.
(#10850)
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Mon Feb 02, 2015 3:24 am
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
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Mon Feb 02, 2015 4:36 am
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
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Mon Feb 02, 2015 3:12 pm
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
Christopher
Site Administrator
Posts: 13596 Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US
Post
by Christopher » Mon Feb 02, 2015 4:08 pm
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.
(#10850)
jonnyfortis
Forum Contributor
Posts: 462 Joined: Tue Jan 10, 2012 6:05 am
Post
by jonnyfortis » Tue Feb 03, 2015 8:20 am
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