What's the right query_sql for this?...

[sotf]

Hi rAthena, I hope you can help me on this one. 

 

Here's my script:

hyrule,142,183,4	script	MVP Ladder	459,{
set .ahmel$,"^00aa00[MVP Ladder]^000000";

mes .ahmel$;
mes "I list those players who succeeds to kill MVP and/or Mini Boss monsters and organize them on a Ranking List!";
mes "^0000ffMVP Boss^000000 = 3 points";
mes "^0000ffMini Boss^000000 = 1 point";
next;
	if(getgroupid() == 99)
	{
		switch(select("Show MVP Ladder:Point Shop:Reset Ladder:Cancel"))
			{
				case 1:
				goto mvprank;

				case 2:
				goto pointshop;
				
				case 3:
				goto reset;

				case 4:
				goto cancel;
			}
	}
	else
	{
		switch(select("Show MVP Ladder:Point Shop:Cancel"))
			{
				case 1:
				goto mvprank;

				case 2:
				goto pointshop;
				
				case 3:
				goto cancel;
			}
	}
	
	
pointshop:
	mes .ahmel$;
	mes "Here you can use your MVP Points to purchase useful items:";
	next;
	mes "^0000ffMVP Badge^000000: requirement for Class Specialization";
	mes "^ffffff.^000000";
	//mes "^0000ffArmor Exchange Ticket^000000: useful if you want to exchange your QUEST armor for another QUEST armor of the same level";
	//mes "^ffffff.^000000";
	//mes "^0000ffWeapon Exchange Ticket^000000: useful if you want to exchange your QUEST weapon for another QUEST weapon";
	//mes "^ffffff.^000000";
	//mes "^0000ffHeadgear Exchange Ticket^000000: useful if you want to exchange your tier 3 QUEST headgears for another tier 3 QUEST headgear set";
	//mes "^ffffff.^000000";
	switch(select("MVP Badge - 30 pts:^aaaaaaArmor Exchange Ticket - 150 pts:Weapon Exchange Ticket - 150 pts:Headgear Exchange Ticket - 150 pts^000000"))
	{
	case 1:
	next;
	set .@price,30;
	set .@mvpitem$,"MVP Badge";
	set .@itemshit,26047;
	mes .ahmel$;
	mes "How many MVP Badge do you want to purchase?";
	input .@quantity;
	next;
	goto shopmenu;

		
	case 2:
		next;
		mes .ahmel$;
		mes "Not yet implemented!";
		close;
		//set .@price,150;
		//set .@mvpitem$,"Armor Excange Ticket";
		//set .@itemshit,26018;
		//mes .ahmel$;
		//mes "How many Armor Exchange Ticket do you want to purchase?";
		//input .@quantity;
		//next;
		//goto shopmenu;

	
	case 3:
		next;
		mes .ahmel$;
		mes "Not yet implemented!";
		close;
		//set .@price,150;
		//set .@mvpitem$,"Weapon Exchange Ticket";
		//set .@itemshit,26020;
		//mes .ahmel$;
		//mes "How many Weapon Exchange Ticket do you want to purchase?";
		//input .@quantity;
		//next;
		//goto shopmenu;

	
	case 4:
		next;
		mes .ahmel$;
		mes "Not yet implemented!";
		close;
		//set .@price,150;
		//set .@mvpitem$,"Headgear Exchange Ticket";
		//set .@itemshit,26019;
		//mes .ahmel$;
		//mes "How many Headgear Exchange Ticket do you want to purchase?";
		//input .@quantity;
		//next;
		//goto shopmenu;
		
	}
	
shopmenu:
	mes .ahmel$;
	mes "You're going to purchase "+.@quantity+" "+.@mvpitem$+", proceed?";
	menu "Yes",yesbadge,"Cancel",nobadge;
		
		yesbadge:
		next;
		query_sql( "SELECT `name`='"+strcharinfo(0)+"',`Count` FROM `E-MVPRank`",.@Name$,.@Count );
		if(.@Count < .@price*.@quantity){
		mes .ahmel$;
		mes "Insufficient Points";
		mes ""+.@Count+" "+.@quantity+" "+.@price+"";
		close; }
		else {
		getitem .@itemshit,.@quantity;
		set .@quantity,.@quantity*.@price;
		query_sql( "INSERT INTO `E-MVPRank` SET `char_id`='"+getcharid(0)+"',`name`='"+strcharinfo(0)+"',`Count`='3' ON DUPLICATE KEY UPDATE `Count`=`Count`-"+.@quantity+"" );
		mes .ahmel$;
		mes "Done! Here are your "+.@mvpitem$+".";
		mes "^ff0000"+.@quantity+" pts^000000 have been deducted to you";
		close;}
		
		nobadge:
		next;
		mes .ahmel$;
		mes "Alright! Come back when you're decided."; close;
		
		
mvprank:
	query_sql( "SELECT `name`,`Count` FROM `E-MVPRank` ORDER BY `Count` DESC LIMIT 5",.@Name$,.@Count );
	if( getarraysize( .@Name$ ) ){
		for( set .@i,0; .@i < getarraysize( .@Name$ ); set .@i,.@i + 1 )
			mes "[# "+( .@i + 1 )+" ]^ff0000 "+.@Name$[.@i]+" ^000000- "+.@Count[.@i]+" Point(s)"; 
			if(getgroupid() == 99)
				{
					switch(select("Reset Ladder","Close"))
						{
							case 1:
							goto reset;
							
							case 2:
							close;
						}
				}
			else
				{
					close;
				}
		}
	else{
		mes .ahmel$;
		mes "No Record Found."; close; end;
		}


reset:
	next;
	mes .ahmel$;
	mes "Are you really sure you want to reset the MVP Ladder?";
	switch(select("Yes","Cancel"))
	{
	
	case 1:
	next;
	mes .ahmel$;
	mes "Input Reset Password:";
	next;
	input .@pwdreset$;
	if(.@pwdreset$ == "phoenix")
	{
		mes .ahmel$;
		mes "Done!"; 
		query_sql( "TRUNCATE TABLE `e-mvprank`;" );
		announce "[MVP Ladder]: Ranking is Reset! Goodluck Boss-hunting everyone!",bc_all,0xdddddd;
		end;
		close;
	}
	else if(.@pwdreset$ != "phoenix")
	{
	next;
	mes .ahmel$;
	mes "INVALID PASSWORD!"; end; close;
	}
	
	case 2:
	close;
	}

cancel:
	mes .ahmel$;
	mes "Okay, goodluck hunting MVPs!";
	end; close;

OnNPCKillEvent:
if( getmonsterinfo( killedrid,MOB_MVPEXP ) )
	{ query_sql( "INSERT INTO `E-MVPRank` SET `char_id`='"+getcharid(0)+"',`name`='"+strcharinfo(0)+"',`Count`='3' ON DUPLICATE KEY UPDATE `Count`=`Count`+2" );
		announce "[MVP Ladder]: "+strcharinfo(0)+" slained "+getmonsterinfo( killedrid,MOB_NAME )+" at "+strcharinfo(3),bc_all,0xdddddd; }
	
if ( getmonsterinfo( killedrid,MOB_MODE ) & 0x0020 ) 
	{ query_sql( "INSERT INTO `E-MVPRank` SET `char_id`='"+getcharid(0)+"',`name`='"+strcharinfo(0)+"',`Count`='1' ON DUPLICATE KEY UPDATE `Count`=`Count`+1" );}
end;
}

And I'm having a big problem in this part:

yesbadge:
		next;
		query_sql( "SELECT `name`='"+strcharinfo(0)+"',`Count` FROM `E-MVPRank`",.@Name$,.@Count );
		if(.@Count < .@price*.@quantity){
		mes .ahmel$;
		mes "Insufficient Points";
		close; }
		

What's the right sql query that call's a specific character's row and shows the count? 

Edited December 24, 2013 by iObserve

[Capuche]

query_sql( "SELECT `Count` FROM `E-MVPRank` where `name`= '" + escape_sql( strcharinfo(0) ) + "'", .@Count );

nb

cancel:
	mes .ahmel$;
	mes "Okay, goodluck hunting MVPs!";
	end; close;

you will be stuck by the mes window because of the end, you just need close

[sotf]

^Thank you again Sir Capuche! This helped me a lot really, and thank you very much for the tip