Tournament Team DB Registration

[Snaehild]

Hi,

I'm trying to write a script where players can register for a tournament of up to 3 members per team and saves in the database. But I got stuck in the counting part.

I tried using this: 

 

gm_room,76,56,5	script	Tournament	757,{
mes "Team Name";
input .@team_name;
next;

		mes "test";
		for( set .@w,0; .@w < 1; set .@w,.@w + 1 ){
			query_sql( "SELECT SUM(`amount`) FROM `tournament` WHERE `team_name`='"+.@team_name+"'",.@amount );
			set .@total,.@total + ( .@amount );
		}		
		mes "Found "+.@total+" x "+.@i+ "";
  
  query_sql "INSERT INTO `tournament` VALUES ( '', '" + .@id + "', '" + getcharid(0) + "', '" + strcharinfo(0) + "', '" + .@team_name + "', '1' , NOW() )";

close;
end;
}

 

[Emistry]

counting total member from the database?

gm_room,76,56,5	script	Tournament	757,{
	mes "Team Name";
	input .@team_name$;
	next;
	mes "Team Name: "+.@team_name$;
	query_sql( "SELECT COUNT(1) FROM `tournament` WHERE `team_name` = '"+escape_sql(.@team_name$)+"' GROUP BY `team_name`",.@amount );
	mes "Total Member: " + .@amount;
	close;
}

 

[Snaehild]

Hi @Emistry

Thank you for the help, I was able to complete the script somehow, but i'm getting a debug error 

[Warning]: script:query_sql: Too many columns, discarding last 1 columns.
[Debug]: Source (NPC): Tournament at gm_room (77,47)

It seems to be the checkExist if statement

if( query_sql( "SELECT `team_name` FROM `tournament` WHERE `team_name` = '"+escape_sql( .@team_name$ )+"'" ) ){

gm_room,77,47,5	script	Tournament	757,{
	
	query_sql( "SELECT * FROM `tournament` WHERE `char_id`='"+escape_sql( getcharid(0) )+"'",.@w_id$,.@a_id$,.@c_id$,.@c_name$,.@team_name$,.amount$,.@time$ );	
	goto checkExist;
	
	checkExist:
		if( query_sql( "SELECT `team_name` FROM `tournament` WHERE `team_name` = '"+escape_sql( .@team_name$ )+"'" ) ){
			goto boundAddress;
		}
		else {
			goto addMember;
		}
	
	boundAddress:
		mes "This character is already registered to the team:";
		mes "^FF0000"+.@team_name$+"";
		mes "You can't change your wallet address at this time.";
		close;
	
	checkTotal:
		mes "Do you want ";
		query_sql( "SELECT COUNT(1) FROM `tournament` WHERE `team_name` = '"+escape_sql(.@w_reg$)+"' GROUP BY `team_name",.@amount );
		mes "Total Member: " + .@amount;
		if (.@amount >= 3) {
			goto moreThanThree;
		}
		else {
			query_sql "INSERT INTO `tournament` VALUES ( '', '" + .@a_id$ + "', '" + getcharid(0) + "', '" + strcharinfo(0) + "', '" + .@w_reg$ + "', '1' , NOW() )";
			close;
		}
		
	moreThanThree:
		mes "There are already 3 members of that team.";
		close;
	
	addMember:
		mes "Enter your team name";
		input .@w_reg$;
		goto checkTotal;
		close;
}

 

[Emistry]

if( query_sql( "SELECT `team_name` FROM `tournament` WHERE `team_name` = '"+escape_sql( .@team_name$ )+"'", .@team_name$ ) ){

you need to assign a variable to store the data you query from database.

[Snaehild]

On 9/19/2021 at 2:11 AM, Emistry said:

if( query_sql( "SELECT `team_name` FROM `tournament` WHERE `team_name` = '"+escape_sql( .@team_name$ )+"'", .@team_name$ ) ){

you need to assign a variable to store the data you query from database.

Perfect. Thank you Emistry.

P.S. I'm your fan 

[Snaehild]

Follow up questions @Emistry

Instead of adding this to a separate database, I want to just use a character variable so when a character gets deleted, the team can add a member.

So I have 

set team_Name$, .@w_reg$;

But how can I properly get the count if a team_Name already have 3 members?
 

query_sql( "SELECT COUNT(1) FROM `tournament` WHERE `team_name` = '"+escape_sql(.@w_reg$)+"' GROUP BY `team_name",.@amount );
		mes "Total Member: " + .@amount;
		if (.@amount >= 3) {
			goto moreThanThree;
		}

 

[Emistry]

2 hours ago, Snaehild said:

Instead of adding this to a separate database, I want to just use a character variable so when a character gets deleted, the team can add a member.

query the data from the char_reg_str table

[Snaehild]

22 hours ago, Emistry said:

query the data from the char_reg_str table

Thanks @Emistry, I thought there would be a simpler way by calling out the variable.

I'm having issues with my SQL query when using AND

checkExist:
		mes "^FF0000"+.c_id$+"";
		mes "^FF0000"+.w_key$+"";
		/* SELECT * FROM `char_reg_str` WHERE `char_id` = 150875 AND `key` LIKE 'slyr_Wallet$' */
		if( query_sql( "SELECT `char_id` FROM `char_reg_str` WHERE `key` LIKE '"+.w_key$+"'", .@c_id$ ) ){
			mes "^FF0000"+.c_id$+"";
		}
		else {
			goto addMember;
		}

I also tried nitrous' suggestion in discord to debug my sql query:

	query_sql( "SELECT `char_id`,`name` FROM `char` WHERE `char_id` LIKE '%"+escape_sql( getcharid(0) )+"%' GROUP BY `char_id` LIMIT 50",.@id,.@name$ );
	query_sql( "SELECT * FROM `char_reg_str` WHERE `char_id`='"+escape_sql( getcharid(0) )+"'",.c_id$,.c_key$,.index$,.value$ );
	mes "^FF0000"+.c_id$+"";
	mes "^FF0000"+.w_key$+"";
	.@string$ = "SELECT char_id FROM char_reg_str WHERE char_id = '"+.c_id$+"' & key = '" + .w_key$ + "'";
	debugmes .@string$;
	if (query_sql(.@string$, .@c_id$)) {
	}

	OnInit:
		.w_key$ = "slyr_Member$";	
	end;

 

[SQL]: DB error - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key = 'slyr_Member$'' at line 1

Does this have something to do with my mySQL installation?

[Emistry]

.@string$ = "SELECT char_id FROM char_reg_str WHERE char_id = '" + escape_sql(.c_id$) + "' AND key = '" + escape_sql(.w_key$) + "'";

https://www.techonthenet.com/mysql/and.php

Edited October 17, 2021 by Emistry