How do I NOT Insert any records if form values contain any duplicates

PHP
1

Hi,

I’ve had a look through the previous posts and cannot find any solution to a problem I am having when submitting Form values to a mySql database

I have a form which submits a playerid, date and points.

I have a unique index on the database table which says that playerid and date must be unique so a playerid can only have one entry per date. This works well when submitting a form but I’ve run into a problem where if i’m submitting say 10 values and a duplicate entry is made for value 5.

The first four values are inserted but the fifth is not and an error message is returned.

I want this to work differently in that I check all form values before I Insert and if ANY duplicates found NO inserts should executed.

What would be the most effecient way of checking in this case ?
Can I run one Select statement to check all values in Query String before I insert ?

My code is


	$sql = "INSERT into " .$tbl_name. " (points,date,playerid) 
		values (1000,".$date.",".$_GET['1']."),
		(750,".$date.",".$_GET['2']."),
		(600,".$date.",".$_GET['3']."),
		(500,".$date.",".$_GET['4']."),
		(450,".$date.",".$_GET['5']."),
		(400,".$date.",".$_GET['6']."),
		(300,".$date.",".$_GET['7']."),
		(200,".$date.",".$_GET['8']."),
		(100,".$date.",".$_GET['9']."),
		(50,".$date.",".$_GET['10'].")";
	
	mysql_select_db($db, $conn);

	if (mysql_query($sql,$conn))
		echo "<br/>Tournament Points Saved in Database";
	else
		die ('There was a problems saving the points');

thanks in advance for any advice.

2

add the IGNORE option to your INSERT statement

done and done

:cool:

3

Thanks for the quick reply r937 though maybe I havent explained myself properly…
From my understanding IGNORE does not enter duplicates but will not produce an error (and enter all other valid form values)
but in this I want the error displayed to the user…

What I am after is if there is a duplicate in any of the 10 values submitted via the form I want the entire submit to fail (no form values inserted into database) and for the user to go back and enter all again (preferably displaying the first duplicate which produced the error)

Say my form values are as follows

name points date
tom 500 20110111
mark 400 20110111
tom 350 20110111

Before I Insert I want to make sure that name and date is not duplicated, if it is I do not want to Insert ANY values and display an duplication error.

What is the best method of doing this ?

thanks again,

aor

4

ok, how about using a START TRANSACTION with a COMMIT or ROLLBACK as necessary

5

ok…i’ll just go and see how to work that out!
Can I just ask a few questions here…
I thought that PHP might be the way to handle this but do you reckon its best to do this on the database end ?
Will this produce a meaningful error for the end user ?
Its a pretty simple operation …is this approach overkill ?

thanks again

aor

6

Something like this will check for rows that prevent the insert and give more useful feedback. Databases are good for data integrity, but not necessarily UI messages.


$sql = "SELECT playerid, date, points FROM `$tbl_name` WHERE ";

for($i=1; $i&lt;=10; $i++) {
	$playerid = (int)$_GET[$i];
	$sql.= "(`date` = '$date' AND playerid=$playerid) OR ";
	
	//assumes $date is already set
}

//Trim trailing ' OR' from $sql
$sql = rtrim($sql, " OR ");

if($result = mysql_query($sql) AND mysql_num_rows($result)) {
	
	$error = "&lt;p&gt;Some players already have points entered for this date:&lt;/p&gt;&lt;ul&gt;";
	while($row = mysql_fetch_assoc($result)) {
		$error.= "&lt;li&gt;$row[playerid] ($row[points] points)&lt;/li&gt;";
	}
	echo $error . "&lt;/ul&gt;";
}

else {
	//Do  your update
}

You may want to join playerid to the table with their name so that the error message makes more sense.

7

i think what you have is fine – ‘There was a problems saving the points’

i don’t understand why you’d want to stop Mark’s data from getting in if you’ve messed up Tom’s data, but maybe that’s just me

8

Well if there is a single form where these records are being inserted in one conceptual action it could be confusing for the user to say “Mark’s points were entered, John’s points were entered, there was an error with Tom”. It could be missed. Internet users aren’t known for their attention to detail.

If aor has the time I’d recommend giving the most informative messages possible.

Or you could separate the valid/invalid entries and refill the form with only those that failed (so Mark’s data would get in).

9

Thanks for all the replies.
cranial is correct in saying that I have a page where the user will enter 10 results every night for a tournament so it is ‘one conceptual action’
I didnt want the situation where some points went through as it could get messy.

I also prob didnt fully realise that I need to check for duplicates in the Query String and also for duplicates already in database (I will use another version of the form to enter the players name so didnt want to the same player to be entered more than once)

For anyone else reading I first checked that the query string contained unique values and if it did i would send the user back to start again
I then implemented cranial-bore check for duplicates already in the database


	$getCount = count($_GET);
	$newArray = array_unique($_GET);
	$newArrayCount = count($newArray);
	if($newArrayCount < $getCount) {
		die('You have entered the same name more than once');
	}
	else {
		$isql = "SELECT playerid, date, points FROM $tbl_name WHERE ";

		for($i=1; $i<=10; $i++) {
			$playerid = (int)$newArray[$i];
			$isql.= "(date = '$date' AND playerid=$playerid) OR ";
		}
	
		//Trim trailing ' OR' from $sql
		$isql = rtrim($isql, " OR ");
		echo $isql.'<br/>';
		//$result = mysql_query($isql,$conn);
		//echo 'result is '.$result;
	
		if($result = mysql_query($isql) AND mysql_num_rows($result)) {
			$error = "<p>Some players already have points entered for this date:</p><ul>";
			while($row = mysql_fetch_assoc($result)) {
				$error.= "<li>$row[playerid] ($row[points] points)</li>";
			}
			echo $error . "</ul>";
		}
		else {
    		//Do  your update
	
			$sql = "INSERT into " .$tbl_name. " (points,date,playerid) 
				values (1000,".$date.",".$_GET['1']."),
				(750,".$date.",".$_GET['2']."),
				(600,".$date.",".$_GET['3']."),
				(500,".$date.",".$_GET['4']."),
				(450,".$date.",".$_GET['5']."),
				(400,".$date.",".$_GET['6']."),
				(300,".$date.",".$_GET['7']."),
				(200,".$date.",".$_GET['8']."),
				(100,".$date.",".$_GET['9']."),
				(50,".$date.",".$_GET['10'].")";
		}
	}

Thanks again r937 and cranial-bore

aor