So I have the following problem. I have two tables:
paypal_log
user_fees
The fee is fixed, 9000. But I also want to let the user be able to pay for half, 4500, and then 4500 will remain to pay later. I am using paypal to do the payment. When the data is posted to paypal, the paypal_log table also gets populated with the data.
The user completes the payment on paypal, and paypal sends an ipn, which then I use to get transaction id, payment_status and update the paypal_log table. Then I need to update the user_fees table.
Here, what I do is, I have a column in the paypal_log table called isHalf. The query checks if the amount is 4500 and isHalf = 0, then it updates user_fees from 9000, to 4500 and updates is isHalf to 1.
Then there's another if statement, that checks if amount is 4500 and isHalf is 1, at which point it updates user_fees to 0.00.
The first check is for 9000, which automatically goes to 0.00 and it's simple. The problems appear when the user starts a payment and cancels it or starts a payment and just goes off the page because a record still gets created.
My check is below:
$stmt1 = $mysqli->prepare("SELECT userid FROM paypal_log WHERE invoice_id = ? LIMIT 1");
$stmt1->bind_param('i', $invoice_id); //invoice ID that Paypal sent back
$stmt1->execute();
$stmt1->store_result();
$stmt1->bind_result($db_userid);
$stmt1->fetch();
$stmt1->close();
$stmt1 = $mysqli->prepare("SELECT isHalf, product_amount FROM paypal_log WHERE userid = ? ORDER BY created_on ASC LIMIT 1");
$stmt1->bind_param('i', $db_userid);
$stmt1->execute();
$stmt1->store_result();
$stmt1->bind_result($isHalf, $product_amount);
$stmt1->fetch();
$stmt1->close();
if ($product_amount == '9000.00' ) {
$full_fees = 0.00;
$updated_on = date("Y-m-d G:i:s");
$stmt2 = $mysqli->prepare("UPDATE user_fees SET fee_amount=?, updated_on=? WHERE userid = ? LIMIT 1");
$stmt2->bind_param('isi', $full_fees, $updated_on, $db_userid);
$stmt2->execute();
$stmt2->close();
} else {
if ($product_amount == '4500.00' AND $isHalf == '0' ) {
$half_fees = 4500.00;
$isHalf = 1;
$updated_on = date("Y-m-d G:i:s");
$stmt3 = $mysqli->prepare("UPDATE user_fees SET fee_amount=?, updated_on=? WHERE userid=? LIMIT 1");
$stmt3->bind_param('isi', $half_fees, $updated_on, $db_userid);
$stmt3->execute();
$stmt3->close();
$stmt4 = $mysqli->prepare("UPDATE paypal_log SET isHalf=?, updated_on=? WHERE userid=? LIMIT 1");
$stmt4->bind_param('isi', $isHalf, $updated_on, $db_userid);
$stmt4->execute();
$stmt4->close();
} else {
$full_fees = 0.00;
$updated_on = date("Y-m-d G:i:s");
$stmt5 = $mysqli->prepare("UPDATE user_fees SET fee_amount=?, updated_on=? WHERE userid = ? LIMIT 1");
$stmt5->bind_param('isi', $full_fees, $updated_on, $db_userid);
$stmt5->execute();
$stmt5->close();
}
}
I tried to explain it as straight forward as possible. I am aware that my logic is probably not right, hence why I thought I might receive some good answers that will make me think.