[Abhinash]

I am using SQLite.
But when I am table data with 2 conditions the columns are getting added every time I start server.
Means everytime I am creating server the columns are getting repeated.

Codes ---

Load Section ---

PHP Code:

public Load_MySql(id)
{
    new szSteamId[32], szTemp[512]
    get_user_authid(id, szSteamId, charsmax(szSteamId))

    new Data[1]
    Data[0] = id

    //we will now select from the table `tutorial` where the steamid match
    format(szTemp,charsmax(szTemp),"SELECT * FROM `perfectzm` WHERE `steamid` = '%s' AND `name` = '%s'", szSteamId, g_playername[id])
    SQL_ThreadQuery(g_SqlTuple, "register_client", szTemp, Data, 1)
} 


Register client section ---

PHP Code:

public register_client(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
    {
        log_amx("Load - Could not connect to SQL database.  [%d] %s", Errcode, Error)
    }
    else if(FailState == TQUERY_QUERY_FAILED)
    {
        log_amx("Load Query failed. [%d] %s", Errcode, Error)
    }

    new id
    id = Data[0]

    if(SQL_NumResults(Query) < 1) 
    {
        //.if there are no results found
        
        new szSteamId[32]
        get_user_authid(id, szSteamId, charsmax(szSteamId)) // get user's steamid
        
        //  if its still pending we can't do anything with it
        if (equal(szSteamId,"ID_PENDING"))
            return PLUGIN_HANDLED
            
        new szTemp[512]
        
        // now we will insturt the values into our table.
        format(szTemp,charsmax(szTemp),"INSERT INTO `perfectzm` ( `steamid`, `name`, `points`, `kills`, `deaths`, `score`)VALUES ('%s', '%s', '0', '0', '0', '0');", szSteamId, g_playername[id])
        SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle", szTemp)
        g_totalplayers++
    } 
    else 
    {
        // if there are results found
        g_points[id] = SQL_ReadResult(Query, 2)
        g_kills[id] = SQL_ReadResult(Query, 3)
        g_deaths[id] = SQL_ReadResult(Query, 4)
        g_score[id] = SQL_ReadResult(Query, 5)

        set_dhudmessage(0, 255, 255, 0.03, 0.5, 2, 6.0, 10.0)
        show_dhudmessage(id, "You are now ranked!")
    }
    
    return PLUGIN_HANDLED
} 


MySQL Init section ---

PHP Code:

public MySql_Init()
{
    // Set Affinity to use SQLite instead of SQL
    SQL_SetAffinity("sqlite")

    // We tell the API that this is the information we want to connect to,
    // just not yet. basically it's like storing it in global variables
    g_SqlTuple = SQL_MakeDbTuple("", "", "", "ZombieQueen")
   
    // Ok, we're ready to connect
    new ErrorCode,Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(SqlConnection == Empty_Handle)
    {
        // stop the plugin with an error message
        set_fail_state(g_Error)
    }

    new Handle:Queries
    // we must now prepare some random queries
    Queries = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS perfectzm (steamid varchar(32), name varchar(32), points INT(11), kills INT(11), deaths INT(11), score INT(11))")

    if(!SQL_Execute(Queries))
    {
        // if there were any problems the plugin will set itself to bad load.
        SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        set_fail_state(g_Error)
    }
    
    // Free the querie
    SQL_FreeHandle(Queries)

    // you free everything with SQL_FreeHandle
    SQL_FreeHandle(SqlConnection)   
} 


Does anyone knows any solution ?

[Bugsy]

Are you saying a new row is being created on each connect, even when the player already has a row in the db?

Check that your query is correctly getting formatted:
"SELECT * FROM `perfectzm` WHERE `steamid` = '%s' AND `name` = '%s'", szSteamId, g_playername[id])"

I would avoid using a column name of 'name'.

[Abhinash]

Originally Posted by Bugsy Are you saying a new row is being created on each connect, even when the player already has a row in the db? Check that your query is correctly getting formatted: "SELECT * FROM `perfectzm` WHERE `steamid` = '%s' AND `name` = '%s'", szSteamId, g_playername[id])" I would avoid using a column name of 'name'.

Exactly.
I want to select data only if the steamid and name of the player matches, else don't.
But when doing this a new row is getting added on every connect and the old row is left behind and not updated.

[Natsheh]

I don't see a return when the query fails instead it will proceed to add more rows.

When creating the table make sure the steamid column is unique.

[Bugsy]

Good catch. I assume he's checked his logs. @Abhinash, do you see the below in logs?

"Load - Could not connect to SQL database. [%d] %s"
"Load Query failed. [%d] %s"

I recommend changing your CREATE to:

CREATE TABLE IF NOT EXISTS perfectzm (ID INT AUTO_INCREMENT PRIMARY KEY, steamid varchar(32) UNIQUE, name varchar(32), points INT(11), kills INT(11), deaths INT(11), score INT(11))

and then to INSERT or UPDATE, use this. You will never get a duplicate steam ID and it will always over-write the players existing data.

REPLACE INTO perfectzm (steamid, name, points, kills, deaths, score) VALUES ("STEAM:0:12345","bugsy",5,6,7,8)

[LondoN]

use UPDATE for updating client data

and for restoring best option for my is:

g_value[id] = SQL_ReadResult(Handle:Query, SQL_FieldNameToNum(Handle:Query, "name,points,steamid etc"));

[Natsheh]

I don't think that update is better than replace.

[LondoN]

Originally Posted by Natsheh I don't think that update is better than replace.

yeah, probably you're right, i know basic skills of sql.

They do the same thing in diffferent ways but the speed intex of sql statements differs with 0,00001 seconds probably.

It's a 22 year old game, i don't think the cpu/ram will increase with 20-30 percent if using replace instread of update.

Buy everyone use what it's like.

[Natsheh]

its not that, but its that UPDATE won't add a new row if the current data wasn't found it will just ignore in the other hand REPLACE will add a new row if the current data wasn't found, thats why i recommend using REPLACE.

[JocAnis]

I would suggest as Bugsy said first, rename column 'name' to smth else like 'nickname', and ofc put ';' at the end of every query to avoid any possible error/problem