Ipn, Chapter19 And Me

[another_noob]

I have developed a practice site based on chapter 19, just a different product. I have everything working as I want it, without any issues except getting my PayPal ipn.php script to update the database. My return ipn from PayPal is correct because I have saved it to a text file and it is as it should be. The trouble seems to be in my database query in the ipn.php script.  When I return the fake buyer from PayPal to the return thank-you url, the database orders and order_ contents tables are both updated as expected. I have tried everything I can think of in ipn.php to simply update the orders table with the 3 fields I need from PayPal, transaction_id, payment_status, payment_amount. The return url from PayPal gets to my site before the ipn data does which makes my UPDATE query ineffective...there is nothing to update yet at that point. I purchased the E-Commerce book and learned a lot, but this final piece of vital code confuses me. Here is my current not working UPDATE script:

 

require(MYSQL);
					 $txn_id=mysqli_real_escape_string($dbc,$_POST['txn_id']);
					 $q = "SELECT order_id 
					       FROM orders
						   WHERE transaction_id='$txn_id'";
                     $r = mysqli_query($dbc, $q);
                      if(mysqli_num_rows($r) == 0){
					    $uid = (isset($_POST['custom'])) ? (int)$_POST['custom'] : 0;
					    $status = mysqli_real_escape_string($dbc,$_POST['payment_status']);
						$amount=(float)$_POST['mc_gross'];
						$q = "UPDATE orders
           					  SET transaction_id = '$txn_id', payment_status = '$status', payment_amount = $amount
							  WHERE user_id = $uid";
                        $r = mysqli_query($dbc, $q);

So, my question is, what should the ipn.php mysql query look like to update or insert these 3 fields into the orders table based on Script 19.11 in chapter 19?

[Larry]

Could you explain what the problem is with the IPN script NOT performing the update? The need is to get the order stored in the database. Whether that's done through the return script or the IPN script doesn't make any difference. Or maybe I'm not understanding your situation.

[another_noob]

Thank you for replying Larry. Perhaps I am over thinking this situation. You are correct, I am getting the order recorded in the database when the return url I set in PayPal hits my checkout.php script. I took the site live for some tests and the financial transaction is occurring correctly, I get the buyers payment and get an email from PayPal notifying me of the transaction and of course the database "orders" table gets updated through the checkout.php script as written in chapter 19. Of course, as chapter 19 is written, it does not address ipn and that is all explained in the sidebar in the intro to checkout.php, I understand the complexities involved in trying to "sum it up" in one chapter. I am just trying to make sure that I understand ipn. In this case, I don't really see the purpose of ipn other than if the return url from PayPal never finds it's way home I was thinking that the ipn from PayPal would be a back-up notification...if nothing else. Also, as a newbie, I hate to ignore something in a book I don't understand. Specifically speaking, I don't understand the purpose of the transaction_id field in the orders table and how I get ipn.php to update it after a transaction is completed at PayPal. Actually, I don't see why I would want to store the transaction_id in my database when PayPal has that data saved for me in their database. Again, maybe I am over thinking this. Thanks Larry, I hope I cleared up my question a bit.    

[another_noob]

Well, I am embarrassed! I have been working on this project for so long, I forgot that I added the transaction_id field to the orders table. It is not even mentioned in chapter 19. That's what I get for trying to do a mash-up of this book and the first example in the E-Commerce book. I see now what I am trying to do is unnecessary...the folly of a newbie. Sorry to waste your time Larry. By the way, I really like the way you present and explain the material in your books, I have learned a lot. 

[Larry]

No need to apologize. I'm glad you got it working and thanks for letting us know the how's and why's. Thanks, too, for the nice words on my work!