[hlstriker]

I need help selecting information from my database. First off let me explain what I am trying to do and how I am doing it...

I am trying to save the amount of kills players get with different weapons in their correct columns in the database. Then once they are saved, and people kill some more, the next save should be able to add to the previous saved data.

I have the following columns...
steamIds, knifeKills, gunKills, totalKills

And here is my first problem...
-I will have to select the current steam ids in the table to see if that player already has a row. If they don't have a row, make one then just save the data. If they do have a row, get the current knifeKills, gunKills, and totalKills and put them in variables. Once they are in variables then it will add the 2 variables together and re-save the data.

The problem I have with this is that I don't know how to find the current steam ids in the database, and check to see if those steam id needs updated.

Hopefully I explained this good enough that you can understand, explaining something that you are confused about doing is hard to do

[Deviance]

Originally Posted by hlstriker -I will have to select the current steam ids in the table to see if that player already has a row. If they don't have a row, make one then just save the data. If they do have a row, get the current knifeKills, gunKills, and totalKills and put them in variables. Once they are in variables then it will add the 2 variables together and re-save the data.

new pre_query[150]

public QueryHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
	// lots of error checking
	if(FailState == TQUERY_CONNECT_FAILED)
        	return set_fail_state("Could not connect to SQL database.")
	else if(FailState == TQUERY_QUERY_FAILED)
        	return set_fail_state("Query failed.")
   
	if(Errcode)
        	return log_amx("Error on query: %s",Error)
   
	return PLUGIN_CONTINUE
}

public SelectHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
	if(FailState == TQUERY_CONNECT_FAILED)
        	return set_fail_state("Could not connect to SQL database.")
	else if(FailState == TQUERY_QUERY_FAILED)
        	return set_fail_state("Query failed.")
   
	if(Errcode)
        	return log_amx("Error on query: %s",Error)
		
	new id = Data[0]
   
	if(SQL_NumResults(Query) > 0)
    	{
        	new temp_k = SQL_ReadResult(Query, 1)
        	new temp_g = SQL_ReadResult(Query, 2)        
        	new temp_t = SQL_ReadResult(Query, 3)
        
        	knifeKills[id] += temp_k
        	gunKills[id] += temp_g   
        	totalKills[id] += temp_t
		
		format(pre_query, 149, "UPDATE table SET knifeKills='%d', gunKills='%d', totalKills='%d' WHERE steamIds='%s'", knifeKills[id], gunKills[id], totalKills[id], steamid)
		SQL_ThreadQuery(g_SqlTuple, "QueryHandle", pre_query)
	}
	else
	{
        	format(pre_query, 149, "INSERT INTO table VALUES('%s','%d','%d','%d')", steamid, knifeKills[id], gunKills[id], totalKills[id])
        	SQL_ThreadQuery(g_SqlTuple, "QueryHandle", pre_query)
    	}  
	
	return PLUGIN_CONTINUE
}

public func(id)
{
	new steamid[38], data[1]
	get_user_authid(id, steamid, 37)
    
	format(pre_query, 149, "SELECT * FROM table WHERE steamIds='%s'", steamid)
	
	data[0] = id
	SQL_ThreadQuery(g_SqlTuple, "SelectHandle", pre_query, data, 1)
}

[hlstriker]

Thanks for the reply Deviance.

I have another question though. Isn't it better to use ThreadQuery instead of PrepareQuery?

Also, what is the difference? I thought ThreadQuery made it so the server didn't lag when it was processing, or does PrepareQuery do that too?

[Deviance]

Originally Posted by hlstriker I have another question though. Isn't it better to use ThreadQuery instead of PrepareQuery? Also, what is the difference? I thought ThreadQuery made it so the server didn't lag when it was processing, or does PrepareQuery do that too?

I lack in experience of SQLx, so i dont know. But anyway i red Hawk's SQLx tutorial and i updated the code to use ThreadQuery. But now i'm not sure at all if it will work...

[hlstriker]

I'm opening this thread back up because there is a problem that I would like to figure out.

Where deviance used "temp_id" to store the id to send it to the Handle function seems to be faulty. If one person does something to run the function, then another person does something to run the same function right after, it will use the second persons id for both queries.

If there is a better way to send a variable to the handle function please let me know. Thanks.

----[EDIT]-----

I just noticed that in the handle function...
public SelectHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)

There is Data[]. Would this be to get the variables somehow?

[Greenberet]

your select statement: "SELECT * FROM table WHERE steamIds='%s'" wil give you also the steamid into the result set, so you could search which player has this steamid on your server

I havnt played with data around yet, so i dont know how to use it. sry
but my solution above could be a workaround

[Minimum]

Here is an example using your idea. Not how I would do it but it gets the job done nevertheless.

/*
    Lets assume:
    dbc = Database Connection Variable
    table = Whatever table you're using.
*/

public select_query(id) {
    if(dbc == Empty_Handle) return false
    new authid[32], knifekills, gunkills, totalkills, output[64], query[256]
    get_user_authid(id,authid,31)
    new Handle:result
    result = SQL_PrepareQuery(dbc,"SELECT knifeKills,gunKills,totalKills FROM table WHERE steamIds='%s'",authid)
    SQL_Execute(result)
    if(SQL_NumResults(result) > 0) {
        // We have results returned.
        
        // This will return the field as a string.
        SQL_ReadResult(result,0,output,63)
        
        // This will return the field as a data.
        knifekills = SQL_ReadResult(result,0)
        gunkills = SQL_ReadResult(result,1)
        totalkills = SQL_ReadResult(result,2)
        
        // Then you would do your calculations, whatever here.
        
        // Preparing a Simple Query (We don't care about the results)
        formatex(query,255,"UPDATE table SET knifeKills = %i,gunKills = %i,totalKills = %i WHERE steamIds = '%s'",knifekills,gunkills,totalkills,authid)
        
    }
    else {
        // We have no rows returned.
        
        // Then you would do your calculations, whatever here.
        
        formatex(query,255,"INSERT INTO table (steamIds,knifeKills,gunKills,totalKills) VALUES ('%s',%i,%i,%i)",authid,knifekills,gunkills,totalkills)
    }
    SQL_SimpleQuery(dbc,query)
    SQL_FreeHandle(result)
    return true
}

Another way of doing it.

/*
    Lets assume:
    dbc = Database Connection Variable
    table = Whatever table you're using.
*/

public select_query(id) {
    if(dbc == Empty_Handle) return false
    new authid[32], knifekills, gunkills, totalkills, query[256]
    get_user_authid(id,authid,31)
    new Handle:result
    result = SQL_PrepareQuery(dbc,"SELECT count(*) FROM table WHERE steamIds='%s'",authid)
    SQL_Execute(result)
    if(SQL_ReadResult(result,0) > 0) {
        // We have rows returned.
        
        // No need for calculations now.
        
        // Preparing a Simple Query (We don't care about the results)
        formatex(query,255,"UPDATE table SET knifeKills=knifeKills+%i, gunKills=gunKills+%i, totalKills=totalKills+%i WHERE steamIds = '%s'",knifekills,gunkills,totalkills,authid)
    }
    else {
        // We have no results returned.
        
        // Then you would do your calculations, whatever here.
        
        formatex(query,255,"INSERT INTO table (steamIds,knifeKills,gunKills,totalKills) VALUES ('%s',%i,%i,%i)",authid,knifekills,gunkills,totalkills)
    }
    SQL_SimpleQuery(dbc,query)
    SQL_FreeHandle(result)
    return true
}

[hlstriker]

Thanks for the replies.

@Minimum:
I'm trying to use the threaded query not the prepare query. Threaded queries are better to use right?

Also I think I might be on to something but it doesn't seem to be working...
SQL_ThreadQuery ( Handle:cn_tuple, const handler[], const query[], const data[]=0 )

That data should be sent to the handler...
public QueryHandler(failstate, Handle:query, error[], errnum, data[], size)

Here is an example that doesn't return the data I sent.

PHP Code:

public function()
{
    new authid[33];
    get_user_authid(id, authid, 32);
    
    format(g_Cache, 511, "SELECT * FROM players WHERE steamid='%s'", authid);
    SQL_ThreadQuery(g_SqlTuple, "SelectHandle", g_Cache, "Test");
    
    return FMRES_HANDLED;
}

public SelectHandle(FailState, Handle:Query, Error[], Errcode, Data[], DataSize)
{
    if(FailState == TQUERY_CONNECT_FAILED)
            return set_fail_state("Could not connect to SQL database.");
    else if(FailState == TQUERY_QUERY_FAILED)
            return set_fail_state("Query failed.");
    
    if(Errcode)
        return log_amx("Error on query: %s", Error);
    
    // Shouldn't this display "Test" because it's the data that was sent?
    // Instead it displays a blank line.
    client_print(0, print_chat, "%s", Data);
    
    return PLUGIN_CONTINUE;
} 


[Deviance]

Updated my post.

[hlstriker]

Oh it's like the set_task sort of

Thank you so much this clears everything up !