Jump to content
  • 0

Promotional Code SQL Error

NexusXVI

Asked by NexusXVI,

 Share

Question

NexusXVI Poring

NexusXVI

Posted
  • Group:  Members
  • Topic Count:  61
  • Topics Per Day:  0.01
  • Content Count:  227
  • Reputation:   6
  • Joined:  01/18/12
  • Last Seen:  
Posted (edited) 

//===== Custom Script ========================================
//= Promotional Codes
//===== By: ==================================================
//= Arzzzae
//= Rewritten by Peopleperson49 @ version 1.1.
//===== Current Version: =====================================
//= 1.8
//===== Compatible With: =====================================
//= Latest svn
//===== Description: ========================================= 
//= Allows GM's to setup promocodes ingame for players.
//===== Additional Comments: ================================= 
//= 1.0 Initial Release by Arzzzae.
//= 1.1 Modified it to allow any length code.
//= 1.2 Added option to choose whether its works any multiple
//=	accounts or one time use only. Added allow_multiple
//=	column to reward_codes table.
//= 1.3 Added option to generate random 10 character/number
//=	code.
//= 1.4 Changed the bindatcmd command to @promocode instead of
//=	@claim and @codesetup instead of @code.
//= 1.5 Cleaned up the menus to flow better and eliminate
//=	unnessary text.
//= 1.6 Change a lot of the dispbottom to mes.
//= 1.7 Added whether the code allows multiple to the view
//=	existing promocodes.
//= 1.8 Added sql tables to bottom of document.
//============================================================
-	script	promoclaim	-1,{

OnInit:
bindatcmd "promocode",strnpcinfo(3)+"::OnClaim";
bindatcmd "codesetup",strnpcinfo(3)+"::OnEditCode",99,0;
end;

OnClaim:
mes "^FF0000 Enter your promotional code.";
input .@pc$;
close2;
if(query_sql("SELECT nameid,amount FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'",.@item,.@amt)==0) { dispbottom "Invalid promotional code."; end; }
if(query_logsql("SELECT code,account_id FROM `rewardlogs` WHERE `code`='"+escape_sql(.@pc$)+"' AND `account_id`='"+getcharid(3)+"'")) { dispbottom "You already claimed this promotional code."; end; }
else if(query_logsql("SELECT code,allow_multiple FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"' AND `allow_multiple`='1'")) {
	getitem .@item,.@amt;
	dispbottom "Promotional code successfully redeemed."; 
	query_sql ("SELECT COUNT(code) FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'", .@pcode_exists);
	if(.@pcode_exists) { query_sql ("DELETE FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'"); end; } else { dispbottom "Promotional code not found."; }
	end;
}
else if(query_logsql("SELECT code,allow_multiple FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"' AND `allow_multiple`='0'")) { 
	getitem .@item,.@amt; dispbottom "Promotional code successfully redeemed.";
	query_logsql("INSERT INTO `rewardlogs` (code,redeem_time,account_id) VALUES ('"+.@pc$+"',NOW(),"+getcharid(3)+")");
	end;
}
dispbottom "Rugby is not for everyone!";
end;

OnEditCode:
switch(prompt("Create random promocode.:Create custom promocode.:View existing promocodes.:Delete existing promocodes.")) {
case 1:
	setarray .@RandomChar$[1],"a","b","c","d","e","f","g","h","i","j","k","m","n","p","q","r","s","t","u","v","w","x","y","z","1","2","3","4","5","6","7","8","9";
	set .@CharPick1$,.@RandomChar$[rand(1,33)];
	set .@CharPick2$,.@RandomChar$[rand(1,33)];
	set .@CharPick3$,.@RandomChar$[rand(1,33)];
	set .@CharPick4$,.@RandomChar$[rand(1,33)];
	set .@CharPick5$,.@RandomChar$[rand(1,33)];
	set .@CharPick6$,.@RandomChar$[rand(1,33)];
	set .@CharPick7$,.@RandomChar$[rand(1,33)];
	set .@CharPick8$,.@RandomChar$[rand(1,33)];
	set .@CharPick9$,.@RandomChar$[rand(1,33)];
	set .@CharPick10$,.@RandomChar$[rand(1,33)];
	set .@pcode$,""+.@CharPick1$+""+.@CharPick2$+""+.@CharPick3$+""+.@CharPick4$+""+.@CharPick5$+""+.@CharPick6$+""+.@CharPick7$+""+.@CharPick8$+""+.@CharPick9$+""+.@CharPick10$+"";
	mes "The computer has generated a random promotional code of: ^0000FF"+.@pcode$+"^000000";
	next;
	query_sql ("SELECT COUNT(code) FROM `reward_codes` WHERE `code`='"+escape_sql(.@pcode$)+"'", .@pcode_exists);
	if(.@pcode_exists) { mes "Sorry, that code already exist."; close; }
	mes "The computer has generated ^FF0000"+.@pcode$+"^000000 as the promocode.";
	mes " ";
	mes " ";
	mes "Please set a reward for the player who has entered the code.";
	input .@reward;
	next;
	if(.@reward==0) { mes "Sorry, that is an invalid item id."; close; }
	set .@iname$,getitemname(.@reward);
	mes "How many ^FF0000"+.@iname$+"s^000000 would you like to give?";
	input .@amount;
	if(.@amount==0) { next; mes "Sorry, that is an invalid amount number."; close; }
	next;
	mes "Do you want this code to be usable on more than one account? Input ^FF00000^000000 to allow multiple or ^FF00001^000000 for single use.";
	input .@multiple;
	next;
	mes "You have setup the prize to be:";
	mes "^0000FF"+.@amount+"^000000 ^FF0000"+.@iname$+"^000000.";
	if(.@multiple==0) { mes "It is setup to be useable on multiple accounts once."; } else { mes "It is setup to only be valid one time."; }
	if(select("Yes:No, thanks")==2) { close; }
	query_sql ("INSERT INTO `reward_codes` (code,nameid,item_name,amount,time_created,allow_multiple) VALUES ('"+.@pcode$+"', "+.@reward+", '"+.@iname$+"', "+.@amount+", NOW(),'"+.@multiple+"')");
	dispbottom "Promotional Code ^FF0000"+.@pcode$+" ^000000is now activated and the reward is ^FF0000"+.@amount+" "+.@iname$+".";
	end;
case 2:
	mes "Please specify what you would like the new code to be?";
	input .@pcode$;
	if(getstrlen(.@pcode$)<1) { mes "You must enter something."; close; }
	next;
	query_sql ("SELECT COUNT(code) FROM `reward_codes` WHERE `code`='"+escape_sql(.@pcode$)+"'", .@pcode_exists);
	if(.@pcode_exists) { mes "Sorry, that code already exist."; close; }
	mes "You have entered ^FF0000"+.@pcode$+"^000000 as the code.";
	mes " ";
	mes " ";
	mes " ";
	mes "Please set a reward for the player who has entered the code.";
	input .@reward;
	next;
	if(.@reward==0) { mes "Sorry, that is an invalid item id."; close; }
	set .@iname$,getitemname(.@reward);
	mes "How many ^FF0000"+.@iname$+"s^000000 would you like to give?";
	input .@amount;
	if(.@amount==0) { next; mes "Sorry, that is an invalid amount number."; close; }
	next;
	mes "Do you want this code to be usable on more than one account? Input ^FF00000^000000 to allow multiple or ^FF00001^000000 for single use.";
	input .@multiple;
	next;
	mes "You have setup the prize to be:";
	mes "^0000FF"+.@amount+"^000000 ^FF0000"+.@iname$+"^000000.";
	if(.@multiple==0) { mes "It is setup to be useable on multiple accounts once."; } else { mes "It is setup to only be valid one time."; }
	if(select("Yes:No, thanks")==2) { close; }
	query_sql ("INSERT INTO `reward_codes` (code,nameid,item_name,amount,time_created,allow_multiple) VALUES ('"+.@pcode$+"', "+.@reward+", '"+.@iname$+"', "+.@amount+", NOW(),'"+.@multiple+"')");
	dispbottom "Promotional Code ^FF0000"+.@pcode$+" ^000000is now activated and the reward is ^FF0000"+.@amount+" "+.@iname$+".";
	end;
case 3:
	set .@nb, query_sql("SELECT code, item_name, amount, allow_multiple amount FROM `reward_codes` ORDER BY time_created DESC LIMIT 100", .@code$, .@nid$, .@amount, .@allowmulti);	
	if(.@nb==0) { dispbottom "No existing codes."; end; }
	dispbottom "=============================================";
	dispbottom "==============  EXISITING CODES  ==============";
	dispbottom "=============================================";
	for(set .@i,0; .@i < .@nb; set .@i,.@i+1) {
	dispbottom ""+.@code$[.@i]+" (Reward: "+.@amount[.@i]+" "+.@nid$[.@i]+") ("+(.@allowmulti[.@i]==0?"Multiple account use.":"One time use only.")+")"; }
	dispbottom "=============================================";
	end;
case 4:
	input .@dcode$;
	query_sql ("SELECT COUNT(code) FROM `reward_codes` WHERE `code`='"+escape_sql(.@dcode$)+"'", .@pcode_exists);
	if(.@pcode_exists) {
		mes "Would you like to delete that code?";
		next;
		if(select("Yes:No, thanks")==2) { close; }
		query_sql ("DELETE FROM `reward_codes` WHERE `code`='"+escape_sql(.@dcode$)+"'");
		mes "Promotional code successfully deleted.";
		close;
	}
	dispbottom "Promotional code not found.";
	end;
case 255:
	close;
}
}

/*
CREATE TABLE IF NOT EXISTS `reward_codes` (
  `code` varchar(10) NOT NULL DEFAULT '',
  `nameid` int(11) unsigned NOT NULL DEFAULT '0',
  `item_name` varchar(45) NOT NULL DEFAULT '',
  `amount` smallint(6) unsigned NOT NULL DEFAULT '0',
  `time_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `allow_multiple` int(11) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `rewardlogs` (
  `code` varchar(10) NOT NULL DEFAULT '',
  `redeem_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `account_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
*/

This was the Edited version of Peoplesperson of the utility script "Promotional Code" at this topic http://rathena.org/board/topic/80676-utility-utility-promotional-codes/

This maybe dead or too old but I think this is kinda useful:

There are 2 Problems here :

1. When you choose if the code can be used by multiple accounts.. -.- It literally can be used by the same account over and over and over again, my thoughts was it can be used by different accounts not by the same one more times than you can count

2. Im having this errors on the map

 

[Debug]: Source (NPC): promoclaim (invisible/not on a map)
[Warning]: script:query_sql: Too many columns, discarding last 2 columns.
[Debug]: Source (NPC): promoclaim (invisible/not on a map)
[SQL]: DB error - Duplicate entry 'H6EfUa3jq4' for key 'PRIMARY'
[Debug]: at script.c:15156 - INSERT INTO `rewardlogs` (code,redeem_time,account_id) VALUES ('H6EfUa3jq4',NOW(),2000045)
[Debug]: Source (NPC): promoclaim (invisible/not on a map)

[Warning]: script:query_sql: Too many columns, discarding last 2 columns.
[Debug]: Source (NPC): promoclaim (invisible/not on a map)
[Warning]: script:query_sql: Too many columns, discarding last 2 columns.
[Debug]: Source (NPC): promoclaim (invisible/not on a map)
[Warning]: script:query_sql: Too many columns, discarding last 2 columns.
[Debug]: Source (NPC): promoclaim (invisible/not on a map)

It it anyway posible to be fixed?

Edited by NexusXVI
Link to comment
Share on other sites

6 answers to this question

Recommended Posts

Emistry Deviling

Emistry

Posted
  • Group:  Forum Moderator
  • Topic Count:  93
  • Topics Per Day:  0.02
  • Content Count:  10013
  • Reputation:   2348
  • Joined:  10/28/11
  • Last Seen:  
Posted

change your whole OnClaim part into this

OnClaim:
	mes "^FF0000 Enter your promotional code.";
	input .@pc$;
	close2;
	if( query_sql("SELECT nameid,amount FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'",.@item,.@amt) == 0 ){
		dispbottom "Invalid promotional code."; 
	}
	else if( query_logsql("SELECT code,account_id FROM `rewardlogs` WHERE `code`='"+escape_sql(.@pc$)+"' AND `account_id`='"+getcharid(3)+"'",.@code$,.@aid ) ){
		dispbottom "You already claimed this promotional code.";
	}
	else if( query_logsql("SELECT code,allow_multiple FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"' AND `allow_multiple`='1'",.@code$,.@multi ) ){
		getitem .@item,.@amt;
		dispbottom "Promotional code successfully redeemed."; 
		query_sql ("SELECT COUNT(code) FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'", .@pcode_exists);
		if(.@pcode_exists){
			query_sql ("DELETE FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'");
		}
		else{
			dispbottom "Promotional code not found."; 
		}
		end;
	}
	else if( query_logsql("SELECT code,allow_multiple FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"' AND `allow_multiple`='0'",.@code$,.@multi ) ){ 
		getitem .@item,.@amt; dispbottom "Promotional code successfully redeemed.";
		query_logsql("INSERT INTO `rewardlogs` (code,redeem_time,account_id) VALUES ('"+.@pc$+"',NOW(),"+getcharid(3)+")");
	}
	dispbottom "Rugby is not for everyone!";
	end;
Link to comment
Share on other sites
NexusXVI Poring

NexusXVI

Posted
  • Group:  Members
  • Topic Count:  61
  • Topics Per Day:  0.01
  • Content Count:  227
  • Reputation:   6
  • Joined:  01/18/12
  • Last Seen:  
  • Author
Posted

 

change your whole OnClaim part into this

OnClaim:
	mes "^FF0000 Enter your promotional code.";
	input .@pc$;
	close2;
	if( query_sql("SELECT nameid,amount FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'",.@item,.@amt) == 0 ){
		dispbottom "Invalid promotional code."; 
	}
	else if( query_logsql("SELECT code,account_id FROM `rewardlogs` WHERE `code`='"+escape_sql(.@pc$)+"' AND `account_id`='"+getcharid(3)+"'",.@code$,.@aid ) ){
		dispbottom "You already claimed this promotional code.";
	}
	else if( query_logsql("SELECT code,allow_multiple FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"' AND `allow_multiple`='1'",.@code$,.@multi ) ){
		getitem .@item,.@amt;
		dispbottom "Promotional code successfully redeemed."; 
		query_sql ("SELECT COUNT(code) FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'", .@pcode_exists);
		if(.@pcode_exists){
			query_sql ("DELETE FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"'");
		}
		else{
			dispbottom "Promotional code not found."; 
		}
		end;
	}
	else if( query_logsql("SELECT code,allow_multiple FROM `reward_codes` WHERE `code`='"+escape_sql(.@pc$)+"' AND `allow_multiple`='0'",.@code$,.@multi ) ){ 
		getitem .@item,.@amt; dispbottom "Promotional code successfully redeemed.";
		query_logsql("INSERT INTO `rewardlogs` (code,redeem_time,account_id) VALUES ('"+.@pc$+"',NOW(),"+getcharid(3)+")");
	}
	dispbottom "Rugby is not for everyone!";
	end;

 

Did that and still this error persist :

: DB error - Duplicate entry 'ifp7bhe3ck' for key 'PRIMARY'

[Debug]: at script.c:15156 - INSERT INTO `rewardlogs` (code,redeem_time,account_id) VALUES ('ifp7bhe3ck',NOW(),2000045)

[Debug]: Source (NPC): promoclaim (invisible/not on a map)

+ Some characters Can still get multiple items some cannot

Link to comment
Share on other sites
Emistry Deviling

Emistry

Posted
  • Group:  Forum Moderator
  • Topic Count:  93
  • Topics Per Day:  0.02
  • Content Count:  10013
  • Reputation:   2348
  • Joined:  10/28/11
  • Last Seen:  
Posted

you have duplicated contents.....drop/remove the old contents.

Link to comment
Share on other sites
NexusXVI Poring

NexusXVI

Posted
  • Group:  Members
  • Topic Count:  61
  • Topics Per Day:  0.01
  • Content Count:  227
  • Reputation:   6
  • Joined:  01/18/12
  • Last Seen:  
  • Author
Posted (edited)

you have duplicated contents.....drop/remove the old contents.

Dropped and retested, it seems that only 1 player can redeem it once meaning ,

Ex. Player 1 got the code, entered the code, got the reward and re - entered the code again and got the message "You can only redeem once"

Then Player 1 tells Player 2 the code ,

Player 2 got the code , entered the code, got the reward, re - enter the code, got another reward, re - enter the code and then got another reward :D

Every Reward he gets gives the SQL Error

 

[SQL]: DB error - Duplicate entry 'pw5br2cg7q' for key 'PRIMARY'
[Debug]: at script.c:15156 - INSERT INTO `rewardlogs` (code,redeem_time,account_id) VALUES ('pw5br2cg7q',NOW(),2000312)
[Debug]: Source (NPC): promoclaim (invisible/not on a map)

PS. This is the option on Using it on multiple accounts, meaning the code can and must be used on different account once!

To make it more clear here is the pictures :

1. The Options

1_zps1be22a51.png

2. Either usable on 1 or more accounts, Selected 0 (Zero) so it can be used by other accounts but only once :D

 

2_zpsd804dcb9.png

3. The Issue,

The first picture was on the first one to claim the said price.. The price is 7608 (Premium Ticket) *ignore the blank card*

as you can see he claimed it only once..the second time he tried the code a confirmation appears "You already claimed this promotional code." Which is what I was hoping for. but....

3_zps2eca5e91.png

The second picture shows the second one to claim the price., he/she can get the price over and over and over again.. as long as he/she is not the first one to claim it..

4_zps10473087.png

I hope you understand me now :D

and im guessing it's on the table so here it is :

 

CREATE TABLE IF NOT EXISTS `reward_codes` (
  `code` varchar(10) NOT NULL DEFAULT '',
  `nameid` int(11) unsigned NOT NULL DEFAULT '0',
  `item_name` varchar(45) NOT NULL DEFAULT '',
  `amount` smallint(6) unsigned NOT NULL DEFAULT '0',
  `time_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `allow_multiple` int(11) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `rewardlogs` (
  `code` varchar(10) NOT NULL DEFAULT '',
  `redeem_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `account_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Bump

Edited by NexusXVI
Link to comment
Share on other sites
Guih Poring

Guih

Posted
  • Group:  Members
  • Topic Count:  16
  • Topics Per Day:  0.00
  • Content Count:  40
  • Reputation:   0
  • Joined:  11/25/12
  • Last Seen:  
Posted

I'm having the same problem with this NPC :(

 

Can anyone help?

Link to comment
Share on other sites
Kong Poring

Kong

Posted
  • Group:  Members
  • Topic Count:  1
  • Topics Per Day:  0.00
  • Content Count:  26
  • Reputation:   0
  • Joined:  12/03/12
  • Last Seen:  
Posted

This is a really buggy script and code. Wrong implementation of end and close tags, as well as queries resulting to this kind of error and character being stucked.

 

 

post-12080-0-97711200-1423963166_thumb.jpg

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to question listing
×
×
  • Create New...