Jump to content
  • 0

Tournament Team DB Registration


Snaehild

Question


  • Group:  Members
  • Topic Count:  16
  • Topics Per Day:  0.00
  • Content Count:  72
  • Reputation:   7
  • Joined:  06/23/14
  • Last Seen:  

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

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

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.

Link to comment
Share on other sites

  • 0

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

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

 

Link to comment
Share on other sites

  • 0

  • Group:  Members
  • Topic Count:  16
  • Topics Per Day:  0.00
  • Content Count:  72
  • Reputation:   7
  • Joined:  06/23/14
  • Last Seen:  

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

 

Link to comment
Share on other sites

  • 0

  • Group:  Members
  • Topic Count:  16
  • Topics Per Day:  0.00
  • Content Count:  72
  • Reputation:   7
  • Joined:  06/23/14
  • Last Seen:  

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 ?

Link to comment
Share on other sites

  • 0

  • Group:  Members
  • Topic Count:  16
  • Topics Per Day:  0.00
  • Content Count:  72
  • Reputation:   7
  • Joined:  06/23/14
  • Last Seen:  

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

 

Link to comment
Share on other sites

  • 0

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

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

  • Group:  Members
  • Topic Count:  16
  • Topics Per Day:  0.00
  • Content Count:  72
  • Reputation:   7
  • Joined:  06/23/14
  • Last Seen:  

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?

Link to comment
Share on other sites

  • 0

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

.@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 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...