Player Checker NPC

[caspa]

I would like to request for Player Checker NPC...... It's an NPC where you input a certain name, then the npc shows the character that you've input along with all the other character in that account and tells you whether which character is online or he is currently using.....

[Emistry]

try this

http://pastebin.com/raw.php?i=P2X7dEEL

[Euphy]

getcharid() doesn't return IDs of offline characters, does it?

[Rafael]

No, it doesn't. The command returns 0 for offline characters. So, better do an escape to the character name and search it first in `name` field.

@edit

Two possible solutions:

Solution 1. Replace the query:

SELECT `name`,`online` FROM `char` WHERE `account_id`='"+getcharid( 3,.@Input$ )+"' ORDER BY `char_num` ASC LIMIT 12

With:

SELECT `char2`.`name`, `char2`.`online` FROM `char` LEFT JOIN `char` AS `char2` ON (`char`.`account_id`=`char2`.`account_id`) WHERE `char`.`name`='" + escape_sql (.@Input$) + "' ORDER BY `char2`.`char_num` ASC LIMIT 12

Solution 2. Replace the whole script with:

prontera,157,178,5    script    Sample    757,{
if( getgmlevel() >= 80 ){
   mes "Input Player Name";
   input .@Input$;
   query_sql "SELECT `account_id` FROM `char` WHERE `name`='" + escape_sql (.@Input$) + "'", .@aid;
   if (.@aid) {
       query_sql "SELECT `name`,`online` FROM `char` WHERE `account_id`='"+.@aid+"' ORDER BY `char_num` ASC LIMIT 12",.@Name$,.@Online;
       for( set .@i,0; .@i < getarraysize( .@Name$ ); set .@i,.@i + 1 )
           mes " > "+.@Name$[.@i]+" "+( ( .@Online )?"Online":"Offline" );
   }else{
       mes "No Character found.";
   }
}
close;
}

I'm not sure about which is more efficient, so it's your decision....

Edited October 29, 2012 by Rafael

[AnnieRuru]

this is more efficient xD

prontera,157,178,5	script	Sample	757,{
if ( getgmlevel() < 80 ) end;
mes "Input Player Name";
if ( input( .@Input$, 4, 23 ) ) {
	mes "invalid name length";
	close;
}
if ( .@nb = query_sql( "select name, online from `char` where account_id = ( select account_id from `char` where name = '"+ escape_sql(.@input$) +"' ) order by char_num", .@name$, .@online ) )
	for ( .@i = 0; .@i < .@nb; .@i++ )
		mes " > "+ .@name$[.@i] +" "+( ( .@online[.@i] )? "^00FF00[Online]" : "^FF0000[Offline]" )+"^000000";
else
	mes "No character found";
close;
}

I don't like using JOIN command if it can be avoided

[caspa]

=================================================================================

I'm Getting Some kind of Error on line 8.

P.S : Im using a 3ceAm SVN files

================================================================================

I've also tried the other but its a little bit different on what i ask, coz what i want is like this

1. input name = [ john ]

2. showing list of characters on john account :

3. search character [ john ]-> offline

other character [ xverdugo ] - online

other character [ mitson ] - offline

something like this so even if the paladin john is offline you would still know that he is online becoz his sniper which is xverdugo is ON =D

Edited October 29, 2012 by caspa

[Rafael]

Yeah, it's really wrong, but not in line 8. Line 8 shouldn't cause any error, if you are using rAthena. Line 9 should cause an error, because it shouldn't be set .@i = 0;, but set .@i, 0; or .@i = 0;.

The only error in line 8 is that it should be .@Input$, instead of .@input$.

Are you sure you just COPIED the script, and didn't change ANYTHING? '-'

Copy Annie's script, edit that .@input$ to .@Input$ and it should just work fine.

@Annie

Good solution, despite JOIN shouldn't be slow matching `account_id`, since it is indexed.

@edit

Generated a huge "fake" DB here, and executed the queries X times. Both took ~8.5s to run. So I'm still not sure about which is better, or if they are just the same... it should depend just on "lucky", since using a JOIN in a column that is indexed would result in a complexity O(N+M), the same for "select inside select", considering that only 1 result could match the WHERE clause (there are no identical names).

Edited October 29, 2012 by Rafael

[Ryokem]

=================================================================================

I'm Getting Some kind of Error on line 8.

P.S : Im using a 3ceAm SVN files

================================================================================

prontera,157,178,5<tab>script<tab>Sample<tab>757,{
if ( getgmlevel() < 80 )
    end;
mes "Input Player Name";
if ( input( .@input$, 4, 23 ) ) {
	mes "invalid name length";
	close;
}
if ( .@nb == query_sql( "select name, online from `char` where account_id = ( select account_id from `char` where name = '"+ escape_sql(.@input$) +"' ) order by char_num", .@name$, .@online ) )
	for ( set .@i, 0; .@i < .@nb; set .@i, .@i + 1 )
		mes " > "+ .@name$[.@i] +" "+( ( .@online[.@i] )? "^00FF00[Online]" : "^FF0000[Offline]" )+"^000000";
else
	mes "No character found";
close;
}

All credits @ AnnieRuru

3 small changes done:

input( .@input$, 4, 23 ) //The variable used in the sql command has no capital letter.
.@nb == query_sql() //dual '=' needed for comparisons
for ( set .@i, 0; .@i < .@nb; set .@i, .@i + 1 ) //Optimized for 3CeaM users (like him)

[Emistry]

.@nb == query_sql() //dual '=' needed for comparisons

Annie are not comparing the value....

but assigning the value returned by the query_sql to the variable...

input( .@input$, 4, 23 ) //The variable used in the sql command has no capital letter.

capital or non-capital letter in variable work the same...

[caspa]

i've tried what you've posted ryokem and whenever i enter a name even though its online it keeps saying character not found............

[Euphy]

Yes, of course that doesn't work - if you read what Emistry posted. >.>

[caspa]

anybody has a perfect solution? i'll pay 2$ via paypal........

[AnnieRuru]

@Rafael

omg !

yeah you are right, using select inside select will return result faster only if the search key is not indexed xD

if a search inside the where clause is indexed, run time is the same

thx for reminding me

though I still prefer to use this method since I don't need to check the field is indexed or not

here it is

prontera,157,178,5    script    Sample    757,{
   if ( getgmlevel() < 80 ) end;
   mes "Input Player Name";
   if ( input( .@input$, 4, 23 ) ) {
       mes "invalid name length";
       close;
   }
   if ( set( .@nb, query_sql( "select name, online from `char` where account_id = ( select account_id from `char` where name = '"+ escape_sql(.@input$) +"' ) order by char_num", .@name$, .@online ) ) )
       for ( set .@i, 0; .@i < .@nb; set .@i, .@i +1 )
           mes " > "+ .@name$[.@i] +" "+( ( .@online[.@i] )? "^00FF00[Online]" : "^FF0000[Offline]" )+"^000000";
   else
       mes "No character found";
   close;
}

also ... everyone

eathena\rathena script engine sux big time

the labels and variable names are NOT case sensitive in rathena script engine, unlike visual basic or C++

prontera,157,178,5    script    Sample    757,{
   set .@test_var, 123456;
   npctalk .@TEST_vAr +"";
   end;
onnpckIlLeVenT:
   dispbottom "You just killed a mob ID "+ kIlLeDrid;
   end;
}

all above example works

[caspa]

annie u rock....................... i love you much....................