Updating form after response from payment gateway

[Aluwe]

Hi All,

I need your help in solving this problem.
The scenario:
1. Students fill enrolment form - form 1 (Form)
2. Enrolment data displayed for students with a unique transaction reference number. - Form 2 (Confirm page)
3. This takes the student to payment gateway and form finalised - redirection page and Form 3 (Process page) Form finalised

4. Payment Gateway payment processing

5. After payment, a response is sent to the response page having this code. (response page/thankyou page)

<?php
$hostname = "myhost";
$username="my_form_tools_database username";
$password="my_password";
$dbname="my_database;
$usertable="ft_form_99";
mysql_connect($hostname,$username,$password);

mysql_select_db("my_database") or die("Could not select database");

if(isset($_POST['ce_transref'])){
// the following are the parameters returned by $_POST
$ceresponse = strtoupper($_POST['ce_response']);
$cetransref = $_POST['ce_transref'];
$ceamt = $_POST['ce_amount'];
$cedata = $_POST['ce_data'];
$cememo = $_POST['ce_memo'];
if($cedata=='datacode')
{
$query="UPDATE ft_form_99 SET ce_response='$ceresponse', ce_transref='$cetransref', ce_amount='$ceamt', ce_data='$cedata', ce_memo='$cememo',
WHERE cetxref='$cetransref'";

mysql_query($query);
mysql_close();

}



else
{
// terminate because this transaction was not initiated from your store; it's a fake
}
}
?>

5 (a) After payment, a response page is displayed to the students as thank you for your payment. But with the above php on top.

In my database, the following field supposed to be updated by filling data from the response from the payment gateway:
ce_response
ce_transref
ce_amount
ce_data
ce_memo

I have tested the form, all other data are ok, and the forms are finalised. The only snag is that I cannot update the above data in red.

Please advise if there is another way, I can get the response to work and update the submitted finalised data with the response from the payment gateway.

[Ben]

Hi Aluwe,

That's a perfectly decent approach - I don't see anything wrong with it in principle; it looks like there's just a bug occurring somewhere down the line.

A couple of things to check:
1. So the ce_response, ce_transref etc. fields are the database field names, correct? Not just the form field names? I always forget this myself, so double-check that.

2. Are you sure that SQL is in fact getting executed? Echo "here" in the if($cedata=='datacode') clause. Also, stick an "or die()" to the end of the query for a little debugging:

PHP Code:

mysql_query($query) or die(mysql_error()); 


But assuming that cetxref is unique across all submissions in the table, I don't see anything wrong at all.. Very possibly it's data-oriented: the content being fed back to you doesn't have the values you're expecting.

Let me know how it goes! Interesting one.

- Ben

[Aluwe]

Thank you Ben, the code below update the database. The problem now is to use the pre-parser to send emails to students, base on the response code. I will be working on this now. Any help on how to use the preparser to do this will be appreciate.

The form has been finalised, prior to updating with this code, which was a response from the payment gateway. So I want to use the update in the preparser to send the emails each time the responses are inserted or altered.

Ben, thank you for the encouragement. Should I remove this sql code now "mysql_query($query) or die(mysql_error()); "

<?php
$hostname = "myhost.com";
$username="myusername";
$password="mypassword";
$dbname="mydatabasename";
$usertable="mytable";
mysql_connect($hostname,$username,$password);

mysql_select_db("mydatabase") or die("Could not select database");

if(isset($_POST['ce_transref']))
{
$ce_res['C00'] = "Transaction successful.";
$ce_res['C01'] = "Transaction cancelled. ";
$ce_res['C02'] = "Transaction cancelled by inactivity.";
$ce_res['C03'] = "Transaction aborted by user.";

// the following are the parameters returned by $_POST
$ceresponse = strtoupper($_POST['ce_response']);
$cetransref = $_POST['ce_transref'];
$ceamt = $_POST['ce_amount'];
$cedata = $_POST['ce_data'];
$cememo = $_POST['ce_memo'];
//echo $cedata;


//print_r ($ceresponse);


if($cedata=='mycode)
{
$query="UPDATE ft_form_99 SET ce_response= '$ceresponse', ce_transref= '$cetransref', ce_amount= '$ceamt', ce_data= '$cedata', ce_memo='$cememo' WHERE cetransref= '$cetransref'";

mysql_query($query);
//mysql_close();
mysql_query($query) or die(mysql_error());

}



else
{
// terminate because this transaction was not initiated from your store; it's a fake
}
}
?>

[Ben]

Hi Aluwe,

Great! Sounds like you're making progress.

Yes, I'd drop the or die(mysql_error()) bit now. It's generally not a good idea to have die()'s in your production code.

Regarding triggering the Pre-Parser rule, hmm... I wonder what would be the best approach here...

Here's the thing: the Submission Pre-Parser was designed to attach your code to the Core Form Tools events - adding, editing and deleting submissions within the Form Tools UI. Since you're doing it manually, you'd need to either manually trigger that even and fool the module into thinking it just occurred in the Core, or simply cut & paste the PHP code in your rule.

Despite the code redundancy, the copy-paste method may actually be a better approach. The reason is that it will ensure your code continues to work even if the Submission Pre-Parser module changes somewhere down the road. But it's judgement call. Feel free to disagree with me.

If you'd rather try to trigger the Pre-Parser rule directly, I'd try something like this:

PHP Code:

require_once("/path/to/module/submission_pre_parser/library.php");
$info = array(
  "form_tools_calling_function" => "ft_update_submission",
  "form_id" => 99
);
spp_parse($info); 


It's untested, but I think the idea is sound. First, it includes the main code for the module, then it compiles a list of data needed for the spp_parse() function.

That spp_parse function calls all rules that have been defined for that event (updating a submission) on that form (ID #99).

However: it *won't* include other info, like the submission ID - which you may be using in your rule. You'll need to locate and add that to the $info var in order to use it in your Pre-Parser rule. That's the drawback: the core function adds in that info for you, but by manually doing it like this, you'll need to construct it manually.

It's a bit complicated, but hopefully the general approach makes sense.

Let me know how it goes! Good luck!

- Ben

[Aluwe]

Thank you Ben. I have been trying without success. At which point in the above code should I insert the trigger you suggested. Thank you very much.

[Ben]

Hi Aluwe,

You should be able to add that code right after the mysql_query(). No luck?

Maybe try adding it, then in your Submission Pre-Parser rule add an:

PHP Code:

echo "here!";
exit; 


This would at least confirm that the Submission Pre-Parse rule is being called.

Sorry, this is a dense problem to debug via a forum!

- Ben