help script duplicate sql

[Christopher Freitas]

Good evening, I need a help, the script has doubled the entry in this table.

What can I do?

 

[SQL]: DB error - Duplicate entry '2000000' for key 'PRIMARY'
[Debug]: at script.c:15460 - INSERT INTO `mvp` (`char_id`,`account_id`,`name`,`mvps`,`pontos`) VALUES (150012,'2000000','Admin Magnus',1,1)
[Debug]: Source (NPC): mvppoint01 (invisible/not on a map)

 

 

- script Rank MvP -1,{
OnInit:
query_sql "CREATE TABLE IF NOT EXISTS `mvprank` (`id` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0',`name` VARCHAR( 23 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,`mvpkills` BIGINT UNSIGNED NOT NULL ,UNIQUE (`id`)) ENGINE = MYISAM";
SET $a, 0;
SET $topmvp, 10;
setarray $mvpid[0],1511,1251,1734,1647,1651,1785,1630,1399,1039,1874,1272,1719,1046,1389,1885,1876,1112,1115,1957,1418,1871,1252,1768,1086,1688,1646,1373,1147,1492,1059,1150,1956,2022,1087,1190,1038,1157,1159,1502,1623,1650,1583,1779,1708,1312,1751,1685,1648,1649,1917,1658,1832,1916;
end;
 
OnNPCKillEvent:
FOR (SET .@i, 0; .@i < getarraysize($mvpid); SET .@i, .@i + 1) {
IF (killedrid == $mvpid[.@i]) SET .@s,1;
}
IF (!.@s) end;
IF ($a) announce "O player [ "+strcharinfo(0)+" ] matou o MvP [ "+getmonsterinfo(killedrid,0)+" ] no mapa [ "+strcharinfo(3)+" ]",bc_blue|bc_all;
query_sql "INSERT INTO mvprank (id, name, mvpkills) values('"+getcharid(0)+"','"+strcharinfo(0)+"','1') ON DUPLICATE KEY UPDATE mvpkills = mvpkills + '1'";
end;
}
 
prontera,145,177,4 script Top MVP Killers 837,{
IF (getgroupid() > 90) {
mes "[^0000FFTop MvP "+$topmvp+"^000000]";
mes "O que deseja fazer?";
switch(SELECT("Ver Top",(($a) ? "Desligar anúncio":"Ligar anúncio"),"Resetar Top MvP:Número de posições")) {
case 1: break;
case 2:
SET $a, ($a) ? 0 : 1;
close;
case 3:
query_sql "TRUNCATE `mvprank`";
close;
case 4:
mes "Digite o número de posições do top mvp.";
input $topmvp;
break;
}
next;
}
query_sql "SELECT `name`,`mvpkills` FROM `mvprank` ORDER BY `mvpkills` DESC LIMIT "+$topmvp+"",@names$,@mvpkills;
mes "[^0000FFTop MvP "+$topmvp+"^000000]";
FOR (SET @c, 0; @c < $topmvp; SET @c, @c + 1) {
mes "^FF0000"+(@c+1)+"º^000000 ^0000FF"+@names$[@c]+"^000000 matou ^FF0000"+@mvpkills[@c]+"^000000 MvP's.";
}
close;
OnInit:
query_sql "SELECT `name`,`mvpkills` FROM `mvprank` ORDER BY `mvpkills` DESC LIMIT 1",.@names$,.@mvpkills;
waitingroom .@names$,0;
end;
}
 
//===============================
//=======COntador MvP============
//========by: Fenrir===============
 
- script mvppoint01 -1,{
OnNPCKillEvent:
setarray .@monsterID[0],1038,1039,1046,1059,1086,1087,1112,1115,1147,1150,1157,1159,1190,1251,1252,1272,1312,1373,1389,1418,1492,1511,1583,1623,1630,1658,1685,1688,1719,1785,1885; // ID Dos Boss
for(set @a,0; @a <= getarraysize(.@monsterID); set @a,@a+1)
if(killedrid == .@monsterID[@a]){
set @CID,getcharid(0);set @name$,"";
query_sql "SELECT `name` FROM `mvp` WHERE `char_id`="+@CID+"",@name$;
if ( @name$ == "" ) {
query_sql "INSERT INTO `mvp` (`char_id`,`account_id`,`name`,`mvps`,`pontos`) VALUES ("+@CID+",'"+getcharid(3)+"','"+strcharinfo(0)+"',1,1)",@esc$;
set #MVPPOINTS,#MVPPOINTS+1;
 message strcharinfo(0),"[SERVER]: Você possui "+#MVPPOINTS+" ponto(s) de MvPs.";
end;
}
set #MVPPOINTS,#MVPPOINTS+1;
query_sql "UPDATE `mvp` SET `mvps` = `mvps` +1,`pontos` = `pontos` +1 WHERE `char_id`="+@CID+"",@esc$;
  message strcharinfo(0),"[SERVER]: Você possui "+#MVPPOINTS+" ponto(s) de MvPs.";
  end;
}}
- script mvppoint02 -1,{
OnNPCKillEvent:
setarray .@monsterID[0],1708,1734,1751,1768,1779,1832,1871,1917,1990,1991; // ID Dos Boss
for(set @a,0; @a <= getarraysize(.@monsterID); set @a,@a+1)
if(killedrid == .@monsterID[@a]){
set @CID,getcharid(0);set @name$,"";
query_sql "SELECT `name` FROM `mvp` WHERE `char_id`="+@CID+"",@name$;
if ( @name$ == "" ) {
query_sql "INSERT INTO `mvp` (`char_id`,`account_id`,`name`,`mvps`,`pontos`) VALUES ("+@CID+",'"+getcharid(3)+"','"+strcharinfo(0)+"',1,3)",@esc$;
set #MVPPOINTS,#MVPPOINTS+3;
message strcharinfo(0),"[SERVER]: Você possui "+#MVPPOINTS+" ponto(s) de MvPs.";
end;
}
set #MVPPOINTS,#MVPPOINTS+3;
query_sql "UPDATE `mvp` SET `mvps` = `mvps` +1,`pontos` = `pontos` +3 WHERE `char_id`="+@CID+"",@esc$;
message strcharinfo(0),"[SERVER]: Você possui "+#MVPPOINTS+" ponto(s) de MvPs.";
end;
}}
- script mvppoint03 -1,{
OnNPCKillEvent:
setarray .@monsterID[0],1646,1647,1648,1649,1650,1651,1874,1929,1956,1957; // ID Dos Boss
for(set @a,0; @a <= getarraysize(.@monsterID); set @a,@a+1)
if(killedrid == .@monsterID[@a]){
set @CID,getcharid(0);set @name$,"";
query_sql "SELECT `name` FROM `mvp` WHERE `char_id`="+@CID+"",@name$;
if ( @name$ == "" ) {
query_sql "INSERT INTO `mvp` (`char_id`,`account_id`,`name`,`mvps`,`pontos`) VALUES ("+@CID+",'"+getcharid(3)+"','"+strcharinfo(0)+"',1,5)",@esc$;
set #MVPPOINTS,#MVPPOINTS+5;
 message strcharinfo(0),"[SERVER]: Você possui "+#MVPPOINTS+" ponto(s) de MvPs.";
end;
}
set #MVPPOINTS,#MVPPOINTS+5;
query_sql "UPDATE `mvp` SET `mvps` = `mvps` +1,`pontos` = `pontos` +5 WHERE `char_id`="+@CID+"",@esc$;
message strcharinfo(0),"[SERVER]: Você possui "+#MVPPOINTS+" ponto(s) de MvPs.";
end;
}}
 
- script ranking_MVP_semanal -1,{
OnSat0005:
 
query_sql "SELECT `account_id`,`name`,`mvps`,`pontos` FROM `mvp` WHERE `pontos`>='1' ORDER BY `pontos` DESC, `mvps` DESC LIMIT 0,1",$@MVPSemanal,$@MVPnome$,$@MVPsqt,$@MVPPts;
if($@MVPSemanal)
{
set $historicomvp,$historicomvp+1;
query_sql "UPDATE `mvp` SET `premio_mvp` = 7558 WHERE `account_id`='"+$@MVPSemanal+"'";
   Announce "Vencedor do ranking MvP semanal é: "+$@MVPnome$+", MvPs: "+$@MVPsqt+" Pontos: "+$@MVPPts+".",8;
query_sql "UPDATE `mvp` SET `mvps`='0',`pontos`='0'";
query_sql "INSERT INTO `premio_mvp` (`historico`,`account_id`,`name`,`mvps`,`pontos`) VALUES ('"+$historicomvp+"','"+$@MVPSemanal+"','"+$@MVPnome$+"','"+$@MVPsqt+"','"+$@MVPPts+"')";
sleep2 4000;
   Announce "O Player "+$@MVPnome$+" receberá como prêmio: 1 "+getitemname(20061)+".",8;
end;
}}
 
 

[Tranquility]

What is the primary key there, the charID or the accountID? Else players with the same account but different characters will create duplicate entries due to it being the PK.

[shadowscreen]

The primary key of your database table is the account ID,  you have to set the charID as primaryKey.

 

The accountID may not be unique aswell

Edited April 7, 2015 by shadowscreen

[Sehrentos]

Hmm... at line 59 the if statement:

set @CID,getcharid(0);set @name$,"";
query_sql "SELECT `name` FROM `mvp` WHERE `char_id`="+@CID+"",@name$;
if ( @name$ == "" ) { 

How about changing the if statement like this?

 

 - Because the query_sql sets array variables @name$

set @CID, getcharid(0);
query_sql("SELECT `name` FROM `mvp` WHERE `char_id`="+@CID,@name$);
if (getarraysize(@name$) > 0) {
	query_sql("UPDATE `mvp` SET `mvps` = `mvps` +1,`pontos` = `pontos` +1 WHERE `char_id`="+@CID);
} else {
	query_sql("INSERT INTO `mvp` (`char_id`,`account_id`,`name`,`mvps`,`pontos`) VALUES ("+@CID+",'"+getcharid(3)+"','"+strcharinfo(0)+"',1,1)");
}

Edited April 10, 2015 by Sehrentos

[Emistry]

you can easily fix it by changing/update your table primary key.

PRIMARY KEY ( `char_id`,`account_id` )

[Brian]

Your script shows the query that created the `mvprank` table:

OnInit:
query_sql "CREATE TABLE IF NOT EXISTS `mvprank` (`id` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0',`name` VARCHAR( 23 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,`mvpkills` BIGINT UNSIGNED NOT NULL ,UNIQUE (`id`)) ENGINE = MYISAM";

But how did you create the `mvp` table? Can you post the SQL query you used?