Extra Commands MySQL

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.

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

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.

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 [You must be registered and logged in to see this link.]. 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 [You must be registered and logged in to see this link.]. 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.