Get playerName from Char ID from another SQL Table

[Strand]

Hello community,

I'm doing a ranking for BG and it is working, however, on the SQL table "char_bg" it is just showing char_id not "name" of the character, however, I want it to show the name of the character from another table based on the char_id of the table char_bg.

Can you guys help me?

 

set .@size, query_sql( "select char_id, rank_points, division from char_bg where rank_points > 0 order by rank_points desc limit "+ .top, .@name$, .@points, .@levelname$ );
	for (set .@c, 0; .@c < .@size; set .@c, .@c + 1)
	dispbottom "[ #" +(.@c + 1) +" ]      :      " + .@name$[.@c] +"      :      " + .@levelname$[.@c] +"      :      [ " + .@points[.@c] +" ]", 0xe60000;

 

[Zell]

https://pastebin.com/raw/GfnnpKTG

[Strand]

Hello @Zell,

Thank you so much for the help! I have another question. When I try to update a column in the char_bg table, which is "Division" and I save it it can be reflected on the ranking script. But when I use a command which is @addfame 2 100 to update rank_points, the division name of the character gets deleted like if I cannot update any column manually.

 

Edited September 15, 2019 by Strand

[Zell]

post the code of addfame. I don't use bg's. No idea how about the query.

[Strand]

Here it is: I do not why I can´t update the char_bg colums manually and have them stored.

/*==========================================
 * Fame Points
 *------------------------------------------*/
ACMD_FUNC(addfame)
{
    int fame = 0, type = 0;
    nullpo_retr(-1, sd);

    if( !message || !*message || sscanf(message, "%d %d", &type, &fame) < 2 )
    {
        clif_displaymessage(fd, "Usage: @addfame <type> <points>");
        clif_displaymessage(fd, "- type : 0 Class (Alche/BS/TK) | 1 PK | 2 BG Ranked | 3 BG Regular");

        return -1;
    }

    if( type < 0 || type > 3 )
    {
        clif_displaymessage(fd, "Invalid type : 0 Class (Alche/BS/TK) | 1 PK | 2 BG Ranked | 3 BG Regular");
        return -1;
    }

    pc_addfame(sd,fame,type);
    return 0;
}

 

Edited September 15, 2019 by Emistry
codebox

[Zell]

Well. Don't know if you are using some source modify or a source custom, but mine chmapif_parse_updfamelist just receive 3 types of rank. RANK_BLACKSMITH, RANK_ALCHEMIST and RANK_TAEKWON, prob yours is different.

Why not create a bindatcmd to make your query?

[Strand]

@Zell Yes, I did the bindatcmd but, want I am trying to say is this:

When I manually update the colum "Division" and type in "Diamond" I click on save and it gets actually saved, like this:

But, when I use the command @addfame 2 10 to add the character 10 more points, and then relog, the "Division" name for the character gets deleted.

Do you happen to know why does it happen and how to fix it?

[Zell]

Yes, the update query from your chmapif_parse_updfamelist  is wrong.

As I sayed, more easy to make a custom bindatcmd and make a right query by yourself

[Strand]

@Zell, I understand. Thanks for the help!

 

By the way, since that column cannot be updated and saved, I´m thinking of creating a new column "Division" but in a different table, but, my question is: How can I do a query including another table name to search for the character Division? How can I do that? Or anything that I can add to show the Division name from another table in the Ranking List shown in the beginning of this post?

Edited September 15, 2019 by Strand

[Emistry]

change 

set .@size, query_sql( "select char_id, rank_points, division from char_bg where rank_points > 0 order by rank_points desc limit "+ .top, .@name$, .@points, .@levelname$ );

into

set .@size, query_sql("SELECT c.`name`, cbg.`rank_points`, cbg.`division` FROM `char_bg` cbg INNER JOIN `char` c ON c.`char_id` = cbg.`char_id` WHERE cbg.`rank_points` > 0 ORDER BY cbg.`rank_points` DESC LIMIT "+ .top, .@name$, .@points, .@levelname$);

join the `char` table to retrieve the character name.

[Strand]

Hello @Emistry,

  Thanks a lot! By the way, in regards to my last question: Is there any way that I can query another table to retrieve the info from the "Division" column in the same sentence? Including the inner join to retrieve the character name.

[Zell]

48 minutes ago, Strand said:

Hello @Emistry,

  Thanks a lot! By the way, in regards to my last question: Is there any way that I can query another table to retrieve the info from the "Division" column in the same sentence? Including the inner join to retrieve the character name.

Yes. Just user another inner join

INNER JOIN your_division_table div ON div.division = bg.div

[Strand]

Hello @Zell,

I do not know how to use two INNER JOINs correctly. How would I add it considering the Emistry script?

The "Division" column is in the "char" table.

Thanks!

 

[Zell]

Just now, Strand said:

Hello @Zell,

I do not know how to use two INNER JOINs correctly. How would I add it considering the Emistry script?

The "Division" column is in the "char" table.

Thanks!

 

 

*coff* the scripts I posted first in pastebin it's the same query *coff*
 

Don't understand what you want. The division is only in char and not in the bg? Or in both?

[Strand]

The Division is only in "char" but the other information I want to retrieve which is "points" is in "char_bg".

[Zell]

After the first INNER JOIN (I'm using mine code as reference) add

INNER JOIN `char_bg` cb ON cb.char_id = c.char_id, so you will have something like this

 "   INNER JOIN `char`     c ON c.char_id  = bg.char_id " +
"   INNER JOIN `char_bg` cb ON cb.char_id = c.char_id  " +

And now to retrieve some information of char_bg is juts use cb.COLUMN

[Emistry]

21 minutes ago, Strand said:

The Division is only in "char" but the other information I want to retrieve which is "points" is in "char_bg".

set .@size, query_sql("SELECT c.`name`, cbg.`rank_points`, c.`division` FROM `char_bg` cbg INNER JOIN `char` c ON c.`char_id` = cbg.`char_id` WHERE cbg.`rank_points` > 0 ORDER BY cbg.`rank_points` DESC LIMIT "+ .top, .@name$, .@points, .@levelname$);