Page 84 of 84 FirstFirst ... 3474828384
Results 831 to 837 of 837
  1. #831
    After nearly a week, data is collecting data nicely on all 9 servers.

    Now to build web front end so my admins can analyze the data. Any suggestions?

    Thank you again.

    BCZ

  2. #832
    Everyone running Stat Logger should run this on their database. It's an updated fix for the blank data bug that stat logger has.

    Code:
    DELIMITER $$
    DROP TRIGGER IF EXISTS `Player_Update_BlankDataFix`$$
    CREATE TRIGGER `Player_Update_BlankDataFix` BEFORE UPDATE ON `tbl_playerdata`
     FOR EACH ROW BEGIN
        IF (NEW.SoldierName IS NULL OR CHAR_LENGTH(NEW.SoldierName) = 0) 
           AND OLD.SoldierName IS NOT NULL
           AND CHAR_LENGTH(OLD.SoldierName) > 0
            THEN SET NEW.SoldierName = OLD.SoldierName;
        END IF;
        IF (NEW.EAGUID IS NULL OR CHAR_LENGTH(NEW.EAGUID) = 0)
            AND OLD.EAGUID IS NOT NULL 
            AND CHAR_LENGTH(OLD.EAGUID) > 0
            THEN SET NEW.EAGUID = OLD.EAGUID;
        END IF;
        IF (NEW.PBGUID IS NULL OR CHAR_LENGTH(NEW.PBGUID) = 0)
            AND OLD.PBGUID IS NOT NULL 
            AND CHAR_LENGTH(OLD.PBGUID) > 0
            THEN SET NEW.PBGUID = OLD.PBGUID;
        END IF;
        IF (NEW.IP_Address IS NULL OR CHAR_LENGTH(NEW.IP_Address) = 0)
            AND OLD.IP_Address IS NOT NULL 
            AND CHAR_LENGTH(OLD.IP_Address) > 0
            AND OLD.IP_Address <> '127.0.0.1'
            THEN SET NEW.IP_Address = OLD.IP_Address;
        END IF;
        IF CHAR_LENGTH(NEW.ClanTag) = 0
            THEN SET NEW.ClanTag = NULL;
        END IF;
    END$$
    DELIMITER ;
    ____

  3. #833
    How do I modify this to do the KDR calculation?

    SELECT a.* ,b.*, c.*
    FROM tbl_playerdatabc2sqdm a
    INNER JOIN tbl_playerstatsbc2sqdm b ON a.PlayerID = b.StatsID
    INNER JOIN tbl_weaponstatsbc2sqdm c ON a.PlayerID = c.WeaponstatsID
    WHERE a.SoldierName LIKE "%%";

  4. #834
    ColColonCleaner,

    Do we copy and paste that into the command line utility or some where else?

    SQL n00b here.

    BCZ

  5. #835
    Community Contributor
    Join Date
    Nov 2011
    Posts
    3,037
    Quote Originally Posted by BalboaCZ View Post
    ColColonCleaner,

    Do we copy and paste that into the command line utility or some where else?

    SQL n00b here.

    BCZ
    Use phpMyAdmin to log into your database and then execute it from there. It is easy to do and there are a lot of YouTube videos to explain much better than I can with text.

  6. #836
    Community Contributor
    Join Date
    Nov 2011
    Posts
    3,037
    Quote Originally Posted by BalboaCZ View Post
    How do I modify this to do the KDR calculation?

    SELECT a.* ,b.*, c.*
    FROM tbl_playerdatabc2sqdm a
    INNER JOIN tbl_playerstatsbc2sqdm b ON a.PlayerID = b.StatsID
    INNER JOIN tbl_weaponstatsbc2sqdm c ON a.PlayerID = c.WeaponstatsID
    WHERE a.SoldierName LIKE "%%";
    Here is a thread XpKiller created with a bunch of different queries:
    https://forum.myrcon.com/showthread....atsDB-examples

    Some may not work since this older plugin version has some tables organized differently.

  7. #837
    Quote Originally Posted by ty_ger07 View Post
    Here is a thread XpKiller created with a bunch of different queries:
    https://forum.myrcon.com/showthread....atsDB-examples

    Some may not work since this older plugin version has some tables organized differently.
    Took me a couple hours of tweaking but what do you think? Yes, produces the expected output. Figuring out that I only needed data from two tables vs the three in the original was like "Hallelujah!" moment. Understanding the temp table naming assignment was the next. (What "FROM tbl_playerstatsbc2r24 tps" actually meant and how to use it.)

    SELECT tpd.PlayerID AS PlayerID, tpd.SoldierName AS SoldierName, SUM(tps.playerScore )AS Score, SUM(tps.playerKills) AS Kills, SUM(tps.playerDeaths) AS Deaths, (SUM(tps.playerKills)/SUM(tps.playerDeaths)) AS KDR,
    SUM( tps.playerHeadshots) AS Headshots, (SUM(tps.playerHeadshots)/SUM(tps.playerKills) * 100) AS HeadPerc ,SUM(tps.playerSuicide) AS Suicide, SUM(tps.playerTKs) AS TKs, SUM(tps.playerPlaytime) AS Playtime,
    ( SUM(tps.playerPlaytime) / 3600) AS Hours, (SUM(tps.playerKills)/(SUM( tps.playerPlaytime)/60)) AS KPM ,SUM(tps.playerRounds) AS Rounds, MAX(tps.Killstreak) AS Killstr, MAX(tps.Deathstreak) AS Deathstr
    FROM tbl_playerstatsbc2r24 tps
    INNER JOIN tbl_playerdatabc2r24 tpd ON tpd.PlayerID = tps.StatsID
    GROUP BY PlayerID
    HAVING (SUM(tps.playerKills)/SUM(tps.playerDeaths)) >= 3 AND (SUM(tps.playerHeadshots)/SUM(tps.playerKills) )>= 0.4 AND MAX(tps.Killstreak) >= 10
    ORDER BY KPM DESC

    Based off the original below:

    SELECT tpd.PlayerID AS PlayerID, tpd.SoldierName AS SoldierName, SUM(tps.Score )AS Score, SUM(tps.Kills) AS Kills, SUM(tps.Deaths) AS Deaths, (SUM(tps.Kills)/SUM(tps.Deaths)) AS KDR,
    SUM( tps.Headshots) AS Headshots, (SUM(tps.Headshots)/SUM(tps.Kills) * 100) AS HeadPerc ,SUM(tps.Suicide) AS Suicide, SUM(tps.TKs) AS TKs, SUM(tps.Playtime) AS Playtime,
    ( SUM(tps.Playtime) / 3600) AS Hours, (SUM(tps.Kills)/(SUM( tps.Playtime)/60)) AS KPM ,SUM(tps.Rounds) AS Rounds, MAX(tps.Killstreak) AS Killstr, MAX(tps.Deathstreak) AS Deathstr
    FROM tbl_playerstats tps
    INNER JOIN tbl_server_player tsp ON tsp.StatsID = tps.StatsID
    INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID
    GROUP BY PlayerID
    HAVING (SUM(tps.Kills)/SUM(tps.Deaths)) >= 3 AND (SUM(tps.Headshots)/SUM(tps.Kills) )>= 0.4 AND MAX(tps.Killstreak) >= 10
    ORDER BY KPM DESC

 

 

Similar Threads

  1. Replies: 283
    Last Post: 10-10-2014, 11:01
  2. Replies: 142
    Last Post: 09-02-2012, 09:54
  3. Replies: 94
    Last Post: 26-06-2011, 00:48
  4. Replies: 3
    Last Post: 07-12-2010, 02:24

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •