[SOLVED] Problem: Transactional Conditional statement ignored..

[MichaelGallagher]

Hi guys,

 

USING: Windows XP & Windows 2003 Server

XAMPPLITE - most recent version.

 

I have a piece of code that stores a TransactionNumber and a TransactionStart date/time whenever the user accesses a record. It creates the transaction and then waits until the form is posted to create a TransactionEnd.

 

As you can see in the code it has a conditional loop that is checking to make sure it doesnt duplicate it at ANY point once it is created, but i can't for the life of me figure out why it is ignoring the check.. It doesn't do it all the time, and there doesn't appear to be any pattern when it chooses to duplicate.

 

In the body of the page (the html) i have some onChange selects that refresh the page, and they pass the TN and TP variables through the URL, but these do not cause it to produce another transaction, so refreshing the page makes no difference as it follows the rules of the conditional statement fine. IT IS ONLY ON THE FIRST LOAD OF THE PAGE that it duplicates the transaction with exactly the same date/time, but an incremented transaction number...

 

DUPLICATED DATA EXAMPLE (O means transaction is still Open..):

OpportunityIDTransactionStartTransactionEndOpportunityOutcomeTransactionNumber

282007-08-29 11:02:350000-00-00 00:00:00O1

282007-08-29 11:02:350000-00-00 00:00:00O2

 

It should just create one record and wait for it to close... The only point that it would create a second transaction is when the user re-enters the 'Opportunity' record to make a sale, etc.

 

SHOULD BE (while transaction is still Open/Active, before it is closed..):

OpportunityIDTransactionStartTransactionEndOpportunityOutcomeTransactionNumber

282007-08-29 11:02:350000-00-00 00:00:00O1

 

EXAMPLE OF CORRECT (completed transaction) RECORDS (CB means Call Back, and SA means Sale Made.. so they approached the customer one day, set a call back, and then made the sale another day):

OpportunityIDTransactionStartTransactionEndOpportunityOutcomeTransactionNumber

282007-08-28 11:02:352007-08-29 11:14:03CB1

282007-08-29 09:03:302007-08-29 09:10:24SA2

 

As you can see I have set  $TransactionPerformed=('1');  so it shouldn't repeat the process at all once it has created a transaction..should it?

 

 

----- I Hope this is enough information, let me know if you need me to be more precise. -----

 

Kind thanks in advance for any assistance,

 

Michael Gallagher

 

 

////// -- BEGIN DISPLAY OF OPPORTUNITY

// Get what individual OPPORTUNITY to display
$OpportunityID=$_GET['opportunityid'];

// Get TransactionNumber value
$TransactionNumber=$_GET['TN'];

// Get TransactionPerformed value
$TransactionPerformed=$_GET['TP'];

// Check opportunity status to know what reason code was passed:
$opstatus = $_GET['opstatus'];

// Universal CONNECT TO DATABASE
$strDB=mysql_connect($strServer,$strUser,$strPwd) or die 
                     ("Error connecting to mysql");
                     
// Universal SELECT DATABASE
$database=mysql_select_db("$strDatabase",$strDB);

// BEGIN GET OPPORTUNITY AND CUSTOMER INFORMAITON for Opportunity selected from POOL
$sqlGetOpportunity="SELECT * FROM opportunities o inner join customers c on o.ClickPosID = c.ClickPosID WHERE o.OpportunityID=$OpportunityID;";

$resultGetOpportunity = mysql_db_query($strDatabase, $sqlGetOpportunity) or die
						("A MySQL error has occurred.<br />Your Query: " . $sqlGetOpportunity . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());

while ($r = mysql_fetch_array($resultGetOpportunity))
{

// opportunity info
$ClickPosID=$r['ClickPosID'];
$NextCallDate=$r['NextCallDate'];
$Carrier=$r['Carrier'];
$ContractEndDate=$r['ContractEndDate'];
$ContractPeriod=$r['ContractPeriod'];
$InvoiceNumber=$r['InvoiceNumber'];
$MobileNumber=$r['MobileNumber'];
$SalesRep=$r['SalesRep'];
$Notes=$r['Notes'];
$PhoneModel=$r['PhoneModel'];
$PlanCategory=$r['PlanCategory'];
$PlanType=$r['PlanType'];
$FirstPurchaseDate=$r['FirstPurchaseDate'];
$PurchasePrice=$r['PurchasePrice'];
$CIDN=$r['CIDN'];
$AgentResponsible=$r['AgentResponsible'];
$CampaignID=$r['CampaignID'];

// Cust info
$Title=$r['Title'];
$Name=$r['Name'];
$Surname=$r['Surname'];
$JobTitle=$r['JobTitle'];
$HomePhone=$r['HomePhone'];
$Email=$r['Email'];
$Fax=$r['Fax'];
$Mobile=$r['Mobile'];
$WorkPhone=$r['WorkPhone'];
$Address=$r['Address'];
$Address1=$r['Address1'];
$Address2=$r['Address2'];
$Suburb=$r['Suburb'];
$State=$r['State'];
$ZipCode=$r['ZipCode'];
$CompanyName=$r['CompanyName'];
$CompanyTradingName=$r['CompanyTradingName'];
$DNC=$r['DNC'];
$DateCreated=$r['DateCreated'];
}
// END GET OPPORTUNITY AND CUSTOMER INFORMATION for Opportunity selected from OPPORTUNITY POOL


// Update OPPORTUNITIES table records to make Sales Agent "STICKY" to this opportunity (unless they make it a VoiceMail, then it goes to anyone again the next day)
$updateAgentResponsible="UPDATE opportunities SET AgentResponsible='$activeSalesRep', OpportunityStatus='active' WHERE OpportunityID=$OpportunityID;";

mysql_db_query($strDatabase, $updateAgentResponsible) or die
						("A MySQL error has occurred.<br />Your Query: " . $updateAgentResponsible . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());


// CHECK to see if the transaction has been performed yet
if (!$TransactionPerformed) {

$TransactionPerformed=('1');

//// BEGIN GET STATUS INFORMATION for Opportunity selected from POOL ////
$GetStatusInfo = ("SELECT * FROM status WHERE OpportunityID=$OpportunityID ORDER BY TransactionNumber ASC;");

$resultGetStatusInfo = mysql_db_query($strDatabase, $GetStatusInfo) or die
			("A MySQL error has occurred.<br />Your Query: " . $GetStatusInfo . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());

while ($r = mysql_fetch_array($resultGetStatusInfo)) {
	$OpportunityID=$r['OpportunityID'];
	$TransactionStart=$r['TransactionStart'];
	$TransactionEnd=$r['TransactionEnd'];
	$OpportunityOutcome=$r['OpportunityOutcome'];
	$TransactionNumber=$r['TransactionNumber'];
}
//// END GET STATUS INFORMATION ////

// Check TransactionNumber and set appropriately
if (!$TransactionNumber) {
	$TransactionNumber=1;
}
else {
	$TransactionNumber=($TransactionNumber + 1);
}

// INSERT NEW TRANSACTION RECORD -- TransactionStart to current DATETIME ONLY if it doesn't already exist.
$TransactionStart=(date("Y-m-d H:i:s"));

$InsertTransaction="INSERT INTO status (OpportunityID ,TransactionStart ,TransactionEnd ,OpportunityOutcome ,TransactionNumber)
VALUES ($OpportunityID, '$TransactionStart', '0000-00-00 00:00:00', 'O', $TransactionNumber);";

mysql_db_query($strDatabase, $InsertTransaction) or die
								("A MySQL error has occurred.<br />Your Query: " . $InsertTransaction . "<br /> Error: (" . mysql_errno() . ") " . mysql_error());
// END SET TRANSACTION DETAILS

}

[btherl]

Perhaps you should check the database to see if a transaction has started, rather than relying on variables passed to your script.

 

As for debugging the problem, a good technique would be to have your script dump a logfile every time it runs.  The log file should contain the entire contents of $_GET and any other relevant data.  Then you can see what shows up in those logs, and trace it back to where the script call comes from.

 

I usually dump a seperate file for each script run, rather than sticking them all together.  It's easier that way, and you don't run the risk of having output from different script runs mixed together.

[MichaelGallagher]

Yeah it is probably a good idea to check the database, but I would still be interested as to why this does not work, I mean, the code appears logical.

 

Since this is only happening on the FIRST entry to the page, not on any refresh, it must be ignoring $TransactionPerformed=('1');

 

It is definately looping after it has been set to '1' because it increments the TransactionNumber..

 

IF $TransactionPerformed does not exist THEN make $TransactionPerformed = 1, perform insert, END...

 

but it repeats the whole process instantly.

 

I'll check out the databsae idea..

 

Cheers,

 

Mick

 

 

[btherl]

Since this is only happening on the FIRST entry to the page, not on any refresh, it must be ignoring $TransactionPerformed=('1');

 

It is definately looping after it has been set to '1' because it increments the TransactionNumber..

 

More likely that TransactionPerformed is not being set when the script runs a second time.  That could be caused by a lot of things, like browsers fetching the page twice or caches fetching it twice.  TransactionPerformed comes from the $_GET variables, which is risky practice in general.

 

Is that your entire script that you posted?

[MichaelGallagher]

[btherl]

Forgive the 'crapness' it's been a while and I have lost most of my code morals...

 

Programmers for the Ethical Treatement of Code will be in touch with you shortly

 

Well that code is quite long, but is the general structure that $TransactionStarted is set to 1 and then passed back to subsequent script calls via the TP get variable?  If so, then you can debug the problem as I described above (using a log file to catch each execution of the script).  The reason being that the only way the code could be run twice is if the script is run twice.  Therefore, the script was run twice   This can also happen if you accidentally include your script into itself somehow.  You can avoid these double includes by using include_once() instead of include().  I usually use require_once(), which kills the script if the required file is missing.

 

If you don't want to debug the problem and just want to fix it, I would recommend either using sessions or checking the database instead of using a get variable.  I would expect either approach to fix the problem.

[MichaelGallagher]

Hi guys,

 

You wouldn't believe what the problem was. I really think I should be reported to SOME authority on coding... :'(

 

I was linking to the transactional page with BUTTON input types, with ANCHOR tags around them.. which of course sends one request from the ANCHOR TAG and one from the BUTTON OBJECT.

 

I assumed that since it was not a SUBMIT button (just type='button') that it was just a generic unattached object.

 

This plagued me for 3 days, and finally a solution. So I hope that anyone else who ever thinks about surrounding a Button object in an Anchor tag THINKS TWICE!!!

 

Thanks for your help though Brian - I did end up changing the code to check the database, it's a lot cleaner (3 days of intense scrutiny, the code better bloody be clean!!)

 

Mick