Script SQL

[Kakaroto]

Hello good evening, someone could please help me with this script? I can not upgrade it to use the new rathena ... I am getting the following alerts in map_server:

[SQL]: DB error - You have an error in your SQL syntax; check the manual that co
rresponds to your MySQL server version for the right syntax to use near '' at li
ne 1
[Debug]: at ..\src\map\script.c:16961 - SELECT name FROM `char` WHERE char_id =
0 OR char_id = 0 OR char_id = 0 OR char_id = 0 OR char_id = 0 OR
[Debug]: Source (NPC): BG Ranker at new_1-1 (51,109)

script:

new_1-1,51,109,5    script    BG Ranker    55,{

    mes .name$;
    mes "I can give you the Battlegrounds Raking Information.";
    mes "Choose what do you want to know.";
    next;
    
    set .@selection, select( implode( .menu$, ":" ) ) - 1;
    
    query_sql "SELECT char_id, "+ .tables$[.@selection] +" FROM char_bg ORDER BY "+ .tables$[.@selection] +" DESC LIMIT "+ .maxsize , @char_id, @bgtop;
    
    for ( .@a = 0; .@a < .maxsize; .@a++ )
        set .@fetch_name$, .@fetch_name$ + "char_id = "+ @char_id[.@a]+ " OR ";
    
    query_sql "SELECT name FROM `char` WHERE "+ .@fetch_name$, @char_name;
    
    mes .name$;
    mes .menu$[.@selection];
    for ( .@a = 0; .@a < .maxsize; .@a++ )
        mes "["+ (.@a+1) +"] "+ ( (@char_name) ? @char_name : "^FF0000None^000000" );
    close;
    
    OnInit:
        set .name$,        "[^0000FFBattlegrounds Ranker^000000]";
        
        setarray .menu$[0],        "Top Game Win",
                                "Top Game Tie",
                                "Top Game Lost",
                                "Top Leader Game Win",
                                "Top Leader Game Tie",
                                "Top Leader Game Lost";
        
        setarray .tables$[0],    "win",
                                "tie",
                                "lost",
                                "leader_win",
                                "leader_tie",
                                "leader_lost";
                                
        set .maxsize,    5;        //Amount of player that will appear
        end;
        
}

Thank you in advance anyone who can help me.

[Emistry]

it's caused by this.

    for ( .@a = 0; .@a < .maxsize; .@a++ )
        set .@fetch_name$, .@fetch_name$ + "char_id = "+ @char_id[.@a]+ " OR ";

the word "OR" is appended on the end of the string.

 

 

you can fix it by

create a new query that consist another sub query inside the SQL statement.

example

SELECT `a`,`b`,`c` FROM `tables1` WHERE `char_id` IN ( SELECT `char_id` FROM `char_bg` )

or

change the way to populate the string, example:

	.@fetch_name$ = "char_id = "+@char_id[0];
	for ( .@a = 1; .@a < .maxsize; .@a++ )
		set .@fetch_name$, .@fetch_name$ + " OR " + " char_id = "+ @char_id[.@a];

anyway for your case that required to check more than one variables, it's better if you just use the IN Operator.

[Capuche]

query_sql "SELECT name FROM `char` WHERE "+ .@fetch_name$, @char_name;

If you replace the content of .@fetch_name$ you will find out what's wrong

[Kakaroto]

query_sql "SELECT name FROM `char` WHERE "+ .@fetch_name$, @char_name;

If you replace the content of .@fetch_name$ you will find out what's wrong

 

 

I tried but it did not really work '-'

Edited February 25, 2016 by Kakaroto

[Capuche]

    for ( .@a = 0; .@a < .maxsize; .@a++ )
        set .@fetch_name$, .@fetch_name$ + "char_id = "+ @char_id[.@a]+ " OR ";
    
    query_sql "SELECT name FROM `char` WHERE "+ .@fetch_name$, @char_name;

>>

query_sql "SELECT name FROM `char` WHERE char_id = "+ @char_id[.@a]+ " OR ", @char_name;

there is an extra OR at the end of the query

[Kakaroto]

 

nop '--'

[benching]

query_sql("query string",@varname);

or

query_sql "query string";

https://rathena.org/wiki/Query_sql

[Kakaroto]

it's caused by this.

    for ( .@a = 0; .@a < .maxsize; .@a++ )
        set .@fetch_name$, .@fetch_name$ + "char_id = "+ @char_id[.@a]+ " OR ";

the word "OR" is appended on the end of the string.

 

 

you can fix it by

create a new query that consist another sub query inside the SQL statement.

example

SELECT `a`,`b`,`c` FROM `tables1` WHERE `char_id` IN ( SELECT `char_id` FROM `char_bg` )

or

change the way to populate the string, example:

	.@fetch_name$ = "char_id = "+@char_id[0];
	for ( .@a = 1; .@a < .maxsize; .@a++ )
		set .@fetch_name$, .@fetch_name$ + " OR " + " char_id = "+ @char_id[.@a];

anyway for your case that required to check more than one variables, it's better if you just use the IN Operator.

 

Testing ..

[Lemongrass]

Honestly you could solve all your problems if you merge those two sql selects and additionally increase the npcs performance too.

 

new_1-1,51,109,5    script    BG Ranker    55,{

    mes .name$;
    mes "I can give you the Battlegrounds Raking Information.";
    mes "Choose what do you want to know.";
    next;
    
    set .@selection, select( implode( .menu$, ":" ) ) - 1;
    
    query_sql "select c.name, " + .tables$[.@selection] + " from `char_bg` cb inner join `char` c on c.char_id = cb.char_id order by " + .tables$[.@selection] + " desc limit " + .maxsize, .@char_name$, .@bgtop;
    
    mes .name$;
    mes .menu$[.@selection];
    for ( .@a = 0; .@a < .maxsize; .@a++ )
        mes "["+ (.@a+1) +"] "+ ( (.@char_name$) ? .@char_name$ : "^FF0000None^000000" );
    close;
    
    OnInit:
        set .name$,        "[^0000FFBattlegrounds Ranker^000000]";
        
        setarray .menu$[0],        "Top Game Win",
                                "Top Game Tie",
                                "Top Game Lost",
                                "Top Leader Game Win",
                                "Top Leader Game Tie",
                                "Top Leader Game Lost";
        
        setarray .tables$[0],    "win",
                                "tie",
                                "lost",
                                "leader_win",
                                "leader_tie",
                                "leader_lost";
                                
        set .maxsize,    5;        //Amount of player that will appear
        end;
        
}

 

I did not test it, but you should get the idea behind it.

[Kakaroto]

Problem solved, thanks guys!