Page 1 of 1

prevent duplicate entries

Posted: Thu Nov 10, 2011 5:46 am
by bizkit1
so i have 3 counter strike servers. I made a php form script which players use to register their name on one or all servers. My database table has "nickname" and "server_id" fields. Each nickname has his own server_id for each server. I want to prevent duplicate name registration for the same server. For example i want to register my nickname "bizkit1" on all 3 servers, but if someone else tries to register the same nick on one of the servers he would get an error that the nickname is already registered

now i have this code but if i try to add same nickname for another server it says that it already exists

Code: Select all

$nick = $_POST["nickname"];
$servernr = $_POST["addtoserver"];
$q = "SELECT * FROM amx_amxadmins, amx_admins_servers WHERE amx_amxadmins.nickname='$nick' AND amx_admins_servers.server_id='$servernr'";

$result = mysql_query($q);

if ( mysql_num_rows ( $result ) > 1 )
 {
 die('Nick name already exists for specified server!');
 }

Re: prevent duplicate entries

Posted: Thu Nov 10, 2011 6:12 am
by mikeashfield

Code: Select all

$nick = $_POST["nickname"];
$servernr = $_POST["addtoserver"];
$q = "SELECT * FROM amx_amxadmins, amx_admins_servers WHERE amx_amxadmins.nickname='$nick' AND amx_admins_servers.server_id='$servernr'";

$result = mysql_query($q);

// >1 would only return true if there were 2+ records.
if ( mysql_num_rows ( $result ) > 0 )
 {
 die('Nickname already exists for specified server!');
 }

Re: prevent duplicate entries

Posted: Thu Nov 10, 2011 2:37 pm
by bizkit1

Code: Select all

<?php
require('config.php');
require("inc/smtp.php");

$con = mysql_connect($adresa,$dbuser,$dbpass);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($numedb, $con);
$nick = $_POST["nickname"];
$password= $_POST["password"] ? md5($_POST["password"]) : '';
$servernr = $_POST["addtoserver"];

if ($servernr == 1) {
$svnum = "[Clasic]CS.XXX.RO:27015";
} elseif ($servernr == 2) {
$svnum = "[GunGame]CS.XXXU.RO:27018";
} elseif ($servernr == 11) {
$svnum = "[ZM Infection]CS.XXX.RO:27019";
} elseif ($servernr == 12) {
$svnum = "[ZM PLAGUE]WAR.XXX.RO:27015";
}




$q = "SELECT id FROM amx_amxadmins WHERE nickname='$nick'";
$result = mysql_query($q);
if ( mysql_num_rows ( $result ) > 0 )
 {
   $admin_id = mysql_result($result, 0, 'id');
   $query = "SELECT server_id FROM amx_admins_servers WHERE admin_id=$admin_id";
   $result = mysql_query($query);
   $server_id = mysql_result($result, 0, 'server_id');
   if ($server_id == $servernr)
	die("<div style='width:574px; background-color:#121212; color:#bec923; padding:10px;'>Name <b>$nick</b> already exists on <b>$svnum</b></div>");
 
}



$add_to_client_query = "insert into amx_amxadmins (nickname, email, password, access, flags, steamid, username, icq, ashow, created, expired, days) values ('$_POST[nickname]','$_POST[email]','$password','b','a','$_POST[nickname]','','','1',UNIX_TIMESTAMP(),'0','0')";
mysql_query($add_to_client_query);
$adminid=mysql_insert_id();
$add_to_booking_query = "insert into amx_admins_servers (admin_id, server_id, custom_flags, use_static_bantime) values ('$adminid','$_POST[addtoserver]','','yes')";
mysql_query($add_to_booking_query);


mysql_close($con)

?> 
this is my insert.php code. Now the problem is this:
for example i add player John only to server 1, after that i can`t add him again to server 1 because it tells me that he is already added. But if i add player the same john to server 2, after that i cand add duplicates of that name to server 1 and server 2 and i don`t know why

Re: prevent duplicate entries

Posted: Thu Nov 10, 2011 3:04 pm
by Celauran
If I'm understanding this right, each nickname is unique in amx_amxadmins. The admin_id associated with each nick can have multiple entries in amx_amxadmins_servers, each time with a different server ID.

Code: Select all

    $query = "SELECT server_id FROM amx_admins_servers WHERE admin_id=$admin_id";
    $result = mysql_query($query);
    $server_id = mysql_result($result, 0, 'server_id');
You're only looking at the first result. If the first entry is for server 1 and you're trying to add a new entry for server 2, it will allow it because 1 != 2.

You may do better with something like this:

Code: Select all

    $query = "SELECT COUNT(*) FROM amx_admins_servers WHERE admin_id = $admin_id AND server_id = $servernr";
    $result = mysql_query($query);
    list($count) = mysql_fetch_row($result);
    if ($count != 0)
        die("<div style='width:574px; background-color:#121212; color:#bec923; padding:10px;'>Name <b>$nick</b> already exists on <b>$svnum</b></div>");
Also, and I can't stress this enough, validate and escape user data before putting it in a query.

Re: prevent duplicate entries

Posted: Thu Nov 10, 2011 3:14 pm
by bizkit1
the id in amx_amxadmins is unique. There can be 4 users with the same name, but different ids and different server_id. Server id is in table amx_amxadmins_servers. I added your code, but the same problem, if i add the same player name on 2 or more servers, than i can add it again (duplicated). If i only add the player`s name on 1 server and then i try to add it again on the same server it says that it already exists.

Re: prevent duplicate entries

Posted: Thu Nov 10, 2011 3:32 pm
by Celauran
Usernames not being unique looks to me to be the problem, or the fact that you're using admin_id rather than nick in amx_amxadmins_servers. Consider the following:

Code: Select all

SELECT admin_id, nick FROM amx_amxadmins WHERE nick = 'Bob'
+----------+------+
| admin_id | nick |
+----------+------+
|        1 | Bob  |
+----------+------+
|        2 | Bob  |
+----------+------+

SELECT s.admin_id, s.server_id, a.nick FROM amx_amxadmins_servers AS s INNER JOIN amx_amxadmins AS a ON a.admin_id = s.admin_id
+----------+-----------+------+
| admin_id | server_id | nick |
+----------+-----------+------+
|        1 |         1 | Bob  |
+----------+-----------+------+
|        2 |         1 | Bob  |
+----------+-----------+------+

Re: prevent duplicate entries

Posted: Thu Nov 10, 2011 3:55 pm
by bizkit1
i`m kind of new to php and i don`t know many things. Can you please help me with the code? I just want that every time a player registers his name the script to check if that name is already registered to the selected server

Re: prevent duplicate entries

Posted: Thu Nov 10, 2011 3:57 pm
by Celauran
The code isn't the problem here, the database structure is. You could probably piece together some solution in PHP, but that's solving the wrong problem. I'd recommend either making nick unique in amx_amxadmins, or adding a nick column to amx_amxadmins_servers.

Re: prevent duplicate entries

Posted: Thu Nov 10, 2011 4:27 pm
by bizkit1
how about this: let`s say i registered my nick bizkit1 on server 1. If i want to register it on server 2 the script just duplicates my admin_id in amx_admins_servers and puts the corresponding server_id

Re: prevent duplicate entries

Posted: Thu Nov 10, 2011 4:35 pm
by Celauran
That's basically what you're doing now.