SA-MP Forums Archive
MySQL SELECT Not getting last row - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL SELECT Not getting last row (/showthread.php?tid=613774)


MySQL SELECT Not getting last row - Shinja - 31.07.2016

This my code for command to show online/offline members
PHP код:
format(string, 256, "SELECT Name FROM Users WHERE Clan = %d", GetPlayerClan(playerid, g_SQL));
    mysql_query(g_SQL, string);
    for(new i=0; i<GetMaxMembers(GetPlayerClan(playerid, g_SQL), g_SQL); i++)
    {
        new name[25], s[48];
        cache_get_field_content(i, "Name", name);
        if(IsPlayerConnected(GetPlayerId(name)))
        {
            switch(i)
            {
                case 0..14:
                {
                    format(s, 24+24, "%s\t%s\t{00FF2F}Online\n", name, GetRankName(GetPlayerClan(playerid, g_SQL), GetPlayerRank(GetPlayerId(name), g_SQL), g_SQL));
                    strcat(members, s);
                }
                case 15..28:
                {
                    format(s, 24+24, "%s\t%s\t{00FF2F}Online\n", name, GetRankName(GetPlayerClan(playerid, g_SQL), GetPlayerRank(GetPlayerId(name), g_SQL), g_SQL));
                    strcat(members2, s);
                }
            }
        }
        else switch(i)
        {
            case 0..14:
            {
                format(s, 24+24, "%s\t%s\t{FF0000}Offline\n", name, GetRankName(GetPlayerClan(playerid, g_SQL), GetOfflineRank(name, g_SQL), g_SQL));
                strcat(members, s);
            }
            case 15..28:
            {
                format(s, 24+24, "%s\t%s\t{FF0000}Offline\n", name, GetRankName(GetPlayerClan(playerid, g_SQL), GetOfflineRank(name, g_SQL), g_SQL));
                strcat(members2, s);
            }
        }
    }
    ShowPlayerDialog(playerid, 13, DIALOG_STYLE_TABLIST, "Clan Members", members, "Next", "Cancel"); 
members2 is for The second dialog "Next", Everything is fine but it doesn't show the last member, example members are 17, it shows 14 in first page and 2 in second page, 1 missing

Any help would be appreciated, thanks!

Re: MySQL SELECT Not getting last row - Konstantinos - 31.07.2016

I am certain you execute more queries than needed to retrieve all the information. Another important thing to keep in mind is that using non-threaded queries and not removing the cache afterwards will cause memory leaks.

If more than 1 player use the same dialog, "members2" will be overwritten so the best way (at least for me) would be to save the cache in memory. In the dialog's response, if the player clicked on next page, set the cache and load the rest otherwise delete it.

Rows returned is the number of members so no need to keep calling GetMaxMembers over and over again. You can select rank along with "Name" in the query so you don't have to call GetPlayerRank and definitely using sscanf is much better than GetPlayerId.

Something last, variables are used to store temporary (as long as the player is online) their data so don't worry about using them. It is still better than keep calling queries to get the player's clan.

The above altogether (also assuming that a clan have have max. of 30 members) and modify it to your needs:
PHP код:
// global:
new Cache: gPlayer_ClanMembers[MAX_PLAYERS];
// OnPlayerConnect:
gPlayer_ClanMembers[playerid] = Cache: 0;
// wherever you execute the query
new Query[50];
mysql_format(g_SQL, Query, sizeof Query, "SELECT Name,Rank FROM Users WHERE Clan = %d", GetPlayerClan(playerid, g_SQL)); // better a variable that stores player's clan
mysql_tquery(g_SQL, Query, "OnClanMembersLoad", "i", playerid);
// the specified callback 
forward OnClanMembersLoad(playerid);
public OnClanMembersLoad(playerid)
{
    new rows = cache_get_row_count();
    if (!rows) return ... // error that there are no members
    else if (rows > 15)
    {
        gPlayer_ClanMembers[playerid] = cache_save();
        rows = 15;
        // load the first 15 results and store the rest in memory
    }
    new name[25], rank[25], s[48], targetid;
    members[0] = EOS; // reset
    for (new i; i != rows; i++)
    {
        cache_get_row(i, 0, name);
        cache_get_row(i, 1, rank);
        sscanf(name, "r", targetid);
        format(s, sizeof s, "%s\t%s\t%s\n", name, rank, targetid != INVALID_PLAYER_ID ? ("{00FF2F}Online") : ("{FF0000}Offline"));
        strcat(members, s);
    }
}
ShowPlayerDialog(playerid, 13, DIALOG_STYLE_TABLIST, "Clan Members", members, rows > 15 ? ("Next") : ("Close"), "Cancel");
// OnDialogResponse (dialogid 13):
if (!response)
{
    cache_delete(gPlayer_ClanMembers[playerid]);
    gPlayer_ClanMembers[playerid] = Cache: 0;
}
else
{
    if (cache_is_valid(gPlayer_ClanMembers[playerid]))
    {
        cache_set_active(gPlayer_ClanMembers[playerid]);
        new rows = cache_get_row_count(), name[25], rank[25], s[48], targetid;
        members[0] = EOS; // reset
        for (new i = 14; i != rows; i++) // 15 first (rowid 0-14) have been loaded and we want the rest
        {
            cache_get_row(i, 0, name);
            cache_get_row(i, 1, rank);
            sscanf(name, "r", targetid);
            format(s, sizeof s, "%s\t%s\t%s\n", name, rank, targetid != INVALID_PLAYER_ID ? ("{00FF2F}Online") : ("{FF0000}Offline"));
            strcat(members, s);
        }
        ShowPlayerDialog(playerid, 13, DIALOG_STYLE_TABLIST, "Clan Members", members, "Close", "Cancel");
        cache_set_active(Cache: 0);
        cache_delete(gPlayer_ClanMembers[playerid]);
        gPlayer_ClanMembers[playerid] = Cache: 0;
    }
} 


Re: MySQL SELECT Not getting last row - Shinja - 31.07.2016

Thanks alot for your time.
There is still 2 problems, its always showing "Close"
And, last member in first page appears first in the second page

Re: MySQL SELECT Not getting last row - Konstantinos - 31.07.2016

I set rows to 15 and save cache in memory but then I check if the rows are more than 15. Change:
pawn Код:
ShowPlayerDialog(playerid, 13, DIALOG_STYLE_TABLIST, "Clan Members", members, rows > 15 ? ("Next") : ("Close"), "Cancel");
to:
pawn Код:
ShowPlayerDialog(playerid, 13, DIALOG_STYLE_TABLIST, "Clan Members", members, cache_is_valid(gPlayer_ClanMembers[playerid]) ? ("Next") : ("Close"), "Cancel");
so it will give the "Next" option if we have saved anything (referring to cache).

---

I also commented that 15 first rows were shown that are row IDs 0 to 14 and started the iteration variable from 14 when it should be from 15. Change (from dialog response):
pawn Код:
for (new i = 14; i != rows; i++)
to:
pawn Код:
for (new i = 15; i != rows; i++)


Re: MySQL SELECT Not getting last row - Shinja - 31.07.2016

Quote:
Originally Posted by Konstantinos
Посмотреть сообщение
I also commented that 15 first rows were shown that are row IDs 0 to 14 and started the iteration variable from 14 when it should be from 15. Change (from dialog response):
pawn Код:
for (new i = 14; i != rows; i++)
to:
pawn Код:
for (new i = 15; i != rows; i++)
I already fixed this yeah
And there will be more pages thought untill 100 members maximum, i shall do same stuff?

EDIT: Close/Next fixed also Thanks alot

Re: MySQL SELECT Not getting last row - Konstantinos - 31.07.2016

Having a variable to determinate the page can make it possible without too many checks.

PHP код:
// global:
new Cache: gPlayer_ClanMembers[MAX_PLAYERS], gPlayer_ClanMembers@Page[MAX_PLAYERS];
// OnPlayerConnect:
gPlayer_ClanMembers[playerid] = Cache: 0;
gPlayer_ClanMembers@Page[playerid] = 0;
// wherever you execute the query
new Query[50];
mysql_format(g_SQL, Query, sizeof Query, "SELECT Name,Rank FROM Users WHERE Clan = %d", GetPlayerClan(playerid, g_SQL)); // better a variable that stores player's clan
mysql_tquery(g_SQL, Query, "OnClanMembersLoad", "i", playerid);
// the specified callback
forward OnClanMembersLoad(playerid);
public OnClanMembersLoad(playerid)
{
    new rows = cache_get_row_count();
    if (!rows) return ... // error that there are no members
    else if (rows > 15)
    {
        gPlayer_ClanMembers[playerid] = cache_save();
        rows = 15;
        gPlayer_ClanMembers@Page[playerid] = 1; // viewing 1st page
        // load the first 15 results and store the rest in memory
    }
    new name[25], rank[25], s[48], targetid;
    members[0] = EOS; // reset
    for (new i; i != rows; i++)
    {
        cache_get_row(i, 0, name);
        cache_get_row(i, 1, rank);
        sscanf(name, "r", targetid);
        format(s, sizeof s, "%s\t%s\t%s\n", name, rank, targetid != INVALID_PLAYER_ID ? ("{00FF2F}Online") : ("{FF0000}Offline"));
        strcat(members, s);
    }
}
ShowPlayerDialog(playerid, 13, DIALOG_STYLE_TABLIST, "Clan Members", members, cache_is_valid(gPlayer_ClanMembers[playerid]) ? ("Next") : ("Close"), "Cancel");
// OnDialogResponse (dialogid 13):
if (!response)
{
    cache_delete(gPlayer_ClanMembers[playerid]);
    gPlayer_ClanMembers[playerid] = Cache: 0;
    gPlayer_ClanMembers@Page[playerid] = 0;
}
else
{
    if (cache_is_valid(gPlayer_ClanMembers[playerid]))
    {
        cache_set_active(gPlayer_ClanMembers[playerid]);
        new rows = cache_get_row_count(), results = ++gPlayer_ClanMembers@Page[playerid] * 15;
        if (rows > results) // if there are more rows, set the max rows for the current page (goes like 15, 30, 45, 60 etc.)
        {
            rows = results;
        }
        else gPlayer_ClanMembers@Page[playerid] = 0; // otherwise this is the last page, reset so we can delete the cache afterwards
        new name[25], rank[25], s[48], targetid;
        members[0] = EOS; // reset
        for (new i = (results - 15); i != rows; i++) // 1 page = 15 results | page * 15 is the max rows
        {
            cache_get_row(i, 0, name);
            cache_get_row(i, 1, rank);
            sscanf(name, "r", targetid);
            format(s, sizeof s, "%s\t%s\t%s\n", name, rank, targetid != INVALID_PLAYER_ID ? ("{00FF2F}Online") : ("{FF0000}Offline"));
            strcat(members, s);
        }
        ShowPlayerDialog(playerid, 13, DIALOG_STYLE_TABLIST, "Clan Members", members, !gPlayer_ClanMembers@Page[playerid] ? ("Close") : ("Next"), "Cancel");
        if (!gPlayer_ClanMembers@Page[playerid])
        {
            cache_set_active(Cache: 0);
            cache_delete(gPlayer_ClanMembers[playerid]);
            gPlayer_ClanMembers[playerid] = Cache: 0;
        }
    }
}