[lexzor]

hello, first of all i wish you a merry christmas !

i have a question.

how should i use primary key when i create a table and update it.

i mean i have this table

PHP Code:

new const g_szTablesInfo[][] =
{
    "(`hostname` VARCHAR(55) NOT NULL ,\
    `ipaddress` VARCHAR(22) NOT NULL ,\
    `onlineplayers` INT(3) NOT NULL ,\
    `onlineadmins` INT(3) NOT NULL ,\
    `currentmap` VARCHAR(55) NOT NULL ,\
    `nextmap` VARCHAR(55) NOT NULL ,\
    `maxplayers` INT(2) NOT NULL ,\
    `hide` INT(2) NOT NULL ,\
    PRIMARY KEY (`hostname`))"
}; 


and this is how i update it

PHP Code:

if(iResults == 0)
    {    
        formatex(szQuery, charsmax(szQuery), "INSERT INTO `%s` (`hostname`,`ipaddress`,`onlineplayers`,`onlineadmins`,`currentmap`,`nextmap`,`maxplayers`,`hide`) VALUES ('%s','%s','%i','%i','%s','%s','%i','0')",
        g_szTables[0], g_ServerData[HostName], g_ServerData[InternetAddress], g_ServerData[OnlinePlayers], g_ServerData[OnlineAdmins], g_ServerData[CurrentMap], g_ServerData[NextMap], g_ServerData[MaxPlayers]);        
        SQL_ThreadQuery(g_SqlTuple, "FreeHandle", szQuery);
    }
    else
    {
        formatex(szQuery, charsmax(szQuery), "UPDATE `%s` SET `hostname` = '%s' , `ipaddress` = '%s' , `onlineplayers` = '%i' , `onlineadmins` = '%i' , `currentmap` = '%s' , `nextmap` = '%s' , `maxplayers` = '%i'",
        g_szTables[0], g_ServerData[HostName], g_ServerData[InternetAddress], g_ServerData[OnlinePlayers], g_ServerData[OnlineAdmins], g_ServerData[CurrentMap], g_ServerData[NextMap], g_ServerData[MaxPlayers]);
        SQL_ThreadQuery(g_SqlTuple, "FreeHandle", szQuery);
    } 


but i get this error.

SQL ERROR: Duplicate entry 'server name' for key 'PRIMARY'

[Shadows Adi]

You have two same entries in your table for a Primary key.
A MySQL Primary key can't be in duplicate because it is unique. Delete one entry from your table and it should work.

[lexzor]

but why the plugin doesn t update that row?

PHP Code:

public update_server_info()
{
    get_server_info();
    new szQuery[512];
    formatex(szQuery, charsmax(szQuery), "SELECT * FROM `%s` WHERE `ipaddress` = '%s'", g_szTables[0], g_ServerData[InternetAddress]);
    SQL_ThreadQuery(g_SqlTuple, "UpdateServerInfo", szQuery);
}

public UpdateServerInfo(FailState, Handle:Query, szError[], ErrorCode, szData[], iSize)
{
    if(FailState || ErrorCode)
        server_print("^n[%s] SQL ERROR: %s^n", g_szPluginName, szError);
        
    new szQuery[512];    

    new iResults = SQL_NumResults(Query);

    if(iResults == 0)
    {    
        formatex(szQuery, charsmax(szQuery), "INSERT INTO `%s` (`hostname`,`ipaddress`,`onlineplayers`,`onlineadmins`,`currentmap`,`nextmap`,`maxplayers`,`hide`) VALUES ('%s','%s','%i','%i','%s','%s','%i','0')",
        g_szTables[0], g_ServerData[HostName], g_ServerData[InternetAddress], g_ServerData[OnlinePlayers], g_ServerData[OnlineAdmins], g_ServerData[CurrentMap], g_ServerData[NextMap], g_ServerData[MaxPlayers]);        
        SQL_ThreadQuery(g_SqlTuple, "FreeHandle", szQuery);
    }
    else
    {
        formatex(szQuery, charsmax(szQuery), "UPDATE `%s` SET `hostname` = '%s' , `ipaddress` = '%s' , `onlineplayers` = '%i' , `onlineadmins` = '%i' , `currentmap` = '%s' , `nextmap` = '%s' , `maxplayers` = '%i'",
        g_szTables[0], g_ServerData[HostName], g_ServerData[InternetAddress], g_ServerData[OnlinePlayers], g_ServerData[OnlineAdmins], g_ServerData[CurrentMap], g_ServerData[NextMap], g_ServerData[MaxPlayers]);
        SQL_ThreadQuery(g_SqlTuple, "FreeHandle", szQuery);
    }
    
} 


i just want to add it if doesn t exists and update some fields if exists.

[Shadows Adi]

Originally Posted by lexzor but why the plugin doesn t update that row?

Because mysql throws an error ( explained above ), as written in your script:

if(FailState || ErrorCode)
server_print("^n[%s] SQL ERROR: %s^n", g_szPluginName, szError);

[bigdaddy424]

You are updating the hostname therefore you must follow this example

formatex(szQuery, charsmax(szQuery), "UPDATE `%s` SET `ipaddress` = '%s' WHERE `hostname` = '%s';", str1, str2);

[lexzor]

Solved, thanks

[Natsheh]

Why on failstate you keep executing code, just stop and add return, its really annoying to see alot of coders do this mistake.