prevent duplicate entries

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

Post Reply
bizkit1
Forum Newbie
Posts: 11
Joined: Wed Oct 05, 2011 5:20 am

prevent duplicate entries

Post 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!');
 }
mikeashfield
Forum Contributor
Posts: 159
Joined: Sat Oct 22, 2011 10:50 am

Re: prevent duplicate entries

Post 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!');
 }
bizkit1
Forum Newbie
Posts: 11
Joined: Wed Oct 05, 2011 5:20 am

Re: prevent duplicate entries

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: prevent duplicate entries

Post 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.
bizkit1
Forum Newbie
Posts: 11
Joined: Wed Oct 05, 2011 5:20 am

Re: prevent duplicate entries

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: prevent duplicate entries

Post 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  |
+----------+-----------+------+
bizkit1
Forum Newbie
Posts: 11
Joined: Wed Oct 05, 2011 5:20 am

Re: prevent duplicate entries

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: prevent duplicate entries

Post 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.
bizkit1
Forum Newbie
Posts: 11
Joined: Wed Oct 05, 2011 5:20 am

Re: prevent duplicate entries

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: prevent duplicate entries

Post by Celauran »

That's basically what you're doing now.
Post Reply