[zDevon]

Below you can see how I attempted to generate a list of the top 5 players by their score and send their names to be displayed in a textdraw. I'm not the most literate in SQL aside from basic queries and what I have below is just what I scrambled up after reading a few Stack Overflew things.

pawn Код:

new query[200],result[32];

format(query,sizeof(query),"SELECT * FROM users ORDER BY Score DESC LIMIT 0,1");
mysql_query(query);
mysql_store_result();
mysql_fetch_field_row(result, "AccountName");
mysql_free_result();
format(topplayer1,sizeof(topplayer1),result);
print(topplayer1);

format(query,sizeof(query),"SELECT * FROM users ORDER BY Score DESC LIMIT 1,1");
mysql_query(query);
mysql_store_result();
mysql_fetch_field_row(result, "AccountName");
mysql_free_result();
format(topplayer2,sizeof(topplayer2),result);
print(topplayer2);

//and so on

My print lines return "<null>" and that's it.

[Scenario]

First of all, you should be using threaded queries- you're severely limiting your performance with non-threaded queries. Secondly, your queries are being told to fetch everything from the users table so you're already going to be causing some strain on your MySQL server.

Let me give you a better example:

pawn Код:

mysql_function_query(connectionHandle, "SELECT `AccountName` FROM `users` ORDER BY `Score` DESC LIMIT 5", true, "thread_GetHighestScore", ""); // this would need to go somewhere where you want to load the highest scores (under a command or something)

forward thread_GetHighestScore();
public thread_GetHighestScore()
{
    new
        rows,
        fields;
    
    if(rows > 0)
    {
        new
            szUsername[MAX_PLAYER_NAME];
            
        for(new i = 0; i < rows; i++)
        {
            cache_get_row(i, 0, szUsername);
            // update your TD- szUsername is the username of the player
        }
    }
    return 1;
}

Obviously that's using threaded queries. I won't enable people to use non-threaded queries, so I won't give you the code for that. However, there's a better query for you to use if you prefer not to use threaded queries.

[zDevon]

Lovely reply, I just don't have the time right now to learn and then convert an entire script to threaded queries (I do realize the advantages of doing so however). I will try to adapt the query you gave me to work with what I have but I'm really not sure where to start.

[SuperViper]

pawn Код:

mysql_query("SELECT `AccountName` FROM `users` ORDER BY `Score` DESC LIMIT 5");
mysql_store_result();

while(mysql_retrieve_row())
{
    mysql_fetch_row(result);
    print(result);
}

mysql_free_result();

I haven't used MySQL R6 in a long time but this should work.

[zDevon]

Doesn't seem to. These are some sample accounts I set up to test the top 5:

• Player 1 - 154
• Player 2 - 188
• Player 3 - 51
• Player 4 - 11
• Player 5 - 233

Running the code you gave prints the names like this:

1. Player 2
2. Player 3
3. Player 3

[zDevon]

Bump

[SuperViper]

If you also need the score, use this:

pawn Код:

new playersScore;
mysql_query("SELECT `AccountName`, `Score` FROM `users` ORDER BY `Score` DESC LIMIT 5");
mysql_store_result();

while(mysql_retrieve_row())
{
    mysql_fetch_row(result);
    sscanf(result, "p<|>s[24]d", result, playersScore);
    
    printf("%s - %d", result, playersScore);
}

mysql_free_result();