Keep duplicates OUT OF THE DB!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

davidprogramer
Forum Commoner
Posts: 64
Joined: Mon Nov 28, 2005 6:11 pm

Keep duplicates OUT OF THE DB!

Post by davidprogramer »

I hope I'm not wearing out my welcome here, but I have been stuck on this about 4 hours now. Could use some guidance. My problem is not that it won't put the player in the ladder, but how to keep him/her from registering over and over by simply refreshing the page that it pops up. Here is the code I have so far~ Thanks ahead of time for helping me out here :)

Code: Select all

<?php
if (!defined('MODULE_FILE')) {
   die ("You can't access this file directly...");
}
$index = 1;
require_once("mainfile.php");
$module_name = basename(dirname(__FILE__));
include("modules/League/includes/league_functions.php");

$user_id = $cookie[0];
$current_ladder = $_GET[lid];

$players_count = 0;
opentable();

$ladders = sql_query("SELECT ladder_id, ladder_name, ladder_directory, sql_db, sql_username, sql_pw, sql_prefix, pri_color, sec_color
						FROM ".$prefix."_league_ladders WHERE ladder_id = $current_ladder");
list($ladder_id, $ladder_name, $ladder_directory, $sql_db, $sql_username, $sql_pw, $sql_prefix, $pri_color, $sec_color) = sql_fetch_row($ladders);

connection($sql_db, $sql_username, $sql_pw);

$sql = 'SELECT * FROM `'.$sql_prefix.'_players`';
$result = mysql_query($sql) or die('SQL ['.$sql.'] caused: '. mysql_error()); 

//initialize variable as an array 
$players = array(); 
//assuming you have a unique id for each player 
//loop results 
while ($row = mysql_fetch_assoc($result)) { 
  //store player ids inside an array 
  $players[] = $row['player_id']; 
} 
$num_players = count($players);

$user_profile = sql_query("SELECT user_id, username, user_status, user_avatar, total_matches, total_points, user_honor, 
							user_disputes, user_regdate FROM ".$prefix."_users WHERE user_id = $user_id");
list($user_id, $username, $user_status, $user_avatar, $total_matches, $total_points, $user_honor, $user_disputes, $user_regdate) = sql_fetch_row($user_profile);

if ($db->sql_numrows($db->sql_query("SELECT username FROM ".$sql_prefix."_users WHERE username='$username'")) > 0) $stop = "<center>"You are already registered in this ladder."</center><br>";
if (!$stop) {
	$join_league = mysql_query("INSERT INTO ".$sql_prefix."_players 
	(`player_id` , `user_id` , `player_name` , `clan_id` , `player_rank`, `recieve_clan_invites`, `player_wins` , `player_losses` ,
	 `player_points` , `player_streak` , `player_avatar` , `clan_rank` , `permission_invite` , `permission_kick` ,
	 `career_wins` , `career_losses` , `career_games` , `career_points` , `career_streak` )
	 
	 VALUES ('', '$user_id', '$username', '', '$num_players', '', '', '', '', '', '', '', '', '', '', '', '', '', '');");
	echo "Thank-you $username, You have successfully joined $ladder_name !";
}
}else{
	echo "$stop";
}
include("modules/League/includes/update_ranks.php");
closetable();
?>
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

Simply make the player name field a unique field when creating the database:
http://dev.mysql.com/doc/refman/5.0/en/ ... table.html
A UNIQUE index is one in which all values in the index must be distinct. An error occurs if you try to add a new row with a key that matches an existing row.
davidprogramer
Forum Commoner
Posts: 64
Joined: Mon Nov 28, 2005 6:11 pm

Post by davidprogramer »

The solution to this problem makes me sick to my stomach. Thank you Roja. I am going to go bang my head on a wall now.
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

davidprogramer wrote:The solution to this problem makes me sick to my stomach. Thank you Roja. I am going to go bang my head on a wall now.
I hope not! If thats the response, I certainly won't help any more!

You are welcome. Sometimes its something very simple that we overlook. I could tell you a story about a 1500 line file and a single bracket...
davidprogramer
Forum Commoner
Posts: 64
Joined: Mon Nov 28, 2005 6:11 pm

Post by davidprogramer »

rofl!!

Well...The help on these forums...just shocking. One more q though. That did solve the problem, but the end-user wont know it. So how would I display an error message if the name is taken?? :wink:
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post by AGISB »

just check if that error occured. If so just display the message. Basically display the message on all errors occuring with the insert statement you don't need to be specific here ;)
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

it would be the same as

Code: Select all

$query = mysql_query($sql) or die(mysql_error());

//but instead do somtin like this

$query = mysql_query($sql)
if (!$query)
{
  echo 'Refreshing the page will not help you out, so dont do it b-funk.';
}
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post by foobar »

Roja wrote: I could tell you a story about a 1500 line file and a single bracket...
Tell me tell me tell me tell me tell me tell me! :mrgreen:

Why?

Because i wanna know i wanna know i wanna know i wanna know!!! :P
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

foobar wrote:
Roja wrote: I could tell you a story about a 1500 line file and a single bracket...
Tell me tell me tell me tell me tell me tell me! :mrgreen:

Why?

Because i wanna know i wanna know i wanna know i wanna know!!! :P
I think its self explanatory :?
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Post by Roja »

foobar wrote:
Roja wrote: I could tell you a story about a 1500 line file and a single bracket...
Tell me
This is the one story where I admit I didn't use the best tools for the job - so start with me admitting that.

There was a file of legacy code in BNT, that was 1500 lines, and the indention level was *one* space. As I tried to fix the indention level, I noticed that many of the if statements werent bracketted:

Code: Select all

if ($blah==0)
 dostuff;
And with that level of indention, it became very, very confusing.

So, I tried to reformat the whole file (yes, manually, groan) in an editor with no code highlighting.

I spent *six hours* redoing it a dozen times, each time causing errors. When I finally popped it into code highlighting, I found the underlying problem: A *single* bracket that I missed each, and every time.

Best tool for the job folks, always use the best tool for the job.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post by shiznatix »

Roja wrote:Best tool for the job folks, always use the best tool for the job.
a hammer and a calculator. if the numbers don't add up, BAM! start smashin stuff and it alllll works out
davidprogramer
Forum Commoner
Posts: 64
Joined: Mon Nov 28, 2005 6:11 pm

Post by davidprogramer »

lol. That sucks major! I wont lie though. I use dreamweaver and debugging is a snap. I spent about 6 hours on that update ranks script that Jcart fixed up in about 20 minutes. *sigh* I guess its a learning experience. Thanks shiznatix for the solution. I figured it was something easy like that.

edit:

The following didn't work. Did I do something wrong?

Code: Select all

$sql = 'SELECT * FROM `'.$sql_prefix.'_players`';
$result = mysql_query($sql) or die('SQL ['.$sql.'] caused: '. mysql_error()); 

if (!$result){ 
  echo 'Refreshing the page will not help you out, so dont do it b-funk.'; 
}
//initialize variable as an array 
$players = array();

Piece of cake. The irony of this is, I was talking to shiznatix on aim and couldn't figure it out. I had a few beers and it just hit me upside the head and worked first try. Odd.....

Code: Select all

$join_league = mysql_query("INSERT INTO ".$sql_prefix."_players 
(`player_id` , `user_id` , `player_name` , `clan_id` , `player_rank`, `recieve_clan_invites`, `player_wins` , `player_losses` ,
 `player_points` , `player_streak` , `player_avatar` , `clan_rank` , `permission_invite` , `permission_kick` ,
 `career_wins` , `career_losses` , `career_games` , `career_points` , `career_streak` )
 
 VALUES (DEFAULT, '$user_id', '$username', DEFAULT, '$num_players', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);");
	if (!$join_league)
	{
		echo 'Error: Username already exists in the ladder.'; 		
	}
	else
	{
		echo 'Thank-you' .$username.', You have successfully joined' .$ladder_name.' !';
	}
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post by foobar »

Roja wrote:
foobar wrote:
Roja wrote: I could tell you a story about a 1500 line file and a single bracket...
Tell me
This is the one story where I admit I didn't use the best tools for the job - so start with me admitting that.

There was a file of legacy code in BNT, that was 1500 lines, and the indention level was *one* space. As I tried to fix the indention level, I noticed that many of the if statements werent bracketted:

Code: Select all

if ($blah==0)
 dostuff;
And with that level of indention, it became very, very confusing.

So, I tried to reformat the whole file (yes, manually, groan) in an editor with no code highlighting.

I spent *six hours* redoing it a dozen times, each time causing errors. When I finally popped it into code highlighting, I found the underlying problem: A *single* bracket that I missed each, and every time.

Best tool for the job folks, always use the best tool for the job.
Lolz, if I may say so. That's one of the reasons I gave up on Notepad :lol: . Also, I love when it tells me what parameter a function takes. Bwiss!
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Why not just perform a check to see if the name has already been entered? You will need to do this ANYWAY to prevent new joins from using the same name.

Code: Select all

$sql ="'SELECT name FROM `" . $sql_prefix . "_players` where playername='$playername'";
$result = mysql_query($sql) or die('SQL ['.$sql.'] caused: '. mysql_error());
if(mysql_num_rows( $result ) != 0)
{
	echo "Sorry that name is already in use.<br>";
}
else
{
	// Do your normal stuff
}
The same thing can be used for anything else you don't want input into the database more than once.
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post by foobar »

AKA Panama Jack wrote:Why not just perform a check to see if the name has already been entered? You will need to do this ANYWAY to prevent new joins from using the same name.
Or you can just catch the mysql error when inserting a duplicate unique field.
Post Reply