Jump to content
  • 0
Snaehild

Tournament Team DB Registration

Question

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;
}

 

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0
if( query_sql( "SELECT `team_name` FROM `tournament` WHERE `team_name` = '"+escape_sql( [email protected]_name$ )+"'", [email protected]_name$ ) ){

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

Link to comment
Share on other sites

  • 0

counting total member from the database?

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

 

Link to comment
Share on other sites

  • 0

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( [email protected]_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;
}

 

Link to comment
Share on other sites

  • 0
On 9/19/2021 at 2:11 AM, Emistry said:
if( query_sql( "SELECT `team_name` FROM `tournament` WHERE `team_name` = '"+escape_sql( [email protected]_name$ )+"'", [email protected]_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 😅

Link to comment
Share on other sites

  • 0

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$, [email protected]_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([email protected]_reg$)+"' GROUP BY `team_name",[email protected] );
		mes "Total Member: " + [email protected];
		if ([email protected] >= 3) {
			goto moreThanThree;
		}

 

Link to comment
Share on other sites

  • 0
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

Link to comment
Share on other sites

  • 0
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$+"'", [email protected]_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",[email protected],[email protected]$ );
	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$+"";
	[email protected]$ = "SELECT char_id FROM char_reg_str WHERE char_id = '"+.c_id$+"' & key = '" + .w_key$ + "'";
	debugmes [email protected]$;
	if (query_sql([email protected]$, [email protected]_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?

Link to comment
Share on other sites

  • 0
[email protected]$ = "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 by Emistry
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.



×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use and Privacy Policy.