Stopping multiple inserts in database

[jacko_162]

how do i  stop multiple duplications in database on my PHP script?

 

 

ok i have attached a screenshot of what the database looks like after a few runs of my script. the script is designed to pull api information, input into 1 database and update another user table.

i have made it run as a cron job every 60 minutes.

 

here is my code:

<?php

/* You need multiple instances of this script. Each instance runs once every hour so 6 instances means one 
runs every 10 mins. Remember to change the API URL to reflect the different accounts or characters.*/
include "connect.php";

$columns = "`date` , `refID`, `refType`, `ownerName1`, `ownerName2`, `argName1`, `amount`, `balance`, `reason`";

//Live URL is 

//Assumeing that they are only donating at this time and no one is being paid to reduce the balance. Balance reduction can be done in the prize claim script so its not API delayed.
if ( ($data[2] == "Player Donation") && ($data[4] == "Ship Lotto")){
$reUsed = mysql_query("SELECT * FROM bank WHERE refID='$data[1]';");
	if(!empty($reUsed)){
	$import="INSERT into bank($columns) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]')";
	mysql_query($import) or die(mysql_error());
	}

	/*check to see if the player has already been credited. It checks the last recorded reference #
	and checks to see if the new ref # is greater, else skips the processing. You need to check since 
	the API gives you the last 1000 journal entries or 1 week, what ever is shorter. Not just what is 
	new since last check. Check is performed by seeing if the record in the database for the user is 
	less then or equal to the new once. This works only because CCP's reference #s are auto increasing 
	so they only go up if they are newer, never down.*/
	$name = $data[3];
	//echo "Updating account of ".$name."<br />";
	$queryLastRef = mysql_query("SELECT lastRef FROM users WHERE username='$name';") or die(mysql_error());
	//echo $queryLastRef;
	$arraylastRef = mysql_fetch_assoc($queryLastRef);
	$lastRef = $arraylastRef["lastRef"];
	//echo "The last reference # was: ".$lastRef."<br />";
	$currentRef = $data[1];
	//echo "The current reference # is: ".$currentRef."<br />";
	if($lastRef<$currentRef){
		$amount = $data[6];
		//echo "Player deposited ISK in the amount of: ".$amount."<br />";
		$queryBal = mysql_query("SELECT user_iskbalance FROM users WHERE username='$name';") or die(mysql_error());
		//echo "Executing the SQL command to query balance ID#: ".$queryBal."<br />";
		$getBal = mysql_fetch_assoc($queryBal);
		//echo "Executing the SQL command to get balance amount: ".$getBal["user_iskbalance"]."<br />";
		$deposit = $amount+$getBal["user_iskbalance"];
		//echo "Depositing ISK in the ammount of: ".$deposit."<br />";
		$importBal= "UPDATE users SET user_iskbalance=$deposit WHERE username='$name';";
		//echo "Executing the SQL command to desposit: ".$importBal."<br />";
		mysql_query($importBal) or die(mysql_error());
		$importRefID= "UPDATE users SET lastRef='$currentRef' WHERE username='$name';";
		//echo "Executing the SQL command to set the new reference: ".$currentRef."<br />";
		mysql_query($importRefID) or die(mysql_error());
		//echo "Success!"."<br />";

		//For the sake of stats tracking update the total isk on deposit. The payout script will subtract.

		$queryiskDeposit = mysql_query("SELECT iskDeposit FROM stats;") or die(mysql_error());
		//echo "Executing the SQL command to query the ISK deposited : ".$queryiskDeposit."<br />";
		$arrayiskDeposit = mysql_fetch_assoc($queryiskDeposit);
		$getiskDeposit = $arrayiskDeposit["iskDeposit"];
		//echo "Got total isk on deposit of: ".$getiskDeposit."<br />";
		$iskDeposit = $getiskDeposit+$deposit;
		//echo "Inserting: ".$iskDeposit." ISK"."<br />";
		$importiskDeposit= "UPDATE stats SET iskDeposit='$iskDeposit';";
		//echo "Executing the SQL command to desposit: ".$importBal."<br />";
		mysql_query($importiskDeposit) or die(mysql_error());
		//echo "<br />";
		//echo "<br />";
		//echo "NEXT!<br />";
		//echo "<br />";
	}
	else{
		//echo "There is no update for ".$name." because ".$lastRef." is not less then or equal to ".$currentRef."<br />";
		//echo "<br />";
		//echo "<br />";
		//echo "NEXT!<br />";
		//echo "<br />";
	}
	//echo "DEBUG for ".$name." lastRef ".$lastRef." and currentRef ".$currentRef."<br />";

	//update the time that last update ran
	$today = date("Ymd G:i");
	mysql_query("UPDATE stats SET iskLastUpdate='$today';") or die(mysql_error());
	//echo "Updating Date to: ".$today;
	//echo "<br />";
	//echo "<br />";
	//echo "NEXT!<br />";
	//echo "<br />";
}
}
?>

 

can anyone help me stop it duplicating the entries in the database please?

[Adam]

Take a look at how to set-up 'unique constraints' on a column or across several:

 

http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html

[filnike]

hi, i think the column named "refID" have to be the Primary Key, but for any reason it's not configured in this way

 

so, try this:

 

"ALTER TABLE tablename ADD PRIMARY KEY (refID) NOT NULL;"

 

also, if you dont want to set a primary key (because maybe u already have one)

 

"ALTER TABLE tablename ADD UNIQUE(refID);"

 

maybe you have to truncate the table if you configured the colum "refID" allowing "null" values

 

sorry for my english, spanish

[jacko_162]

so a primary key cannot be duplicated twice on a table?

[filnike]

so a primary key cannot be duplicated twice on a table?

 

never!

[jacko_162]

ok i made the "refID" cant test script as the pull can only happen every 60 muinutes, so not sure on it yet. will making it primary key mess with the code or not?

[jacko_162]

problem solved. thanks guys