Form randomly saving twice

[New Coder]

Hello all I have a form for a meeting unable to attend log.

An attendee calls in to say they will not be able to make meeting and the form saves the reason etc into a db and infroms (via emails) all staff members who would have attended of the cancelation.

 

It works but for some reason the record saves twice in the db on occasion and I'm unsure why.

 

The loggers fills in some deatils on a form called log.php and then clicks save which goes to the following page:

 

<?php
$auth = $_COOKIE['auth'];
$login_id = $_COOKIE['login_id'];
header("Cache-Control:no-cache");
if(!$auth == "ok")
{ header("Location:../log_index.php");
exit();
}
include("../php_include/connection.php");
include("../php_include/functions.php");

$absent_date = $_POST['absent_date'];
$call_time = $_POST['hour'] .":". $_POST['mins'];
$reason = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['reason']) : $_POST['reason'];
$reason = fix_quotes($reason);
$log_date = date ("d/m/Y");
$log_time = date("G:i");
$attendee_id = str_replace("\'","'",$_POST['attendee_id']);
$attendee_name = str_replace("\'","'",$_POST['attendee_name']);
$attendee_email = str_replace("\'","'",$_POST['attendee_email']);
$sent = $_POST['sent'];

if( !$sent )
{
echo( "Error - Data not sent from main form.<br><form action=\"log.php\"><input type=\"submit\" name=\"back\" value=\"Back\"></form>" );
}
else 
{ 
$sql = "SELECT staff_username FROM appointments where attendee_id = \"$attendee_id\" and date = \"absent_date\" ";

$rs = mssql_query( $sql, $conn )
		or die( "Please contact admin and quote: Could not execute Find Staff Query" );

if(mssql_num_rows($rs)!=0) 
{
	while ($row = mssql_fetch_array($rs)) 
	{
		$staff_email = $row["staff_username"]. "@ourdomain.co.uk,";
		$to_staff .= $staff_email;
	}
    	$re_staff = "Unable to attend appointment logged for $attendee_name ($attendee_id)";
    	$msg_staff = "An unable to attend appointment has been logged for $attendee_name ($attendee_id) \n\nReason: $reason \n\nDate of Absence: $absent_date \n\nTime of Call: $call_time \n\nLog Date: $log_date \n\nLog Time: $log_time \n\nLogger: $login_id \n\nThank You";
    	$headers_staff = "From: No-Reply@ourdomain.co.uk \r\n";	
	if( mail( $to_staff, $re_staff, $msg_staff, $headers_staff ) )
	{

		$sql2 = "insert into att_log (attendee_id, reason, absent_date, call_time, log_date, log_time, logger) values (\"$attendee_id\", \"$reason\", \"$absent_date\", \"$call_time\", \"$log_date\", \"$log_time\", \"$login_id\") ";
		$rs2 = mssql_query( $sql2, $conn )
				or die( "Please contact admin and quote: Could not execute Save Absence Log");

		$re = "Absence logged for you - $attendee_name ($attendee_id)";
		$msg = "An absence has been logged for you - $attendee_name ($attendee_id) \n\nReason: $reason \n\nDate of Absence: $absent_date \n\nTime of Call: $call_time \n\nLog Date: $log_date \n\nLog Time: $log_time \n\nLogger: $login_id \n\nThank You";
		$headers = "From: No-Reply@ourdomain.co.uk \r\n";

		if( mail( $attendee_email, $re, $msg, $headers ) )
		{	
			$sent_to = str_replace(",",",<br>",$to_staff);		
			echo("Thank you, An email has been sent to the attendees meeting organisers<br><br>$sent_to<br>as notification of this absence.<br><br>An email confirming this absence has been sent to the attendees email account.<br><form action=\"log.php\"><input type=\"submit\" name=\"back\" value=\"Back\"></form>");
		}
		else
		{
			echo("Error.....Email not sent to attendee.<br>This may be because the attendee did not provide an email account.<br>Please go back and check the record has been saved.<br><form action=\"log.php\"><input type=\"submit\" name=\"back\" value=\"Back\"></form>");
		}
	}
	else
	{ 
		echo("Error.....Email not sent and record not saved.<br>This may be because the Email server is not responding. Please go back and try again.<br>If the problem persists please contact Admin.<br><form action=\"log.php\"><input type=\"submit\" name=\"back\" value=\"Back\"></form>");
	}
}
else
{
	echo("<br><br>Error.....Email not sent and record not saved.<br><br>This may be because the there is no record of a meeting for this Attendee.<br>Please contact Admin with the Attendee ID: ".attendee_id."<br><br><form action=\"log.php\"><input type=\"submit\" name=\"back\" value=\"Back\"></form>");
}
}

include("../php_include/close_all.php");
?>

 

Can anyone shed some light?

Or suggest code improvements?

Many Thanks

[New Coder]

Hell all,

 

I have been playing around and I'm convinced it's a prob with the while loop, so I have thought of trying to check that the while loop has completed before anything else is done.

 

so would replacing:

if(mssql_num_rows($rs)!=0) 	{		while ($row = mssql_fetch_array($rs)) 		{			$staff_email = $row["staff_username"]. "@ourdomain.co.uk,";			$to_staff .= $staff_email;		}

 

with

 

 

$finished = false;
while ( ! $finished ):
	if ($row = mssql_fetch_array($rs)) 
	{
		$staff_email = $row["staff_username"]. "@ourdomain.ac.uk,";
		$to_staff .= $staff_email;
	}
	endif;
	$finished = true; 
endwhile;
if ($finished = true)
{ rest of code

 

Work?

 

Am I thinking in the right direction?

 

Many Thanks

[New Coder]

Anyone?  :(

[rwwd]

Personally I would do this:-

if(mssql_num_rows($rs) > 0){

Instead of this:-

 

if(mssql_num_rows($rs) != 0){

 

And I hope that you know that you can just do this: values ($attendee_id, $reason, Opposed to doing it with the escaped quotes, if you really want to you could just do this: values ('".$attendee_id."', '".$reason."', For ech of those, therefore negating the need to escape the quotes.

 

Rw

 

 

[New Coder]

Thanks for the tips rwwd. That would be cleaner, old habits etc

 

Does anyone know why its running the insert statement twice?

[litebearer]

Just a thought...

 

As the attendee-id is unique, rather than using a 'while loop' simply use (psuedo code)

$test_rows = mssql_num_rows($rs);
if($test_rows == 1) {
$row = mssql_fetch_array($rs);
  do whatever else you need to do here
}else{
  do something else
}

 

[rwwd]

Just a thought...

 

As the attendee-id is unique, rather than using a 'while loop' simply use (psuedo code)

$test_rows = mssql_num_rows($rs);
if($test_rows == 1) {
$row = mssql_fetch_array($rs);
  do whatever else you need to do here
}else{
  do something else
}

 

 

That, I like.  Though I am unsure as to why it's doing it twice, following this pseudo code, it may well just make the process easier, but not solve the issue, though, stranger things have happened!

 

Rw

[sasa]

try to redirect page via heder after update database

 

btw. from where commas (,) come in variable $send_to?

[DavidAM]

@rwwd:

And I hope that you know that you can just do this: values ($attendee_id, $reason, Opposed to doing it with the escaped quotes, if you really want to you could just do this: values ('".$attendee_id."', '".$reason."', For ech of those, therefore negating the need to escape the quotes.

 

Rw

I hope you meant that he could use single quotes inside of the double quotes, like this:

$sql2 = "insert into att_log (attendee_id, reason, absent_date, call_time, log_date, log_time, logger) 
values ('$attendee_id', '$reason', '$absent_date', '$call_time', '$log_date', '$log_time', '$login_id') ";

Of course, any of those that are numeric do not need the quotes; but the strings and dates DO need them.

 

@New Coder

It is possible that the Staff email succeeds, then the INSERT succeeds, then the Attendee email FAILS and the user re-enters the record with basically the exact same data except they put the attendee's email in correctly (or whatever), and post the form again.

 

Look for patterns in the duplicate entries: All the attendees logged have an apostrophe in their name (O'Roark, O'Rielly, O'Niel) or something unusual in the reason or name fields.  You are not consistent in the way you collect the POST data. 

 

Does the email get sent twice when the INSERT happens twice? I don't see any reason why the INSERT would happen twice unless the entire script runs twice. Could it be that the user is clicking the submit button a second time because it is taking a while and they think that it is not running?

 

Do these duplicate records have the same logtime?  If the times are different, then the script must have started a second time because you set that value at the top.  You're only logging the hour and minute, so this might not be definitive. If possible, add seconds to that.  Of course, it could be happening so quickly that that value might not change either.

 

You might try logging some of your error conditions to a flat file, with a date-time stamp. Then review this log when you find duplicate records.  Maybe a particular error is occurring, and the user "knows" that they just have to hit refresh and it will work the second time (or something).

 

Also, review your close_all.php include file. Make sure you are not processing $sql2 in some way under some condition that is causing that query to execute again.

 

[New Coder]

Right this just gets wierder.

I have tried all the suggestions and no solution.

I have stripped this script down to the bare bones and it still randomly inserts twice

 

basic script:

 

 

<?php$login_id = $_COOKIE['login_id'];include("../php_include/connection.php");$absent_date = $_POST['absent_date'];$call_time = $_POST['hour'] .":". $_POST['mins'];$reason = (! get_magic_quotes_gpc ()) ? addslashes ($_POST['reason']) : $_POST['reason'];$reason = fix_quotes($reason);$log_date = date ("d/m/Y");$log_time = date("G:i");$attendee_id = str_replace("\'","'",$_POST['attendee_id']);$sql = "insert into att_log (attendee_id, reason, absent_date, call_time, log_date, log_time, logger) values (\"$attendee_id\", \"$reason\", \"$absent_date\", \"$call_time\", \"$log_date\", \"$log_time\", \"$login_id\") ";$rs = mssql_query( $sql, $conn )or die( "Please contact admin and quote: Could not execute Save Absence Log");include("../php_include/close_all.php");?>

 

 

and close_all.php is only

 

<?php				mssql_free_result($rs);mssql_free_result($rs2);mssql_free_result($rs3);mssql_free_result($rs4);mssql_free_result($rsauth);mssql_free_result($rstot);mssql_free_result($rs_rev);mssql_free_result($rs_lookup);mssql_free_result($rsefl);mssql_free_result($rsdd1);mssql_free_result($rsdd2);mssql_free_result($rsdd3);mssql_free_result($rsdd4);mssql_free_result($rsdd5);mssql_free_result($rsdd6);mssql_free_result($rsdd7);odbc_free_result($rs);odbc_free_result($rs2);odbc_free_result($rs3);odbc_free_result($rs4);odbc_free_result($rsauth);odbc_free_result($rsdraft);odbc_free_result($rscheckb);odbc_free_result($rscheck);mssql_close ( $conn );odbc_close ( $conn2 );odbc_close ( $conn3 );odbc_close ( $conn4 );?>

 

I have noticed though, and I'm not sure if this is a red herring, but I can only get it to replicate the insert twice randomly using IE, Firefox has not produced this issue.

 

It's driving me up the wall....

[Pikachu2000]

One thing you can try is checking to see if the query successfully inserted a record with mssql_rows_affected(), and if it did, use a header() redirect and an exit() immediately after. That should prevent a browser-initiated double submission. Although it doesn't fix the root problem, it does address the symptoms.

[New Coder]

i just added

 

echo ("rows: ".  mssql_rows_affected($conn));

 

after the insert and always recieve rows: 1

 

but sometimes it done it twice??? my head hurts....

[New Coder]

Right I don't have a solution but thought I would share what I found.

After many google searches it would appear I'm not alone and interestingly what I have found, has been reported recently too. see below:

 

http://www.codingforums.com/showthread.php?s=ef29ae7fd407dc9a2a70c428a11a3495&p=997352#post997352

 

Only difference is they report issues with firefox??

Would IE's new compatability function be causing the problem as that can force a page reload? If so how to I make my script compatable??

 

 

[litebearer]

might look here...

http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm

 

Use INSERT IGNORE rather than INSERT. If a record doesn't duplicate an existing record, MySQL inserts it as usual. If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.

 

Following example does not error out and same time it will not insert duplicate records.

 

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Jay', 'Thomas');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Jay', 'Thomas');

Query OK, 0 rows affected (0.00 sec)

 

 

Use REPLACE rather than INSERT. If the record is new, it's inserted just as with INSERT. If it's a duplicate, the new record replaces the old one:

 

mysql> REPLACE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Ajay', 'Kumar');

Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)

    -> VALUES( 'Ajay', 'Kumar');

Query OK, 2 rows affected (0.00 sec)

 

 

INSERT IGNORE and REPLACE should be chosen according to the duplicate-handling behavior you want to effect. INSERT IGNORE keeps the first of a set of duplicated records and discards the rest. REPLACE keeps the last of a set of duplicates and erase out any earlier ones.

 

Another way to enforce uniqueness is to add a UNIQUE index rather than a PRIMARY KEY to a table.

 

[New Coder]

Thanks litebearer while your suggestion was specifically for mysql and I'm using mssql you gave me a new direction to look in.

 

I have used IF NOT EXISTS and It seems to be functioning correctly now.

 

$sql2 = "IF NOT EXISTS (SELECT * FROM att_log WHERE attendee_id='$attendee_id' AND log_date='$log_date' AND log_time='$log_time' ) insert into att_log (attendee_id, reason, absent_date, call_time, log_date, log_time, logger) values (\"$attendee_id\", \"$reason\", \"$absent_date\", \"$call_time\", \"$log_date\", \"$log_time\", \"$login_id\") ";

 

I won't close as solved just yet as i want to do a few more tests.

 

Thank you

[rwwd]

as I stated in a previous post:-

values ('".$attendee_id."', '".$reason."', '".$absent_date."', '".$call_time."', '".$log_date."', '".$log_time."', '".$login_id."') 

 

you don't need to have the toothpicks in there, quote-escape-concatenate, easier to follow.

 

Glad you getting there too.

 

Rw

[New Coder]

Yea sorry rwwd, I have made that change and appreciate the help.

I rewrote the query about a million and one times while troubleshooting etc so I was just reverting to old habits.

 

Thanks