[RESOLVED] WHERE clause problem.

[vinny199]

hi,

Ok, this may sound confusing, I'll try to explain best as possible.

I am working with Worldpay payment gateway, using their "callback" featue: payment result / details being returned to my site for updating the database.

I am also using one of their product "futurepay" which is a recurring payment system: I.E, every week, your membership is renewed automatically and money taken of your credit card.

Either upon first payment, or "futurepay paments", the same data is returned to my site and I am able to update my database.

All good.

Where I have a problem is when The customer cancels his membership.
A different string of info is returned to the same page of the website.

On payments, I update my database using the ".$cartID."' field which is given by me, sent to worldpay, and returned with the transaction result.

But upon cancelation of membership, worldpay does not send me back the ".$cartID."'.
Instead, I get '".$futurePayIDField."' as the payment reference,w hich is fine because that value is entered into the database upon first payment.

So my WHERE clause has two possible scenario:

WHERE Uid = '".$cartID."' OR futurepayID = '".$futurePayIDField."'";

so it updates the right row of the database upon payment, or cancelation of payment.

$query_rsusers1 = "UPDATE users SET transStatus = '".$transtatusField."',
transID = '".$transIDField."',
cartID = '".$cartID."',
amount = '".$amountField."',
futurepayID = '".$futurePayIDField."',
FuturePayStatusChange = '".$futurePaystatusChange."',
ut1 = '".$PaymentIDField."'
  WHERE Uid = '".$cartID."' OR futurepayID = '".$futurePayIDField."'";  

NOW THE PROBLEM:

Upon cancelation, only two of the above fields are returned to my site:
'".$futurePayIDField."',
'".$futurePaystatusChange."',

which means that with my current query, upon cancelation, all the other values are NULL and overwrite what wwas previously stored in the table.

Upon cancelation, I need the field:
'".$transtatusField."' not to be overwriten with NULL .

How can I achieve that?

I hope this pmakes sense...

Thanks,

Vinny

[msk_1980]

I am bit confused too. But if i understood it right:

why dont you try to use "if" to check if the field has a value returned or not. if not returned do not include it in the query.

try to make your query dynamically something like: (Please be carefull with " and ', i am not right here)

$query_rsusers1 = "UPDATE users SET transStatus = '".$transtatusField. "',";

if ( isset ( $transIDField ) )
   {
   $query_rsusers1 .= 'transID = '".$transIDField.',"';
   }

..........................................................

Hope that helps, but sorry if i am wrong or if i understood it wrong.

[vinny199]

Thanks,

I think you understood right actually.

But let me just make sure if you don't mind

I am trying to say (in plain english) EITHER:

If '".$transtatusField."' = empty DO NOT overwrite previous value in the database

OR

if '".$futurePaystatusChange."' = NOT empty DO NOT overwrite previous ".$transtatusField."' value in the database

I just don't know about dynamic query and how to phrase this properly..

Is what you suggested going towards what I describe above?

many thanks,
vinny

[msk_1980]

yeap thats what it does. because if you dont include it in the update query it wont update that value and leaves the old value there.

when i say dynamic query its just you build your query depending on conditions, you use string concat "." (dot) and build up your query.

If you want the actual query, i am at work so i might help you when i get home.

ta.

[vinny199]

Thanks a lot,

I'll play with it and try to write it ok.
I'm not against making the effort to learn of course.

However, I'd be very grateful if you could post the full code when you have time later so I can see where I may go wrong and use it as an example for future similar issues if you don't mind.

Thanks again.

[bpat1434]

$query_rsusers1 = "UPDATE users SET transStatus = '".$transtatusField."'";
if($transIDField){ $query_rsusers1 .= ", transID = '".$transIDField."'"; }
if($cartID){ $query_rsusers1 .= ", cartID = '".$cartID."'"; }
if($amountField){ $query_rsusers1 .= ", amount = '".$amountField."'"; }
if($futurePayIDField){ $query_rsusers1 .= ", futurepayID = '".$futurePayIDField."'"; }
if($futurePaystatusChange){ $query_rsusers1 .= ", FuturePayStatusChange = '".$futurePaystatusChange."'"; }
if($PaymentIDField){ $query_rsusers1 .= ", ut1 = '".$PaymentIDField."'"; }
$query_rsuesers1 .= "  WHERE Uid = '".$cartID."' OR futurepayID = '".$futurePayIDField."'";

I believe that is what he was talking about.

[vinny199]

humm..

That did not work.

what that did is upon cancellation, the ENTIRE column FuturePayStatusChange (all rows) was updated, regardless of the "where futurepayID = '".$futurePayIDField."'";" which it used to do

And

And the ENTIRE column FuturePayStatusChange was overwritten (all rows) with NULL as of copurse this value does not exist upon cancelation.

[msk_1980]

just a quick confirmation:

if approved:
you get all the fields and you use cartID to update the data.

if cancelled:
you get futurepayID, FuturePayStatusChange only and you use futurepayID to updat the data.

[vinny199]

100% correct

if approved:
you get all the fields and you use cartID to update the data.

if cancelled:
you get futurepayID, FuturePayStatusChange only and you use futurepayID to updat the data.

I'll just phrase it slightly differently in case it makes it clearer:

if A PAYMENT IS MADE (the payment can be approved, or fail):
you get all the fields and you use cartID to update the data.

if THE PAYMENT SUBSCRIPTION IS cancelled:
you get futurepayID, FuturePayStatusChange only and you use futurepayID to updat the data.

as of course in this case, no transaction has taken place

[bpat1434]

So why not just have 2 separate queries?

<?php

if($cartID)
{
  // Must be Approved or Failed payment
   $query_rsusers1 = "UPDATE users SET transStatus = '".$transtatusField."',
transID = '".$transIDField."',
cartID = '".$cartID."',
amount = '".$amountField."',
futurepayID = '".$futurePayIDField."',
FuturePayStatusChange = '".$futurePaystatusChange."',
ut1 = '".$PaymentIDField."'
  WHERE Uid = '".$cartID."'"; 
}
else
{
  // Must be a cancellation:
  $query_rsusers1 = "UPDATE users SET FuturepayStatusChange = '".$futurepaystatusChange."' WHERE futurepayID = '".$futurePayIDField."'";
}

That seems to be a suitable solution, wouldn't you say?

[vinny199]

thanks, that's perfect.

It's working and I'm learning a lot.

Cheers,

V.

[bpat1434]

Glad to hear you got it working!!