Creating ranking system

shavit · **Author**

PHP Code:


			
void:UpdateRank(client)
{
    new String:auth[32];
    GetClientAuthString(client, auth, 32);
    
    new String:query[256];
    Format(query, 256, "SELECT * FROM playerpoints ORDER BY points DESC");
    
    SQL_TQuery(gH_SQLdb, UpdateRankCallback, query, client, DBPrio_High);
}

public UpdateRankCallback(Handle:owner, Handle:hndl, const String:error[], any:client)
{
    if(hndl == INVALID_HANDLE)
    {
        LogError("SQL Error on UpdateRank: %s", error);
        
        return;
    }
    
    new rank;
    
    while(SQL_HasResultSet(hndl) && SQL_FetchRow(hndl))
    {
        rank++;
    }
    
    gI_Cache_ClientRank[client] = rank;
    
    CloseHandle(hndl);
}

Seems like it doesn't work.

This is how the database looks like:
http://i.imgur.com/EDUE5Dz.png

It always returns the number of rows.

Bacardi · 03-22-2013 , 12:36 Re: Creating ranking system

Have you ever look examples from ...addons\sourcemod\scripting\testsuite\ ?
There is simple plugins source, sqltest.sp

shavit · 03-22-2013 , 12:53 Re: Creating ranking system

Quote:

Originally Posted by Bacardi

Have you ever look examples from ...addons\sourcemod\scripting\testsuite\ ?
There is simple plugins source, sqltest.sp

Haven't found anything related.

Bacardi · 03-22-2013 , 14:02 Re: Creating ranking system

ok, I assume you try get player rank position by counting all players above... ?
But you have not using player auth at all.

Anyway, could this way work ?
http://stackoverflow.com/questions/1...after-order-by

Could we make variable in query and let the mysql do the work ?
I go eat.

shavit · *Last edited by shavit; 03-22-2013 at 14:20.*

Quote:

Originally Posted by Bacardi

ok, I assume you try get player rank position by counting all players above... ?
But you have not using player auth at all.

Anyway, could this way work ?
http://stackoverflow.com/questions/1...after-order-by

Could we make variable in query and let the mysql do the work ?
I go eat.

PHP Code:


			
Format(query, 256, "SELECT `auth` FROM `playerpoints` ORDER BY `points` DESC");

Still doesn't work

thetwistedpanda · 03-22-2013 , 14:51 Re: Creating ranking system

You never limit the scope to the client auth. You always select all data. WHERE auth = 's'

shavit · *Last edited by shavit; 03-22-2013 at 16:19.*

Quote:

Originally Posted by thetwistedpanda

You never limit the scope to the client auth. You always select all data. WHERE auth = 's'

PHP Code:


			
Format(query, 256, "SELECT * FROM `playerpoints` WHERE auth = '%s' ORDER BY `points` DESC", auth);

Still doesn't work, I think that I don't understand SQL at all..

I mean, now it returns 1.

Bacardi · 03-22-2013 , 17:45 Re: Creating ranking system

I found something http://thinkdiff.net/mysql/how-to-ge...g-mysql-query/

- Query all auth, name, points etc. etc. SELECT *
- But if you want only rank position SELECT rank. And no, don't create column rank in playerpoints table

PHP Code:


			
SET @rownum = 0;

SELECT *
FROM
(
    SELECT @rownum := @rownum + 1 AS rank, auth, points, name
    FROM playerpoints ORDER BY points DESC
)
AS result WHERE auth = 'STEAM_0:0:54832373'

plugin code `rank`

*edit More more...
This version not need @variable. And same thing, not need create column rank

PHP Code:


			
SELECT COUNT(*) AS rank, auth, points, name
FROM
(
    SELECT points, auth, name, COUNT(*)
    FROM playerpoints
    GROUP BY points
)
AS result WHERE points >= ( SELECT points FROM playerpoints WHERE auth='STEAM_0:0:9999' )

plugin code `rank` 2

*third example

PHP Code:


			
SELECT(
SELECT COUNT(*)+1 FROM playerpoints AS b WHERE b.points > a.points
) AS rank , a.*
FROM playerpoints AS a
WHERE a.auth = 'STEAM_0:1:14141'

shavit · *Last edited by shavit; 03-22-2013 at 17:59.*

Quote:

Originally Posted by Bacardi

I found something http://thinkdiff.net/mysql/how-to-ge...g-mysql-query/

- Query all auth, name, points etc. etc. SELECT *
- But if you want only rank position SELECT rank. And no, don't create column rank in playerpoints table

PHP Code:


			
SET @rownum := 0;

SELECT *
FROM
(
    SELECT @rownum := @rownum + 1 AS rank, auth, points, name
    FROM playerpoints ORDER BY points DESC
)
AS result WHERE auth = 'STEAM_0:0:54832373'

Try with plugin

PHP Code:


			
Format(query, 300, "SET @rownum := 0;SELECT `rank`, `auth`, `points`, `name` FROM(SELECT @rownum := @rownum + 1 AS `rank`, `auth`, `points`, `name` FROM `playerpoints` ORDER BY `points` DESC)AS result WHERE `auth` = '%s' ", auth);

PHP Code:


			
void:UpdateRank(client)
{
    new String:auth[32];
    GetClientAuthString(client, auth, 32);
    
    new String:query[512];
    Format(query, 512, "SET @rownum := 0;SELECT `rank`, `auth`, `points`, `name` FROM(SELECT @rownum := @rownum + 1 AS `rank`, `auth`, `points`, `name` FROM `playerpoints` ORDER BY `points` DESC)AS result WHERE `auth` = '%s' ", auth);  
    
    SQL_TQuery(gH_SQLdb, UpdateRankCallback, query, client, DBPrio_High);
}

public UpdateRankCallback(Handle:owner, Handle:hndl, const String:error[], any:client)
{
    if(hndl == INVALID_HANDLE)
    {
        LogError("SQL Error on UpdateRank: %s", error);
        
        return;
    }
    
    new rank;
    
    while(SQL_HasResultSet(hndl) && SQL_FetchRow(hndl))
    {
        rank++;
    }
    
    gI_Cache_ClientRank[client] = rank;
    
    CloseHandle(hndl);
}

Gives me the result as 0.
Edit: Causes SQL error because rank doesn't exist, and you told me to not create it. I'm confused.

Bacardi · 03-22-2013 , 18:07 Re: Creating ranking system

*Here

I hate long topics... quote quote quote quote