Hiện bộ gõ=phím F8
PRESS F9 to turn on/off the unicode typing method. Bộ gõ Tiếng Việt kiểu VNI đã mặc định bật, bấm F8 để hiện trạng thái bộ gõ và hiệu chỉnh khi cần.
Latest topics
» Specified Game requires a saved game file problem
Tue Nov 24, 2015 6:08 am by kingsamurie

» Strange Invalid version error after changing to bnet version 26
Wed Oct 07, 2015 9:26 am by Gen

» Perfect-G 412KANAKO Gallery
Wed Sep 23, 2015 11:32 pm by Guest

» Fully Naked Pics Of Jessica Gomes
Tue Jul 07, 2015 5:57 pm by Guest

» The One Tree Hill Season 3
Sun Jun 07, 2015 11:27 am by Guest

» The One Tree Hill Season 3
Sun Jun 07, 2015 11:20 am by Guest

» When i host i get this error using Gen MOdded Ghostone(Latest ver)
Mon Apr 21, 2014 12:00 am by kingsamurie

» When i type !map on any kind of map i get this error
Sun Apr 20, 2014 11:59 pm by kingsamurie

» Players get kicked
Thu Oct 17, 2013 1:51 am by keisersoze

» Where to find Games!
Tue Oct 15, 2013 11:22 am by kokkis

Thống Kê/Statistic
Free counters!

Extra Commands MySQL

View previous topic View next topic Go down

Extra Commands MySQL

Post by russoka on Wed Nov 07, 2012 1:45 pm

I want to show all power of MySQL.

In first - Install Bot, Install MySQL server.

Install MySQL Workbench 5.2 CE. This is Cute client.

[You must be registered and logged in to see this image.]

I'm use db_ttw for my ghost db.

[You must be registered and logged in to see this image.]


put in Sql Script this:

Code:
use test;
# Library for different functions count stats.
# Библиотека различных функций подсчета статистической информации.

# This Procedure show count games for every nickname, and his IP.
# Эта процедура показывает количество игр для каждого никнейма и его IP и место в списке.
# Call ShowRating();

# This Prodedure show players with N games and IP.
# Эта процедура показывает игроков с N игр и его IP.
# Call ShowRatingForN(1);

# This Procedure show unique Nick-IP combinations. (i think it useful for detect hackers)
# Процедура уникальных Nick-IP связок.
# Call ShowUniqueNickWithIP();

# This Procedure show count IP for Nickname
# Эта процедура показывает сколько IP у никнейма.
# Call ShowCountIP();

# This Procedure show all IP's for nickname
# Эта процедура показывает все IP у никнейма.
# Call ShowUniqueIPs();

# This Procedure show count nicknames with same IP.
# Процедура показа сколько никнеймов с одинаковым IP.
# Call ShowNicksWithSameIP();

DROP PROCEDURE ShowRating;
DROP PROCEDURE ShowRatingForN;
DROP PROCEDURE ShowCountIP;
DROP PROCEDURE ShowUniqueIPs;
DROP PROCEDURE ShowNicksWithSameIP;
DROP PROCEDURE ShowUniqueNickWithIP;
delimiter !!
CREATE PROCEDURE ShowRating ()
BEGIN
CREATE TEMPORARY TABLE tmp SELECT name, COUNT(*) as count, ip FROM db_ttw.gameplayers GROUP BY name ORDER BY COUNT(*) DESC;
CREATE TEMPORARY TABLE tmp2 (
    id Mediumint not null Auto_Increment,
    name VARCHAR(15) NOT NULL,
    ip VARCHAR(15) NOT NULL,
    count bigint(21),
    Primary Key (id)
);
INSERT INTO tmp2 (name, ip, count) SELECT name, ip, count FROM tmp;
select * FROM tmp2;
DROP TABLE tmp2;
DROP TABLE tmp;
END !!

CREATE PROCEDURE ShowRatingForN (N int(6))
BEGIN
SELECT name, COUNT(*), ip FROM db_ttw.gameplayers GROUP BY name HAVING COUNT(*) = N ORDER BY COUNT(*) DESC;
END !!

CREATE PROCEDURE ShowCountIP ()
BEGIN
CREATE TEMPORARY TABLE tmp SELECT name, ip FROM db_ttw.gameplayers GROUP BY name, ip;
select name, ip, COUNT(*) FROM tmp GROUP BY name HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC;
DROP TABLE tmp;
END !!

CREATE PROCEDURE ShowUniqueIPs ()
BEGIN
CREATE TEMPORARY TABLE tmp SELECT name, ip FROM db_ttw.gameplayers GROUP BY name, ip;
CREATE TEMPORARY TABLE tmp2 select name, ip, COUNT(*) FROM tmp GROUP BY name HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC;
SELECT t1.name, t1.ip FROM tmp as t1, tmp2 as t2 WHERE t1.name = t2.name;
DROP TABLE tmp2;
DROP TABLE tmp;
END !!

CREATE PROCEDURE ShowNicksWithSameIP ()
BEGIN
CREATE TEMPORARY TABLE tmp SELECT name, ip FROM db_ttw.gameplayers GROUP BY name, ip;
select ip, name, COUNT(*) FROM tmp GROUP BY ip HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC;
DROP TABLE tmp;
END !!

CREATE PROCEDURE ShowUniqueNickWithIP ()
BEGIN
SELECT name, ip FROM db_ttw.gameplayers GROUP BY name, ip;
END !!
delimiter ;

[You must be registered and logged in to see this image.]

Next - Setting. Before start this we should to comment this part:

Code:
DROP PROCEDURE ShowRating;
DROP PROCEDURE ShowRatingForN;
DROP PROCEDURE ShowCountIP;
DROP PROCEDURE ShowUniqueIPs;
DROP PROCEDURE ShowNicksWithSameIP;
DROP PROCEDURE ShowUniqueNickWithIP;

Just insert # Symbol in each string.

Run SQL Script!

[You must be registered and logged in to see this image.]

After uncommend all "Drop Procedure".

Congratulations. Now you can use procedures.

Just uncomment ONLY ONE Procedure!

[You must be registered and logged in to see this image.]

And Run SQL Script.

[You must be registered and logged in to see this image.]

Actuality this is not too hard. I'm writed this library in after 4 hours learning MySQL.

russoka
Active Member
Active Member

Posts : 53
Join date : 2012-10-25
Age : 26
Location : Russia / Far East

Testing
Your Garena ID:

View user profile

Back to top Go down

Re: Extra Commands MySQL

Post by Gen on Sat Nov 10, 2012 5:36 am

good work. Can you research to make and share me a php file to display gamelist like this
[You must be registered and logged in to see this link.]

It's very cool to display slot color & country flag...

Gen
Admin
Admin

Posts : 148
Join date : 2012-02-26

View user profile http://GarenaID G.M.Bot

Back to top Go down

Re: Extra Commands MySQL

Post by russoka on Mon Nov 12, 2012 2:17 am

Oh PHP. I know a little about PHP. But it is possible. About 1 week I think (coz my last practical work on php was in 2006).

p.s. Refresh. No need simple PHP, need PHP + Java Script. This is AJAX technology, known only name. So I can't to say date release this page.

russoka
Active Member
Active Member

Posts : 53
Join date : 2012-10-25
Age : 26
Location : Russia / Far East

Testing
Your Garena ID:

View user profile

Back to top Go down

Re: Extra Commands MySQL

Post by Gen on Fri Nov 16, 2012 4:04 pm

You have some experience working on retrieving information from mySQL database and displaying the players' stats in lobby. Will you be able to invest your time on making this?
We have discussed about coding it in this thread. The discussion is still going on. I've not tested the code and have not come up with a solution to display lobby infos like BGN (blinkgamenetwork). You can go ahead trying to make a working PHP, then share it + the patch (for the coding in that discussion). I shall add the patch for this + your last patch for lobby stats for the update version. It will be very cool, more intuitive & interactive. Time constraint is only my problem atm.
Thanks for your good work. Appreciated.


Gen
Admin
Admin

Posts : 148
Join date : 2012-02-26

View user profile http://GarenaID G.M.Bot

Back to top Go down

Re: Extra Commands MySQL

Post by Sponsored content Today at 12:35 pm


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum