Problem double insert

[MichelDS]

I have search the net and at the end  tried 2 things that didn't solved the problem. It is known that certain browsers can refresh th epage 2 times without us knowing bacause it's doing it all by himself and so fast we don't even see it blink !

 

So I have following code for the normal sql-insert :

$Opdracht = "INSERT INTO tbl_link(userid,linkcat,linksubid,linklang,linkactive,linktitle) 	 VALUES('$userid',1,0,'$lang',1,'$newML')";

 

it was inserted 2 times...

 

I did some session check :  at the top of the page :

session_start();

if(isset($_SESSION['itel'])){
    $_SESSION['itel'] = $_SESSION['itel']+ 1;
}
else {
    $_SESSION['itel'] = 1;
}
echo "<br>session: ". $_SESSION['itel'];

 

And it gave me number 2 !  This means the page was loaded 2 times, thus inserted 2 times.  !

 

Than I tried :

$Opdracht = "INSERT INTO tbl_link(userid,linkcat,linksubid,linklang,linkactive,linktitle) 
		VALUES('$userid',1,0,'$lang',1,'$newML') 				 
		 ON DUPLICATE KEY UPDATE linkid=LAST_INSERT_ID(linkid), linktitle='$linktitle'";

 

I got no error back but again  2 rows were created instead of 1...

 

These are the fields in the table tbl_link :  linkid userid linkcat linksubid linksuborder linklang linkactive linktitle articleid

Unfortunately certain fields may be double in multiple rows, the only unique key is "linkid" and that's AUTO_INCREMENT.

 

The only thing I can use is that userid and linktitle may NOT be reproduced 2 times (inserted) !!!

 

 

 

 

 

[cpd]

If you've got a dupe key on a UNIQUE field you'll receive an error.

 

Not sure where you've got this "some browsers reload twice" malarkey but that's incorrect unless you write your code to refresh the browser a second time. Are you sure you're not reloading the page somewhere or submitting the form twice by accident?

[PFMaBiSmAd]

@cpd, I can name at least 7 different reasons pages can get executed twice, the most frequently occurring reason is: Firefox with debugging plugins.

 

If you have eliminated your client-side, server-side, and any rewrite rules or how your site is hosed as the cause of the double page execution, the surest and simplest method to detected and prevent duplicate page requests in your server-side code is by setting a session variable that indicates the page has already correctly processed the request and then skip the processing for all requests after the first one.

[MichelDS]

I've put everything on one page now for testing... I got the same result. A double insert. I even got a session check and it is inserted just 1 time but saved 2 times in mysql db.

 

I got no debug add ons installed infirefox. I tried it with I.E. 8 and got the same result back, debug add on or other also nothing installed on I.E..

 

The code for the script above, below the database details.

 

Can someone find a clue ?

 

<?php
include('includes/dsn_start.php'); 

session_start();

if(isset($_SESSION['itel'])){
    $_SESSION['itel'] = $_SESSION['itel']+ 1;
}
else {
    $_SESSION['itel'] = 1;
}
echo "<br>session: ". $_SESSION['itel'];


if(isset($_GET['edit'])){
$edit = htmlspecialchars($_GET['edit']);
}
if(isset($_GET['act'])){
$act = htmlspecialchars($_GET['act']);
}	
if(isset($_GET['linkid'])){
$linkid = htmlspecialchars($_GET['linkid']);
}	
if(isset($_GET['linksubid'])){
$linksubid = htmlspecialchars($_GET['linksubid']);
}	
if(isset($_GET['userid'])){
$userid = htmlspecialchars($_GET['userid']);
	//echo "<br><br>xxxxxxx ". $edit ." xxxxx<br><br>";		
}	
if(isset($_POST['newML'])){ 
		$newML = htmlspecialchars($_POST['newML']);



function ShowSaveDelIntoDB($Opdracht,$action){
global $db_host;
global $db_gebruiker; 
global $db_wachtwoord;
global $db_naam;
$ResultShow ='';

$Verbinding = mysql_connect($db_host, $db_gebruiker, $db_wachtwoord);

#  Check of een verbinding is gelukt met de database 
if (!($verbinding = @ mysql_connect($db_host,$db_gebruiker,$db_wachtwoord))) 
{
trigger_error(mysql_error().'<br />connectie met mysql: '.$db_host);
   echo "Kan niet connecteren met database."; 
}
    else{
    	mysql_select_db($db_naam);	
    	#  Check of query is gelukt 
		if (($result_get_user = mysql_query($Opdracht)) === false)
		{ 
			 trigger_error(mysql_error().'<br />In query: '.$Opdracht);
			 echo "<br>FOUT in query !<br>";
		}
	else{
		# query is gelukt en wordt hier opgehaald
		if (isset($action) && $action == "Show"){
			return $ResultShow = mysql_query($Opdracht);
			}
		else{
			mysql_query($Opdracht);
			return mysql_insert_id();
			}
		}
mysql_close($Verbinding);	
	}
	}


function orderlink($somevalue){
if(!empty($somevalue)){
	$i = 1;
	$values ="";
	$value2 ="";
	foreach($somevalue as $value) {
		if($i == 1){ $values[$i] = $value;}
		else { $values[$i] = ",". $value;  }		
		$value2= $value2.$values[$i];
		$i++;
	}
	return $value2;
}
}


if(isset($_GET['act'])){
  
if($act == "upd"){

$orderlink = orderlink($_POST['orderL']);

		if($edit == "ML"){
			if($_SESSION['itel'] = 1){
				 $Opdracht = "INSERT INTO tbl_link(userid,linkcat,linksubid,linklang,linkactive,linktitle) 
				 VALUES('11',1,0,'1',1,'$newML') 				 
				 ON DUPLICATE KEY UPDATE linkid=LAST_INSERT_ID(linkid), linktitle= '$newML'";

				$newlinkid = ShowSaveDelIntoDB($Opdracht,""); //save in DB + give me back the new last inserted rowid		
			} // end if session					
		}
	}			
}
}	

session_destroy();
?>
<html>
<head>
</head> 
<body>
<form name="mainform" action="test.php?edit=ML&act=upd&linkid=1&linksubid=&userid=11" method="POST"><br>

<div id="contentLeft"><ul class="ui-sortable">
<br /><li id="recordsArray_4"><input type="hidden" name="orderL[]" value="2">Hoofd1</li>
<br /><li id="recordsArray_4"><input type="hidden" name="orderL[]" value="3">Hoodf2</li>
<br /><li id="recordsArray_4"><input type="hidden" name="orderL[]" value="4">Hoofd3</li>
</ul></div><input type="text" size="30" name="newML" />
<br /><br />
<input type="submit" value="Bewaar" >
</form>
</body>  </html> 

 

 

And this is the table in the database

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+01:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;


DROP TABLE IF EXISTS `tbl_link`;
CREATE TABLE IF NOT EXISTS `tbl_link` (
  `linkid` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT '0',
  `linkcat` tinyint(1) NOT NULL DEFAULT '0',
  `linksubid` tinyint(1) DEFAULT '0',
  `linksuborder` varchar(25) DEFAULT NULL,
  `linklang` tinyint(1) NOT NULL DEFAULT '0',
  `linkactive` tinyint(1) DEFAULT '0',
  `linktitle` varchar(50) NOT NULL,
  `articleid` int(11) DEFAULT '0',

  KEY `linkid` (`linkid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=72 ;



INSERT INTO `tbl_link` (`linkid`, `userid`, `linkcat`, `linksubid`, `linksuborder`, `linklang`, `linkactive`, `linktitle`, `articleid`) VALUES
(1, 11, 0, 0, '2,3,4', 1, 1, '', 0),
(2, 11, 1, 0, '7,8,9', 1, 1, 'Hoofd1', 0),
(3, 11, 1, 0, '10', 1, 1, 'Hoodf2', 0),
(4, 11, 1, 0, '', 1, 1, 'Hoofd3', 5),
(5, 11, 1, 0, '12,11', 2, 1, 'Tête1', 0),
(6, 11, 1, 0, '', 2, 1, 'Tête2', ,
(7, 11, 2, 2, '', 1, 1, 'Subnl1a', 1),
(8, 11, 2, 2, '', 1, 1, 'Subnl1b', 2),
(9, 11, 2, 2, '', 1, 1, 'Subnl1c', 3),
(10, 11, 2, 3, '', 1, 1, 'Subnl2a', 4),
(11, 11, 2, 5, '', 2, 1, 'Subfr1a', 6),
(12, 11, 2, 5, '', 2, 1, 'Subfr1b', 7),
(13, 11, 0, 0, '6,5', 2, 1, '', 0);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[PFMaBiSmAd]

Your session_destroy statement in that code is clearing all the session variables, so when the page is requested the second time, it starts over as though it was the first time it was requested. You also have one = sign (an assignment operator), instead of two == signs (an equal comparison operator) in the following statement: if($_SESSION['itel'] = 1){, which sets the session variable to 1, then tests if that expression is true, which it always will be. You need two == signs.

[MichelDS]

I got that one wrong but even with session_destroy removed double rows are inserted.

I have trouble with this one, I see the same answer all over the internet and I am eliminating each possibility with no results.

 

 

[MichelDS]

Allright, I got some trail to follow now...

 

A simple test gives me only 1 inserted row !!! 

 

$db_host = "localhost";
$db_gebruiker = "root";
$db_wachtwoord = '';
$db_naam = "projecteasywebsite";


$Opdracht = "INSERT INTO tbl_link(userid,linkcat,linksubid,linklang,linkactive,linktitle)  VALUES('11',1,0,'1',1,'DoubleInputTest5')"; 
$Conn = mysql_connect($db_host, $db_gebruiker, $db_wachtwoord);
mysql_select_db($db_naam);	
mysql_query($Opdracht);
mysql_close($Conn);	

 

 

The pages where I have the double inserts are setup like this :

--------------------------------------------------------------------------------

index.php :

 

included page :  $db_host en other DB data

&

included other page with script to follow

---------------------------------------------------------------------------------

 

 

-> on execute, new data is send back  to index.php  but with a paramater that says act=upd  (action = update)

      if($act == "show" ) { include 'menu_show.php';  }

if($act == "order") { include 'menu_order.php';  }

  if($act == "upd") { include 'menu_upd.php';  }

        ...

 

So the result is this :

 

index.php :

 

included page :  $db_host en other DB data

 

it chooses : if($act == "upd") { include 'menu_upd.php';  }

-> page menu_upd.php is included now where the new data will be saved in the DB.

 

 

 

QUESTION : can these includes be at the root of a double inserts in my case ?  

 

 

[PFMaBiSmAd]

Only if you are including the code twice, so that it is executed twice.

 

Here are the cases I have seen where pages are requested twice/code executed twice -

 

1) FF - debugging addons

2) FF - default page language encodeing set in the browser is different from the language encoding set in the page

3) IE - something to do with requesting the favicon.ico file causes it to request the page a second time

4) URL rewritting - something about either a missing or an actual trailing slash that causes a page to be requested twice

5) Some web hosting where requests are forwarded to servers behind a proxy

6) Javascript on a form that submits the form and the browser submits the form (the javascript does not return a false value, so the browser submits the form too.)

7) Server-side code that loops over/includes/calls code twice.

 

Your simple test code probably rules out 1, 3, and 5. Number 2, 4, 6, and 7 are still possibilities. You would need to post all the code needed to reproduce the problem for anyone here to directly be able to tell what the code is or is not doing that could cause the problem.

[Jessica]

I would think a quick way to check is find/replace all include with include_once and see if the problem stops.

[MichelDS]

I've done your suggestion and changed all the includes to include_once but nope, same result.

Because the test with a simple insert (only a few line on 1 page) gave me only one insert, I know that I have to search within the codes. At least now I know where to look. 

 

[MichelDS]

Ok, I had tested it out with a script on one page, no function at all. There was NO double insert. conclusion... there had to be an error in my codes.

In short, I found the error and it works fine now. I did a double query execution in the function while I thought it was just an error checking if the query was good or not.

Thanks for the help.