Hello I'm having trouble trying to figure out why I'm getting no addition to my username column within my table. I'm trying to use the payer email within my IPN to introduce a username for login. Everything is being posted except for the username. Is there something I'm doing wrong within the placement of the username variable edits? Is there something I'm missing that is preventing this from being posted?
<?php
// Check to see there are posted variables coming into the script
if ($_SERVER['REQUEST_METHOD'] != "POST") die ("No Post Variables");
// Initialize the $req variable and add CMD key value pair
$req = 'cmd=_notify-validate';
// Read the post from PayPal
foreach ($_POST as $key => $value) {
$value = urlencode(stripslashes($value));
$req .= "&$key=$value";
}
// Now Post all of that back to PayPal's server using curl, and validate everything with PayPal
// We will use CURL instead of PHP for this for a more universally operable script (fsockopen has issues on some environments)
//$url = "https://www.sandbox.paypal.com/cgi-bin/webscr";
$url = "https://www.paypal.com/cgi-bin/webscr";
$curl_result=$curl_err='';
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL,$url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $req);
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/x-www-form-urlencoded", "Content-Length: " . strlen($req)));
curl_setopt($ch, CURLOPT_HEADER , 0);
curl_setopt($ch, CURLOPT_VERBOSE, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);
$curl_result = @curl_exec($ch);
$curl_err = curl_error($ch);
curl_close($ch);
$req = str_replace("&", "\n", $req); // Make it a nice list in case we want to email it to ourselves for reporting
// Check that the result verifies
if (strpos($curl_result, "VERIFIED") !== false) {
$req .= "\n\nPaypal Verified OK";
} else {
$req .= "\n\nData NOT verified from Paypal!";
mail("[email protected]", "IPN interaction not verified", "$req", "From: [email protected]" );
exit();
}
/* CHECK THESE 4 THINGS BEFORE PROCESSING THE TRANSACTION, HANDLE THEM AS YOU WISH
1. Make sure that business email returned is your business email
2. Make sure that the transaction’s payment status is “completed”
3. Make sure there are no duplicate txn_id
4. Make sure the payment amount matches what you charge for items. (Defeat Price-Jacking) */
// Check Number 1 ------------------------------------------------------------------------------------------------------------
$receiver_email = $_POST['receiver_email'];
if ($receiver_email != "[email protected]") {
$message = "Investigate why and how receiver email is wrong. Email = " . $_POST['receiver_email'] . "\n\n\n$req";
mail("[email protected]", "Receiver Email is incorrect", $message, "From: [email protected]" );
exit(); // exit script
}
// Check number 2 ------------------------------------------------------------------------------------------------------------
if ($_POST['payment_status'] != "Completed") {
// Handle how you think you should if a payment is not complete yet, a few scenarios can cause a transaction to be incomplete
}
// Connect to database ------------------------------------------------------------------------------------------------------
require_once 'connect_to_mysql.php';
// Check number 3 ------------------------------------------------------------------------------------------------------------
$this_txn = $_POST['txn_id'];
$sql = mysql_query("SELECT id FROM transactions WHERE txn_id='$this_txn' LIMIT 1");
$numRows = mysql_num_rows($sql);
if ($numRows > 0) {
$message = "Duplicate transaction ID occured so we killed the IPN script. \n\n\n$req";
mail("[email protected]", "Duplicate txn_id in the IPN system", $message, "From: [email protected]" );
exit(); // exit script
}
// Check number 4 ------------------------------------------------------------------------------------------------------------
$product_id_string = $_POST['custom'];
$product_id_string = rtrim($product_id_string, ","); // remove last comma
// Explode the string, make it an array, then query all the prices out, add them up, and make sure they match the payment_gross amount
$id_str_array = explode(",", $product_id_string); // Uses Comma(,) as delimiter(break point)
$fullAmount = 0;
foreach ($id_str_array as $key => $value) {
$id_quantity_pair = explode("-", $value); // Uses Hyphen(-) as delimiter to separate product ID from its quantity
$product_id = $id_quantity_pair[0]; // Get the product ID
$product_quantity = $id_quantity_pair[1]; // Get the quantity
$sql = mysql_query("SELECT price FROM products WHERE id='$product_id' LIMIT 1");
while($row = mysql_fetch_array($sql)){
$product_price = $row["price"];
}
$product_price = $product_price * $product_quantity;
$fullAmount = $fullAmount + $product_price;
}
$fullAmount = number_format($fullAmount, 2);
$grossAmount = $_POST['mc_gross'];
if ($fullAmount != $grossAmount) {
$message = "Possible Price Jack: " . $_POST['payment_gross'] . " != $fullAmount \n\n\n$req";
mail("[email protected]", "Price Jack or Bad Programming", $message, "From: [email protected]" );
exit(); // exit script
}
require_once '../includes/db_conx.php';
//
$payer_email = $_POST['payer_email'];
// Select the member from the users table
$username = substr($payer_email, 0, strpos($payer_email, '@'));
$sql = "SELECT username FROM transactions WHERE username='{$username}%'";
$user_query = mysqli_query($db_conx, $sql);
$numrows = mysqli_num_rows($user_query);
if($numrows < 1){
$i = 0;
while ($name_arr = mysqli_fetch_assoc($result)) {
$name = $name_arr['username'];
$after = substr($name, strlen($username));
if (ctype_digit($after)) {
if (($after = (int) $after) > $i) {
$i = $after;
}
}
}
if ($i > 0) {
$username .= $i;
}
}
// END ALL SECURITY CHECKS NOW IN THE DATABASE IT GOES ------------------------------------
////////////////////////////////////////////////////
// Homework - Examples of assigning local variables from the POST variables
$txn_id = $_POST['txn_id'];
$payer_email = $_POST['payer_email'];
$custom = $_POST['custom'];
$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$payment_date = $_POST['payment_date'];
$mc_gross = $_POST['mc_gross'];
$payment_currency = $_POST['payment_currency'];
$txn_id = $_POST['txn_id'];
$receiver_email = $_POST['receiver_email'];
$payment_type = $_POST['payment_type'];
$payment_status = $_POST['payment_status'];
$txn_type = $_POST['txn_type'];
$payer_status = $_POST['payer_status'];
$address_street = $_POST['address_street'];
$address_city = $_POST['address_city'];
$address_state = $_POST['address_state'];
$address_zip = $_POST['address_zip'];
$address_country = $_POST['address_country'];
$address_status = $_POST['address_status'];
$notify_version = $_POST['notify_version'];
$verify_sign = $_POST['verify_sign'];
$payer_id = $_POST['payer_id'];
$mc_currency = $_POST['mc_currency'];
$mc_fee = $_POST['mc_fee'];
$password = mt_rand(1000, 9999);
$p_hash = md5($password);
$username = $_POST['username'];
// Place the transaction into the database
$sql = mysql_query("INSERT INTO transactions (product_id_array, payer_email, first_name, last_name, payment_date, mc_gross, payment_currency, txn_id, receiver_email, payment_type, payment_status, txn_type, payer_status, address_street, address_city, address_state, address_zip, address_country, address_status, notify_version, verify_sign, payer_id, mc_currency, mc_fee, password, ip, username)
VALUES('$custom','$payer_email','$first_name','$last_name','$payment_date','$mc_gross','$payment_currency','$txn_id','$receiver_email','$payment_type','$payment_status','$txn_type','$payer_status','$address_street','$address_city','$address_state','$address_zip','$address_country','$address_status','$notify_version','$verify_sign','$payer_id','$mc_currency','$mc_fee','$p_hash','$ip','$username')") or die ("unable to execute the query");
$to = $payer_email;
$subject = ' Login Credentials';
$message = '
Your officially all ready to go. To login use the information below.
Your account login information
-------------------------
Email: '.$payer_email.'
Password: '.$password.'
-------------------------
You can now login at https://www.test.com/signin.php';
$headers = 'From:[email protected]' . "\r\n";
mail($to, $subject, $message, $headers);
mysql_close();
// Mail yourself the details
mail("[email protected]", "NORMAL IPN RESULT YAY MONEY!", $req, "From: [email protected]");
?>
[28-Jul-2013 16:05:40 America/Denver] PHP Parse error: syntax error, unexpected T_ELSE in /home/lear/public_html/storescripts/my_ipn.php on line 74
[28-Jul-2013 21:06:37 America/Denver] PHP Warning: mysqli_query() expects parameter 1 to be mysqli, null given in /home/lear/public_html/storescripts/my_ipn.php on line 100
[28-Jul-2013 21:06:37 America/Denver] PHP Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in /home/lear/public_html/storescripts/my_ipn.php on line 102
Edit:
I'vej ust updated the script . The error I'm receiving is as follows:
[28-Jul-2013 22:18:33 America/Denver] PHP Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, null given in /home/learnsit/public_html/storescripts/my_ipn.php on line 108
Line 108 being
while ($name_arr = mysqli_fetch_assoc($result)) {
I haven't played around with this too much, but the piece of code which stands out for me as chiefly suspect is:
$sql = "SELECT `username` FROM `transactions` WHERE `username` = \'' . $username . '%\';";
It should probably look more like
$sql = "SELECT `username` FROM `transactions` WHERE `username` = '{$username}%'";
FYI: - PHP can handle single quotes inside of double quotes without needing a backslash () escape. - You don't need a semicolon (;) escape for a MySQL quote - A great way to check to see if the issue is in fact an MySQL query for your future debugging is to add or die(mysql_error($db)) to the end of your query function, like so:
$result = mysqli_query($db_conx, $sql) or die(mysql_error($db_conx));
Hope this helps!
I've added this to the result area to try and debug, but I'm not receiving anything from it. Would this come up in your standard error page?
Try
echo $sql;
After looking even further at your code it looks like you shouldn't see anything for username='{$username}', because $username is being based off of $payer_email, which has not been declared yet. You'll need to either put those username checks into a function and call it after you've assigned variables the $_POST index, or move the $_POST["payer_email"] to before you first declare the $username variable, as well as getting rid of the $_POST["username"]... Although I haven't seen your form it sounds like you're creating the username through the payer email. Does this help more?
Your error with mysql_fetch_assoc is because you haven't declared $result. I believe you may be trying to refer to $user_query, in which case your line 108 should be:
while ($name_arr = mysqli_fetch_assoc($user_query)) {
I got that and I've been able to post to my database finally, but I can't get it to count and add to the username to prevent duplicates.
First of all I notice you're using username ='{$username}%'. I have a handful of points to make and I hope you listen to them. I'm giving you fish here but I'd like to teach you as well.
You see, when you do something with LIKE {$username}%, you're going to end up getting two rows if someone is trying to sign up as awesome_bob, when someone else already has awesome_bobby. You're going to want something more random, I would think, or to create a more complicated function. Or, just keep username as e-mail, which keeps it unique and is a very simple fix.
I'm going to leave this post at that because I think it has answered your initial question and then some. If you have further questions, I think people would be happy to look at them in a separate topic.
Have I answered your question enough? If so I would appreciate a +1 for my effort :)