SQL Script Question

[Virtue]

Hi Scripting Pros,

is it possible to use this query on a script?


SELECT COUNT(account_id) AS OnlinePlayers FROM char WHERE online=1; 

 

I will be using it for some script that checks the online player count.

Thanks to those who will help.

 

[Xgear]

You can do a select in any way you want, each row will be sent as a variable. (You can do subquery, union, group by, joins, etc)

Your query will simply output one integer row/column which needs to be stored as you normally would, 

 

[Virtue]

Alright, Thanks again for your help bro.

 

[jaBote]

You could also set a variable to the value the query_sql function returns, it'll return the number of rows returned, so as said in doc/script_commands.txt:

*query_sql("your MySQL query"{, <array variable>{, <array variable>{, ...}}});
*query_logsql("your MySQL query"{, <array variable>{, <array variable>{, ...}}});
 
Executes an SQL query. A 'select' query can fill array variables with up
to 128 rows of values, and will return the number of rows (the array size).

So this also does exactly what you want:

set .@onlinecount, query_sql("SELECT `account_id` FROM `char` WHERE `online`=1");

In this case, you can also save yourself from using this SQL query which make the script run a bit slower than using this dedicated function:

*getusers(<type&gt
 
This function will return a number of users on a map or the whole server.
What it returns is specified by Type.
 
Type can be one of the following values, which control what is returned:
 
0 - Count of all characters on the map of the invoking character.
1 - Count of all characters in the entire server.
8 - Count of all characters on the map of the NPC the script is
running in.

So it'll be a bit faster if you use this instead of the SQL query:

set .@onlineusers, getusers(1);

Hope I further helped you or at least given a bit more of insight on this.

 

[Virtue]

Well I was sorta thinking I'd do something that does not count the autotrades to the total number of that query but I think autotrades are counted as online at the SQL.

 

[Virtue]

What if I did something like this though :

SELECT COUNT(account_id) AS OnlinePlayers FROM char WHERE online=1 and last_map!="<autotrade map>"; 

 

[jaBote]

What if I did something like this though :

SELECT COUNT(account_id) AS OnlinePlayers FROM char WHERE online=1 and last_map!="<autotrade map>";

(From cellphone) Yeah you could do it, just be careful that the not equal sign in SQL is <>. This method is a bit slow to update because you'd have to wait the char server to save positions for the characters.
You could place an NPC on the autotrade map, then issue a getusers(1)-getusers(8).

Flaws of both two? The same. It will neither count autotrading nor shopping users. I'd try to source mod the server (or making a plugin) for saving autotrade status on a SQL table. Or maybe, let's get creative on there, if I remember correctly, there was a script command named getmapusers which had an option for not counting autotrades and/or buying/selling users. I can't look up on script_commands.txt ATM.

As always, hope I helped.

 

[Virtue]

Alright, I get what you are trying to say. but rather than making a source code, I think its much better to use bindatcmd to log autotrade status on a sql table. 

Thanks 

 

 

[jaBote]

(From cellphone again) I didn't think of it and it's an excellent idea, but in that case make sure that player is vending after issuing that command. I may have dreamt about it, but I'm almost sure there is a checkvending script command. Make sure you remove that status when player logs in again!