MySQL showing total members in factions/online members.

MySQL showing total members in factions/online members. - 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 showing total members in factions/online members. (/showthread.php?tid=613745)

MySQL showing total members in factions/online members. - DavidLuango - 31.07.2016

Hello! I'm struggling on how I should approach this.

So basically my initial approach to get total members of a specific faction is simply loop through my rows and
keep adding 1 if `factionid` value appears same on different users. That way I can simply get total people with that one specific faction id. (I'm not entirely sure how I can do this in pawno if someone can help me)
Also, I'd like to get people who are "online" from that faction.
And when a user does /factions.

I have a MySQL table called `accounts` which has field `factionid`. So I'm trying to loop through all the faction ID and keep adding one if the value of `factionid` repeats. Faction ID value is integer.
For instance
John_White is in `factionID` = 12
Also
Ben_White is in `factionID` = 12
So that's two people in faction ID 12. Faction ID also represents `factionname` so when a user does /factions
I'd like to Print faction name and people online and total members in that one faction.

It shows them ex: "Faction Name" | "2/10"
2 is online users from that faction and 10 is out of total members.
I so far got /factions which returns me all the faction name in the database. Now I need to add the remaining which is showing total members in that faction and online users for that faction.

PHP код:



CMD:factions(playerid,params[])

{

    mysql_tquery(mysql, "SELECT * FROM `Factions` LIMIT "#MAX_FACTIONS"", "LoadFactionsIG", "i",playerid);

    mysql_tquery(mysql, "SELECT * FROM `accounts` LIMIT "#MAX_FACTIONS"", "LoadFactionsIG", "i",playerid);

    return 1;

}

forward LoadFactionsIG(playerid);

public LoadFactionsIG(playerid)

{

    if(playerid == INVALID_PLAYER_ID) return 1;

    new rows,fields,str[160];

    cache_get_data(rows,fields,mysql);

    if(!rows) return 1;

    new fracName[32];

    for (new i; i < rows; i++)

    {

        cache_get_field_content_int(i, "factionid");

        cache_get_field_content(i, "Name",fracName,mysql);

        format(str, sizeof(str), "%s%s  \n",str,fracName);

        

    }

    ShowPlayerDialog(playerid, 10, DIALOG_STYLE_MSGBOX, "Factions", str, "OK","");

    return 1;

}

Here is my MYSQL table structure.
This is my `faction` table.

This is my `accounts` table.

Also factionid and factionname they're both same for both tables `accounts` and `faction`

Re: MySQL showing total members in factions/online members. - Vince - 31.07.2016

You'll want to use "group by". Bascially what you want to do is this:

PHP код:



SELECT factionid, COUNT(factionid) AS memberCount FROM Accounts GROUP BY factionid

That will then produce a list of factionids and the number of rows for each. To add the name you need to join the other table. I'm not sure if the query below is entirely correct because I don't have a database to test it with.

PHP код:



SELECT a.factionid, f.Name, COUNT(a.factionid) AS memberCount FROM Accounts a INNER JOIN Faction f ON f.ID = a.factionid GROUP BY a.factionid, f.Name

Re: MySQL showing total members in factions/online members. - DavidLuango - 31.07.2016

Quote:

Originally Posted by Vince

You'll want to use "group by". Bascially what you want to do is this:

PHP код:



SELECT factionid, COUNT(factionid) AS memberCount FROM Accounts GROUP BY factionid

PHP код:



SELECT a.factionid, f.Name, COUNT(a.factionid) AS memberCount FROM Accounts a INNER JOIN Faction f ON f.ID = a.factionid GROUP BY a.factionid, f.Name

Do I not need to use CallBack for that function mate? Because my inital goal is to loop through `factionid` in pawno. Similarly how I used CallBackFunction for my command /faction, which looked over at LoadFactionIG. Then I simply initiated and defined my rows and fields then I looped through. Could you please show me how to do it in python where, I can simply ShowPlayerDialog with respective faction name and total faction members on that one faction. I'm not too sure about pawno syntax where I could simply add one everyime I see the factionid being repeated under my `accounts` table field.

Re: MySQL showing total members in factions/online members. - DavidLuango - 31.07.2016

bump!

Re: MySQL showing total members in factions/online members. - Stinged - 31.07.2016

Use this:

Quote:

Originally Posted by Vince

PHP код:



SELECT a.factionid, f.Name, COUNT(a.factionid) AS memberCount FROM Accounts a INNER JOIN Faction f ON f.ID = a.factionid GROUP BY a.factionid, f.Name

@Vince, I tried it on a database and it did work. It's amazing how powerful the SQLanguage is.

And then inside of the callback, use:

Код:

cache_get_field_content_int(row, "memberCount", handle);
or
cache_get_row_int(row, 2, handle);

The second one is faster, but the number 2 changes depending on how many fields you're fetching.
Both of them will return the member count.

Also, don't bump before 24 hours.
The rules are in every section of the forum for a reason.

Re: MySQL showing total members in factions/online members. - DavidLuango - 31.07.2016

Quote:

Originally Posted by Stinged

Use this:

@Vince, I tried it on a database and it did work. It's amazing how powerful the SQLanguage is.

And then inside of the callback, use:

Код:

cache_get_field_content_int(row, "memberCount", handle);
or
cache_get_row_int(row, 2, handle);

I'm still a little lost on how I can I use CallBack. I'm trying to do everything under my LoadFactionsIG(playerid) function. Since my inital goal is to simply display player all the faction name on datbaase and total faction members.
Apologies on using the bump, I meant to do that on my first post. Sorry.

PHP код:



CMD:factions(playerid,params[])

{


    mysql_tquery(mysql, "SELECT * FROM `Factions` LIMIT "#MAX_FACTIONS"", "LoadFactionsIG", "i",playerid);

    return 1;

}

forward LoadFactionsIG(playerid);

public LoadFactionsIG(playerid)

{

    if(playerid == INVALID_PLAYER_ID) return 1;

    new rows,fields,str[160];

    cache_get_data(rows,fields,mysql);

    if(!rows) return 1;

    new fracName[32];

    for (new i; i < rows; i++)

    {

        cache_get_field_content_int(i, "factionid");

        cache_get_field_content(i, "Name",fracName,mysql);

        format(str, sizeof(str), "%s%s  \n",str,fracName);

        

    }

    ShowPlayerDialog(playerid, 10, DIALOG_STYLE_MSGBOX, "Factions", str, "OK","");

    return 1;

}

I'm not entirely sure how I can use that query and use CallBack LoadFactions then add the players on the faction. If you don't mind showing me how I could do it.

Re: MySQL showing total members in factions/online members. - Stinged - 31.07.2016

There is nothing in your script that gives us information about the 'total members'.
So I just added the currently online members. Should work.

Код:

CMD:factions(playerid,params[]) 
{
    mysql_tquery(mysql, "SELECT a.factionid, f.Name, COUNT(a.factionid) AS memberCount FROM `Accounts` a INNER JOIN `Faction` f ON f.ID = a.factionid GROUP BY a.factionid, f.Name", "LoadFactionsIG", "i", playerid);
    return 1; 
}

forward LoadFactionsIG(playerid); 
public LoadFactionsIG(playerid) 
{ 
    if(!IsPlayerConnected(playerid))
            return 1;

    new rows = cache_get_row_count(mysql);
    if(!rows)
        return 1; 

    new fName[32], dString[MAX_FACTIONS * (32+6) +1]; 
    for (new i; i < rows; i++) 
    { 
        cache_get_field_content(i, "Name", fName, mysql); 
        format(dString, sizeof(dString), "%s%s\t%i\n", dString, fName, cache_get_field_content_int(i, "memberCount", mysql));   
    }
    ShowPlayerDialog(playerid, 10, DIALOG_STYLE_MSGBOX, "Factions", dString, "OK",""); 
    return 1; 
}

Re: MySQL showing total members in factions/online members. - DavidLuango - 31.07.2016

The code you provided /factions does nothing.

However this code here. Gives me all the faction names on my database. Similarly I just want to add online and total faction members here. The reason why you don't see anything with total members is because I haven't scripted it. I have no idea how to do that. If you don't mind could you make it compatiable with what I have right now.
I just want to show all the factions with current members on out of total faction members.
Below code simply displays you a dialog with all the faction name. Now I just want to add total online members and total faction members.

PHP код:



CMD:factions(playerid,params[])

{


    mysql_tquery(mysql, "SELECT * FROM `Factions` LIMIT "#MAX_FACTIONS"", "LoadFactionsIG", "i",playerid);

    return 1;

}

forward LoadFactionsIG(playerid);

public LoadFactionsIG(playerid)

{

    if(playerid == INVALID_PLAYER_ID) return 1;

    new rows,fields,str[160];

    cache_get_data(rows,fields,mysql);

    if(!rows) return 1;

    new fracName[32];

    for (new i; i < rows; i++)

    {

        cache_get_field_content_int(i, "factionid");

        cache_get_field_content(i, "Name",fracName,mysql);

        format(str, sizeof(str), "%s%s  \n",str,fracName);


    }

    ShowPlayerDialog(playerid, 10, DIALOG_STYLE_MSGBOX, "Factions", str, "OK","");

    return 1;

}

The way I was thinking to get total members of that one specific faction was simply go to table `accounts` then loop through`factionid` column. Everytime a factionid is repeated simply add one everytime which should give me the total addition of factionid mentioned. Then simply tie that back to my faction name. That way I can tie faction name and total faction members.

Also my `Factions` table looks something like this

"ID" refers to faction ID.
And my `accounts` table looks something like this

"factionid" refers to Faction ID.

The table `Factions` are all the factions created in the server. The faction ID is setup using A_I.
As soon as I do /createfaction the user who created faction I update their `accounts` and put their `factionid` as same as the `Factions` ID.

Re: MySQL showing total members in factions/online members. - Stinged - 31.07.2016

Try this:

PHP код:



CMD:factions(playerid,params[]) 
{
    mysql_tquery(mysql, "SELECT a.factionid, f.Name, COUNT(a.factionid) AS memberCount FROM `Accounts` a INNER JOIN `Faction` f ON f.ID = a.factionid GROUP BY a.factionid, f.Name", "LoadFactionsIG", "i", playerid);
    return 1; 
}

forward LoadFactionsIG(playerid); 
public LoadFactionsIG(playerid) 
{ 
    if(!IsPlayerConnected(playerid))
            return 1;

    new rows = cache_get_row_count(mysql);
    if(!rows)
        return 1; 

    new fName[32] fID, dString[MAX_FACTIONS * (32+6) +1], count, id, pool_size = GetPlayerPoolSize();
    for (new i; i < rows; i++) 
    {
        count = 0;
        cache_get_field_content(i, "Name", fName, mysql);
        fID = cache_get_field_content_int(i, "factionid", mysql);
        for (id = 0; id <= pool_size; id++)
        {
            if (PlayerInfo[id][Faction] == fID) // Replace PlayerInfo with your enum
                count++;
        }
        format(dString, sizeof(dString), "%s%s\t%i/%i\n", dString, fName, count, cache_get_field_content_int(i, "memberCount", mysql));   
    }
    ShowPlayerDialog(playerid, 10, DIALOG_STYLE_MSGBOX, "Factions", dString, "OK",""); 
    return 1; 
}

Re: MySQL showing total members in factions/online members. - DavidLuango - 31.07.2016

Still. the command "/factions" doesn't do anything. Maybe the query name is incorrect? I can provide you my enums aswell.

PHP код:



enum PlayerData

{

    ID,

    Name[MAX_PLAYER_NAME],

    Password[129],

    IP[16],

    Admin,

    VIP,

    FactionID,

    FactionName[200],

    FactionRank[32],

    Money,

    Float:posX,

    Float:posY,

    Float:posZ,

    Float:posA

};

new Player[MAX_PLAYERS][PlayerData];

PHP код:



enum faction // Enums, way better then using 10 variables per thing needed for faction system.

{

    ID, // Faction ID

    Name[32], // Name of the faction

    Type, // What kind of faction? Criminal, law, etc

    Rank1[32], // Rank Name

    Rank2[32], // Rank Name

    Rank3[32], // Rank Name

    Rank4[32], // Rank Name

    Rank5[32] // Rank Name

};