INSERT Query adding duplicate entries

[djlotus]

Let me preface this question by saying I am a complete novice with mysql. Below is a description of the problem. Any help would be greatly appreciated. I have also included the part of the script that I believe pertains to the adition of the new player into the DB. This is only a par to fthe page in whole.

I am running a gaming ladder script. When I enter a new player to a roster, It shows the new member only once on the roster. Upon refreshing the page, It shows the new entry twice. I checked the Db and found that it is submitting duplicate entries every time a new player is added to the DB.



[code]if (isset($add_new_player)){

$query_count = "SELECT COUNT(*) FROM members WHERE team_id='$team_id'" or die("Error in query_count");  
$result_count = mysql_db_query($db, $query_count, $dblink) or die("Error in result_count");
$max_count = mysql_result($result_count,0);

$query_size = "SELECT * FROM ladders WHERE ladder_id='$ladder_id'" or die("Error in query_size");
$result_size = mysql_db_query($db, $query_size, $dblink) or die("Error in result_size");
while($links = mysql_fetch_array($result_size)) {
$max_size = $links["max_size"];
}

if ($max_count >= $max_size) {
Echo "
<TABLE BORDER=0 BGCOLOR='$table_bg_color' CELLPADDING='2' CELLSPACING='$fancy_table_border' WIDTH='$manage_table_width'>
<TR BGCOLOR='$tr_bg_color'>
<TD><CENTER><font class=text>Only $max_size player(s) are allowed per team</CENTER></TD>
</TR>
</TABLE>
    
<TABLE BORDER=0 CELLPADDING='0' CELLSPACING='$fancy_table_border' WIDTH='$manage_table_width'>
<TR>
<TD HEIGHT=2></TD>
</TR>
</TABLE>";
return;
}

$query_new_player = "INSERT INTO members (ladder_id, team_id, name, last_modified, joined, modified, member_rank, accepted)
values('$ladder_id','$team_id','$new_username','Never','$modified_date','Never','Member','yes')";
$result_new_player  = @mysql_db_query($db, $query_new_player, $dblink) or die("Error in insert_new_player ");
Echo "
<TABLE BORDER=0 BGCOLOR='$table_bg_color' CELLPADDING='2' CELLSPACING='$fancy_table_border' WIDTH='$manage_table_width'>
<TR BGCOLOR='$tr_bg_color'>
<TD><CENTER><font class=text>$new_username was added on $modified_date</CENTER></TD>
</TR>
</TABLE>

<TABLE BORDER=0 CELLPADDING='0' CELLSPACING='$fancy_table_border' WIDTH='$manage_table_width'>
<TR>
<TD HEIGHT=2></TD>
</TR>
</TABLE>";
}
[/code]

[annihilate]

Refreshing the page straight after submission is resubmitting the data, so that's probably why it appears again. To prevent this, just query the database to see if the data being submitted is already there. If the username must be unique, then just query the username field. Limit the query to 1 row. If it finds 1 row, then don't insert, else no rows are found so do the insert.
[code]SELECT name FROM members WHERE name = $new_username LIMIT 1;[/code]

[fenway]

Two comments -- first, if it should be unique, then use a UNIQUE index and INSERT IGNORE. Second, if you want to prevent refeshing issues, simply issue a redirect first.

[djlotus]

[!--quoteo(post=358290:date=Mar 25 2006, 12:49 PM:name=annihilate)--][div class=\'quotetop\']QUOTE(annihilate @ Mar 25 2006, 12:49 PM) [snapback]358290[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Refreshing the page straight after submission is resubmitting the data, so that's probably why it appears again. To prevent this, just query the database to see if the data being submitted is already there. If the username must be unique, then just query the username field. Limit the query to 1 row. If it finds 1 row, then don't insert, else no rows are found so do the insert.
[code]SELECT name FROM members WHERE name = $new_username LIMIT 1;[/code]
[/quote]

Where would you suggest inserting that string into the code?

[annihilate]

It would go before you do the insert, and the insert would be done depending on the rows found. You need more code than just adding that one line in, but it shouldn't be too difficult to work out.

However, I wouldn't do my suggestion anyway, read Fenway's answer, which is much better than mine :-)

[djlotus]

Unfortunately, I have tried and failed miserably at understanding most MySQL query structures. Fenways answer makes sense, but I have no idea how to put it into code. I tried to grasp it. but to no avail.

[fenway]

Adding a UNIQUE KEY to the colum in question makes sense, correct? If so, the "IGNORE" keyword to INSERT simply silently ignores any errors that you might receive -- in this case, the fact that you'd have a duplicate key entry by trying to add the same username twice.

However, I assume that in PHP there's a redirect directive that allows you to load a given page during a script -- so after you insert, simply direct the user's browser there. That way, if they hit F5, they'll simply reload the page that you redirected them to, NOT the one that they POSTed from.

Make sense now?