Check userID is not in clanID

[karimali831]

Hi,

 

Can someone tell me the elseif code that disallows a user joining a team if their userID is already in clanID.

Hope this makes sense. Take a look at the attatchment, it shows when user (ID -> 325) joins a team (ID -> 187), it allows it multiple of times.  (two rows queried)

 

[attachment deleted by admin]

[sader]

$qr = mysql_query("SELECT id FROM clans WHERE clan_id=xxx AND user_id=yyy LIMIT 1");

if(mysql_num_rows($qr) === 0)

{

//insert user  into clans table

}

[Mchl]

Create UNIQUE index on (clan_id,user_id) columne.

[karimali831]

Okay, I'm not sure where to put this. Here is the code:

 

	$result2 = safe_query("SELECT * FROM ".PREFIX."cup_clan_members WHERE userID = '$userID' && clanID = '$clanID'");
	if(mysql_num_rows($result2)==3)
	echo '<center><b>You already are in 3 teams!</b><br>Please leave a team first.</center>';

==================
ADD HERE?
==================

else{
	$result = safe_query("SELECT * FROM ".PREFIX."cup_all_clans ORDER BY name ASC");		
	$clan .= '<option value="0" selected="selected"> - Please choose a Team - </option>';
	while($ds=mysql_fetch_array($result)) {
		$clan .= '<option value="'.$ds[iD].'">'.$ds[name].'</option>';
	}

 

Thanks for help.

[karimali831]

have I confused anyone? :'(

[jorley]

Are you trying to stop them from joining a clan if they are already a member of 3 clans and if they are a current member of the one show?

[karimali831]

Yes exackly. The code works if you try and join more than 3 clans, you get error but problem is you can join| the same clan 3 times as you can see from the attatchment. Please say you understand.

[jorley]

I think I need a little more info from you. Are there separate tables for each clan? separate member tables for each clan? Are the member ids grouped in a comma list or are they in separate rows?

 

[Mchl]

Create UNIQUE index on (clan_id,user_id) columns.

 

As simple as this. Whenever INSERT query fails with mysql_errno returning 1586, you know user tried to add himself to a clan more than once.

[karimali831]

Create UNIQUE index on (clan_id,user_id) columns.

 

As simple as this. Whenever INSERT query fails with mysql_errno returning 1586, you know user tried to add himself to a clan more than once.

 

Ok. Sorry about this but I have never done unique indexing. Perhaps you could tell me how?

[sader]

I think it's good idea to set unique index on user_id in this table since player can be joined in one clan, but you shouldn't (if I understand table structure correctly)set uniq ID on clan_id since one clan has several players so there gona be more then one row with same clan_id, or I'm totaly wrong?

[Mchl]

I think it's good idea to set unique index on user_id in this table since player can be joined in one clan, but you shouldn't (if I understand table structure correctly)set uniq ID on clan_id since one clan has several players so there gona be more then one row with same clan_id, or I'm totaly wrong?

 

That's why I advise setting up an index on TWO columns instead of one.

 

ALTER TABLE tableName ADD UNIQUE clan_id_user_id (clan_id,user_id)

[karimali831]

Ok, I set unique for userID and clanID now you get this error:

 

# Query failed: errorno=1062

# error=Duplicate entry '187-325' for key 2

# query=INSERT INTO ficocup_cup_clan_members (clanID, userID, function) VALUES ('187', '325', 'Member')

 

which is pretty good, but to those that don't understand this error can't the error be changed?

[Mchl]

Yes it can. Where you do your insert query

 

$sql = "INSERT INTO ficocup_cup_clan_members (clanID, userID, function) VALUES ('187', '325', 'Member')";
if(!$result = mysql_query($sql)) {
  if(mysql_errno() == 1062) {
     echo "You're already a member of this clan";
  }
}

[karimali831]

Ok, now I got :

 

$ergebnis2 = safe_query("SELECT * FROM ".PREFIX."cup_clan_members WHERE userID = '$userID' && clanID = '$clanID'");
	if(mysql_num_rows($ergebnis2)==3)
	echo '<center><b>You already are in 3 teams!</b><br>Please leave a team first.</center>';

                elseif(mysql_errno() == 1062) {
                echo "You're already a member of this clan";
}

else{

 

but still showing sql error and not echo?

[Mchl]

1. This is SELECTing part (which you no longer need). You need to use this code when INSERT is done.

 

2. Your safe_query function has some error catching in it. It's not standard php function, so I can't tell you much about it. Using mysql_query instead (in this one place) you should be fine.

[karimali831]

What's the difference? My CMS developer says it's better to use safe_query but I don't know the difference.

[Mchl]

I haven't seen this function, so I can't tell you if it is indeed safer. It comes with your CMS and is not part of PHP.

Even if it is safer, it seems it has it's own error catching logic built in, which will interfere with what we're trying to do here, so this one time you're forced to use mysql_query() (if you want to use the method presented here). As long as you take care to properly escape all values in the query, there is no additional risk.