SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Code PHP:
    	$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.
    Last edited by aor; Jan 11, 2011 at 10:53. Reason: incorrect summary

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,252
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    add the IGNORE option to your INSERT statement

    done and done

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,252
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    ok, how about using a START TRANSACTION with a COMMIT or ROLLBACK as necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    PHP Code:
    $sql "SELECT playerid, date, points FROM `$tbl_name` WHERE ";

    for(
    $i=1$i<=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 "<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

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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,252
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aor View Post
    Will this produce a meaningful error for the end user ?
    i think what you have is fine -- 'There was a problems saving the points'

    Quote Originally Posted by aor View Post
    is this approach overkill ?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    SitePoint Enthusiast
    Join Date
    Feb 2006
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code PHP:
    	$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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •