Help on query_sql

[noheaven]

I've tried doing a script on my own on getting information from the SQL DB, but I can't seem to make it work. I don't know how the problem is and I've read almost every tutorial and examples available, I think I have trouble comprehending these simple 'query_sql' codes.

prontera,158,177,4	script	v4p	123,{
function getPoints;
set .@points,getPoints(getcharid(3));
mes ""+.@points+"";
close;
end;

function getPoints {
set .@account_id,getarg(0);
query_sql ("SELECT `credits` FROM `cp_votes` WHERE account_id="+.@account_id+" LIMIT 1",.@points);
}
}

I don't what's the problem with this but it won't get the info from my SQL DB.

Here's a screenshot of my cp_vote on my SQL DB.

Please help. If you can make me a script that can check/delete points, I will be much indebted.

[NakedWolf]

delete my post thank you

Edited May 14, 2017 by NakedWolf

[Capuche]

Just a comment

function getPoints {
set .@account_id,getarg(0);
query_sql ("SELECT `credits` FROM `cp_votes` WHERE account_id="+.@account_id+" LIMIT 1",.@points);
}

maybe your problem is because you don't return a value from your function ?

function getPoints {
set .@account_id,getarg(0);
query_sql ("SELECT `credits` FROM `cp_votes` WHERE account_id="+.@account_id+" LIMIT 1",.@points);
return .@points;
}

[noheaven]

Thank you Capuche, that helped a lot.

I got another problem, I fixed the NPC with your help Capuche, but another problem came out, it wouldn't add up the vote points from the SQL DB.

Look at account_id column, where it says 2000003 for four columns, the NPC only displays 1 row, meaning every time I check the NPC for points, it only says that I have 5 points but in reality should have 20 points or so.

This is my edited NPC script, maybe there's another problem that I don't see that you might see.

quiz_02,22,375,6 script V4P NPC 829,{
set .n$,"[Vote Redeemer]";
function getPoints;
function delPoints;
set .@points,getPoints(getcharid(3));
mes .n$;
mes "What would you like to do?";
next;
switch(select("- Check Vote Points","- Redeem Vote Points","Cancel")) {
Case 1:
mes .n$;
mes "You have :";
mes "^FF0000"+.@points+"^000000 Vote Point(s).";
close;
Case 2:
mes .n$;
mes "How many you want to";
mes "exchange?";
input .@rpoints;
next;
if (.@rpoints > .@points) {
mes .n$;
mes "You don't have enough";
mes "vote points.";
close;
} else {
delPoints(getcharid(3),.@rpoints);
getitem 673,.@rpoints;
mes .n$;
mes "Congratulations~";
mes "Please vote again as soon as";
mes "you can.";
close;
}
default :
mes .n$;
mes "Come back when you need";
mes "anything else.";
close;
}
end; // END of NPC
// FUNCTIONS
function getPoints {
set .@account_id,getarg(0);
query_sql ("SELECT `credits` FROM `cp_votes` WHERE account_id="+.@account_id+" LIMIT 30",.@points);
return .@points;
}
function delPoints {
set .@account_id,getarg(0);
set .@rpoints,getarg(1);
query_sql("UPDATE `cp_votes` SET credits=(credits-"+.@rpoints+") WHERE account_id='"+.@account_id+"'");
return;
}
}

My hypothesis on this is that the NPC only gets the information from the banner_id column if it states that it is banner "1", if so, how can I add up those multiple banners specifically banner "2","3", and "4" and some future banners that I might want to place there.

Edited January 12, 2013 by AnnieRuru
use [codebox] if the script > 10 lines

[Capuche]

Your cp votes doesn't add up credits by account... I think it would resolve your current problem if it would do it.

My hypothesis on this is that the NPC only gets the information from the banner_id column if it states that it is banner "1"

You're wrong >.< The NPC gets the first value in your database

Other option :

First at all, what is the purpose of banner_id ? if you don't use it, I propose to update this function :

// FUNCTIONS
function getPoints {
set .@account_id,getarg(0);
set .@nb, query_sql ("SELECT `credits`,`id` FROM `cp_votes` WHERE account_id="+.@account_id+" LIMIT 100",.@points,.@id);
//-- if multi banner_id
if( .@nb > 1 ) {
//-- adding all points in one variable
	for( set .@i, 0; .@i < .@nb; set .@i, .@i + 1 )
		set .@p, .@p + .@points[.@i];
//-- delete and create 1 row for 1 account containing all points
	query_sql("DELETE FROM `cp_votes` WHERE account_id="+.@account_id+" AND id!="+.@id[0]+"");
	query_sql("UPDATE `cp_votes` SET credits="+.@p+" WHERE account_id="+.@account_id+" AND id="+.@id[0]+"");
	set .@points, .@p;
}
return .@points;
}

It delete and create 1 row for 1 account containing all points

Edited January 11, 2013 by Capuche

[noheaven]

Capuche, oh my, thank you again, thanks for the time you've spent helping me.

the banner_id is supposed to designate the banners from the vote for points system in my fluxCP. Ex. top100 = 1, top200 = 2, etc., but you fixed it and I thank you.

1 More question about this, can I add up these rows without deleting the rows itself and compiling it in one row, it kinda messes out with my v4p system in the fluxCP.

Could I possible use this :

set .@something, .@var1 + .@var2;

while setting those ".@var" to query each banner_id like this:

set .@var1, query_sql ("SELECT `credits` FROM `cp_votes` WHERE account_id="+.@account_id+" AND banner_id=1 LIMIT 100,.@points");

and so on and so forth ...

Edited January 12, 2013 by noheaven

[Emistry]

how about like this ?

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