Search the Community

Hi gys, This is a follow up on my last post. I finally managed to sort out the paypal IPN issue that I had. its sending the information and it also logs the information into my database. so no issue there any more. But now I need to credit the users account with the amount of credit they've paid for after successful payment. I have a database with a table called members and a column called balance. How can I update the members balance field after successful payment? this is the ipn.php file code: <?php // Database variables $host = "localhost"; //database location $user = "XXXXXXX"; //database username $pass = "XXXXXXX"; //database password $db_name = "XXXXXXXX"; //database name $test_email=""; // PayPal settings $paypal_email = 'XXXXXXX@gmail.com'; $return_url = 'http://www.XXXX/successful.php'; $cancel_url = 'http://www.XXXX/payment-cancelled.htm'; $notify_url = 'http://www.XXXXXX/payments.php'; $item_name = 'Test Item'; $item_amount = 'amount'; // Include Functions include("functions.php"); //Database Connection $link = mysql_connect($host, $user, $pass); mysql_select_db($db_name); // Check if paypal request or response if (!isset($_POST["txn_id"]) && !isset($_POST["txn_type"])){ // Firstly Append paypal account to querystring $querystring .= "?business=".urlencode($paypal_email)."&"; // Append amount& currency (£) to quersytring so it cannot be edited in html //The item name and amount can be brought in dynamically by querying the $_POST['item_number'] variable. $querystring .= "item_name=".urlencode($item_name)."&"; $querystring .= "amount=".urlencode($item_amount)."&"; //loop for posted values and append to querystring foreach($_POST as $key => $value){ $value = urlencode(stripslashes($value)); $querystring .= "$key=$value&"; } // Append paypal return addresses $querystring .= "return=".urlencode(stripslashes($return_url))."&"; $querystring .= "cancel_return=".urlencode(stripslashes($cancel_url))."&"; $querystring .= "notify_url=".urlencode($notify_url); // Append querystring with custom field //$querystring .= "custom=".USERID; // Redirect to paypal IPN header('location:https://www.sandbox.paypal.com/cgi-bin/webscr'.$querystring); exit(); }else{ // Response from Paypal // read the post from PayPal system and add 'cmd' $req = 'cmd=_notify-validate'; foreach ($_POST as $key => $value) { $value = urlencode(stripslashes($value)); $value = preg_replace('/(.*[^%^0^D])(%0A)(.*)/i','${1}%0D%0A${3}',$value);// IPN fix $req .= "&$key=$value"; } // assign posted variables to local variables $data['item_name'] = $_POST['item_name']; $data['item_number'] = $_POST['item_number']; $data['payment_status'] = $_POST['payment_status']; $data['payment_amount'] = $_POST['mc_gross']; $data['payment_currency'] = $_POST['mc_currency']; $data['txn_id'] = $_POST['txn_id']; $data['receiver_email'] = $_POST['receiver_email']; $data['amount'] = $_POST['amount']; $data['custom'] = $_POST['custom']; $payment_status = $_POST['payment_status']; // post back to PayPal system to validate $header = "POST /cgi-bin/webscr HTTP/1.0\r\n"; $header .= "Content-Type: application/x-www-form-urlencoded\r\n"; $header .= "Content-Length: " . strlen($req) . "\r\n\r\n"; // $paypal_url = "www.paypal.com"; $paypal_url = "www.sandbox.paypal.com"; $fp = fsockopen ($paypal_url, 80, $errno, $errstr, 30); if (!$fp) { // HTTP ERROR } else { fputs ($fp, $header . $req); while (!feof($fp)) { $res = fgets ($fp, 4096); if($payment_status == "Completed" || $payment_status == "Pending"){ // Validate payment (Check unique txnid & correct price) $valid_txnid = check_txnid($data['txn_id']); $valid_price = check_price($data['payment_amount'], $data['item_number']); // PAYMENT VALIDATED & VERIFIED! if($valid_txnid && $valid_price){ $orderid = updatePayments($data); if($orderid){ // Payment has been made & successfully inserted into the Database @mail($test_email, "PAYPAL DEBUGGING", "Payment has been made & successfully inserted into the Database"); exit(); }else{ // Error inserting into DB // E-mail admin or alert user @mail($test_email, "PAYPAL DEBUGGING", "Error inserting into DB"); exit(); } }else{ // Payment made but data has been changed // E-mail admin or alert user // @mail($test_email, "PAYPAL DEBUGGING", "Payment made but data has been changed"); exit(); } } if (strcmp($res, "VERIFIED") == 0) { // Used for debugging @mail($test_email, "PAYPAL DEBUGGING", "Verified Response<br />data = <pre>".print_r($post, true)."</pre>"); // Validate payment (Check unique txnid & correct price) $valid_txnid = check_txnid($data['txn_id']); $valid_price = check_price($data['payment_amount'], $data['item_number']); // PAYMENT VALIDATED & VERIFIED! if($valid_txnid && $valid_price){ $orderid = updatePayments($data); if($orderid){ // Payment has been made & successfully inserted into the Database @mail($test_email, "PAYPAL DEBUGGING", "Payment has been made & successfully inserted into the Database"); }else{ // Error inserting into DB // E-mail admin or alert user @mail($test_email, "PAYPAL DEBUGGING", "Error inserting into DB"); } }else{ // Payment made but data has been changed // E-mail admin or alert user // @mail($test_email, "PAYPAL DEBUGGING", "Payment made but data has been changed"); } }else if (strcmp ($res, "INVALID") == 0) { // PAYMENT INVALID & INVESTIGATE MANUALY! // E-mail admin or alert user // Used for debugging @mail($test_email, "PAYPAL DEBUGGING", "Invalid Response<br />data = <pre>".print_r($post, true)."</pre>"); } } fclose ($fp); } } ?> And this is the code for function.php <?php // functions.php function check_txnid($tnxid){ global $link; return true; $valid_txnid = true; //get result set $sql = mysql_query("SELECT * FROM `payments` WHERE txnid = '$tnxid'", $link); if($row = mysql_fetch_array($sql)) { $valid_txnid = false; } return $valid_txnid; } function check_price($price, $id){ $valid_price = false; //you could use the below to check whether the correct price has been paid for the product /* $sql = mysql_query("SELECT amount FROM `products` WHERE id = '$id'"); if (mysql_numrows($sql) != 0) { while ($row = mysql_fetch_array($sql)) { $num = (float)$row['amount']; if($num == $price){ $valid_price = true; } } } return $valid_price; */ return true; } function updatePayments($data){ global $link; if(is_array($data)){ $sql = mysql_query("INSERT INTO `payments` (txnid, payment_amount, payment_status, itemid, createdtime) VALUES ( '".$data['txn_id']."' , '".$data['payment_amount']."' , '".$data['payment_status']."' , '".$data['item_number']."' , '".date("Y-m-d H:i:s")."' )", $link); return mysql_insert_id($link); } } ?> the function.php file works with the ipn.php file in order to send information back and forth to paypal and updates the mysql database. any help would be appreciated. Thanks

I use the following IPN code from Paypal to trigger my database update. if (!$fp) { // HTTP ERROR } else { fputs ($fp, $header . $req); while (!feof($fp)) { $res = fgets ($fp, 1024); // $res=stream_get_contents($fp, 1024); $res = trim($res); if (strcmp($res, "VERIFIED") == 0) { if ($payment_status === 'Completed') { $txn_id_check = mysql_query("SELECT txn_id FROM log WHERE txn_id = '".$txn_id."'"); if (mysql_num_rows($txn_id_check) != 1) { if ($receiver_email=='seller_999@me.com') { if ($payment_amount == '0.09' && $payment_currency == 'CAD') { // add txn to database $log_query = mysql_query("INSERT INTO log (txn_id, email) VALUES ('".$txn_id."', '".$payer_email."')"); // update premium to 1 $update_premium = mysql_query("UPDATE users SET premium = 1 WHERE user_id = '".$user_id."' "); } } } } } else if (strcmp ($res, "INVALID") == 0) { // log for manual investigation } } fclose ($fp); } The updating has never kicked in. I tried to debug it and found it has been blocked at if (strcmp($res, "VERIFIED") == 0) . From other post of this forum I learned to trace the IPN return script and found $res doesn't exist. Does anyone know how to fix it?