[JaKe Elite]

So I was trying to create a Also-Known-As system using MySQL, I am not quite sure if I should use a threaded or non-threaded query on this especially I am using them both for commands and OnPlayerConnect.

OnPlayerConnect

PHP Code:

    new Cache: result, count, rows;
    mysql_format(mysql, query, sizeof(query), "SELECT * FROM `accounts` WHERE `IP` = '%s'", PlayerInfo[playerid][p_newIP]);
    result = mysql_query(mysql, query);
    cache_get_row_count(rows);
    for(new i = 0; i < rows; i++) {
        new aka_ip[16];
        cache_get_value_name(i, "IP", aka_ip);
        if(IpMatch(aka_ip, PlayerInfo[playerid][p_newIP])) {
            count ++;
        }
    }
    if(count) {
        format(query, sizeof(query), "[Warning] "white"%s may have connected before to the server with a different name. (/aka %d)", GetName(playerid), playerid);
        foreach(Player, i) if(PlayerInfo[i][p_Admin] >= 1) {
            SendClientMessage(i, COLOR_RED, query);
        }
        printf("[Warning] %s may have connected before to the server with a different name. (/aka %d)", GetName(playerid), playerid);
    }
    cache_delete(result); 


/aka

PHP Code:

CMD:aka(playerid, params[])
{
    if(!PlayerInfo[playerid][p_Logged]) return SendClientMessage(playerid, 0xFFFFFFFF, ">> "red"You are not logged in.");
    if(PlayerInfo[playerid][p_Admin] < 1 && !IsPlayerAdmin(playerid)) return SendClientMessage(playerid, 0xFFFFFFFF, ">> "red"You are not an administrator to use this command.");
    new id, Cache: result, aka_names[5][MAX_PLAYER_NAME], count, rows, query[128], string[135];
    if(sscanf(params, "u", id)) return SendClientMessage(playerid, COLOR_RED, "Usage: "white"/aka [playerid]");
    if(id == INVALID_PLAYER_ID) return SendClientMessage(playerid, 0xFFFFFFFF, ">> "red"Player not connected.");
    if(PlayerInfo[playerid][p_Admin] < PlayerInfo[id][p_Admin]) return SendClientMessage(playerid, 0xFFFFFFFF, ">> "red"You cannot use this command on higher admin.");
    mysql_format(mysql, query, sizeof(query), "SELECT * FROM `accounts` WHERE `IP` = '%s'", PlayerInfo[id][p_newIP]);
    result = mysql_query(mysql, query);
    cache_get_row_count(rows);
    for(new i = 0; i < rows; i++) {
        new aka_ip[16];
        cache_get_value_name(i, "IP", aka_ip);
        if(IpMatch(aka_ip, PlayerInfo[id][p_newIP])) {
            if(count < sizeof(aka_names)) {
                cache_get_value_name(i, "NAME", aka_names[i]);
            }
            count ++;
        }
    }
    
    format(string, sizeof(string), ">> "green"%s is also known as... (IP: %s)", GetName(id), PlayerInfo[id][p_newIP]);
    SendClientMessage(playerid, COLOR_GREEN, string);
    format(string, sizeof(string), "%s", aka_names[0]);
    for (new i = 1, j = ((count > sizeof (aka_names)) ? (sizeof (aka_names)) : (count)); i < j; i++)
    {
        if (i == (j - 1))
        {
            if (count > sizeof (aka_names))
                format(string, sizeof(string), "%s, %s and %d more.", string, aka_names[i], (count - j));
            else
                format(string, sizeof(string), "%s and %s.", string, aka_names[i]);
        } else {
              format(string, sizeof(string), "%s, %s", string, aka_names[i]);
        }
    }
    
    if(!count) {
        format(string, sizeof(string), "This player has no other names used in the server.");
    }
    SendClientMessage(playerid, COLOR_GREY, string);
    cache_delete(result);
    return 1;
} 


Based off from Gammix's /aka, I tried doing a version of my own and so far it works great (performance-wise? *shrugs*)

[Calisthenics]

Comparing the IPs does not make sense as it fetches only records that match the IP to begin with. What you need is COUNT aggregate function which returns 1 row with the counter.

The data are not accessed too frequently so I would get rid of the array and execute the query each time. But you need to select only the name from the database and not everything.

The way it is done, it can only store 1 IP. Create a table to track the activity of the players.

[Logic_]

Use MySQL wildcards and here's how your code should look like:

You can use some wildcard magic to detect similar IPs.

PHP Code:

mysql_format(mysql, query, sizeof query, "SELECT * FROM `accounts` WHERE `IP` = '%s'", PlayerInfo[playerid][p_newIP]);
mysql_pquery(mysql, query, "CheckAKA", "i", playerid); 


PHP Code:

forward CheckAKA(playerid);
public CheckAKA(playerid) {

    new
        rows, ip[18];

    cache_get_row_count(rows);

    for (new i; i < rows; i++) {

        cache_get_value_name(i, "IP", ip);

        if (IpMatch(ip, PlayerInfo[playerid][p_newIP]))
            count ++;
    }

    if (count) {
        format(query, sizeof(query), "[Warning] "white"%s may have connected before to the server with a different name. (/aka %d)", GetName(playerid), playerid);
        
        foreach (new i : Player) {
            if (!PlayerInfo[i][p_Admin])
                continue;

            SendClientMessage(i, COLOR_RED, query);
        }
    }

    return 1;
}