Jump to content


Photo

INSERT Query adding duplicate entries


  • Please log in to reply
6 replies to this topic

#1 djlotus

djlotus
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 25 March 2006 - 02:26 PM

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.



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>";
}


#2 annihilate

annihilate
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts
  • LocationUK

Posted 25 March 2006 - 05:49 PM

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.
SELECT name FROM members WHERE name = $new_username LIMIT 1;

Personal site: NewEnigma  |  Java and JSF Discussion

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 March 2006 - 04:42 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 djlotus

djlotus
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 26 March 2006 - 04:00 PM

[!--quoteo(post=358290:date=Mar 25 2006, 12:49 PM:name=annihilate)--][div class=\'quotetop\']QUOTE(annihilate @ Mar 25 2006, 12:49 PM) View Post[/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.
SELECT name FROM members WHERE name = $new_username LIMIT 1;
[/quote]

Where would you suggest inserting that string into the code?

#5 annihilate

annihilate
  • Members
  • PipPipPip
  • Advanced Member
  • 63 posts
  • LocationUK

Posted 26 March 2006 - 04:13 PM

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 :-)
Personal site: NewEnigma  |  Java and JSF Discussion

#6 djlotus

djlotus
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 26 March 2006 - 06:07 PM

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.

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 March 2006 - 08:44 PM

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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users