Page 1 of 4 123 ... LastLast
Results 1 to 10 of 36
  1. #1

    Lightbulb How to write SQL queries for the StatsDB + examples

    This thread is about write SQL-queries for the StatsDB of the BF3 Chat, GUID, Stats and Mapstats Logger.

    The intension of this is to help developers and web designer to write queries for there needs.
    I will post examples for better understanding in this thread too.

    This thread is NOT for general plugin talk or problems.

    Basics for good and fast querys:
    - use joins <-- they are very fast on Innodb so there is no reason to avoid them.
    - in the most case query with joins is faster than two ore more separated queries
    - avoid subqueries
    - avoid count(*) on big tables
    - use SELECT * only if your really need all columns
    - if possible the WHERE condition uses a indexed column
    - use the LIMIT condition to limit the number of rows returned.


    Lets get started:
    Get a Set of data which contains the playerdata and the general playerstats
    Code:
    SELECT * 
    FROM tbl_playerdata tpd
    INNER JOIN tbl_server_player tsp ON tsp.PlayerID = tpd.PlayerID
    INNER JOIN tbl_playerstats tps ON tps.StatsID = tsp.StatsID
    WHERE tpd.SoldierName = '_I_XpKiller_I_'
    Get a Set of data which contains the playerdata and the general playerstats for a specific server.
    Code:
    SELECT * 
    FROM tbl_playerdata tpd
    INNER JOIN tbl_server_player tsp ON tsp.PlayerID = tpd.PlayerID
    INNER JOIN tbl_playerstats tps ON tps.StatsID = tsp.StatsID
    WHERE tpd.SoldierName = '_I_XpKiller_I_' AND ServerID = 1
    Set all dogtags for a player of a specific Player order by Count
    Code:
    SELECT tpd.SoldierName AS Killer, dt.Count , tpd2.SoldierName AS Victim
    FROM tbl_dogtags dt
    INNER JOIN tbl_server_player tsp ON tsp.StatsID = dt.KillerID 
    INNER JOIN tbl_server_player tsp2 ON tsp2.StatsID = dt.VictimID
    INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID
    INNER JOIN tbl_playerdata tpd2 ON tsp2.PlayerID = tpd2.PlayerID
    WHERE tpd.SoldierName = '_I_XpKiller_I_' AND tsp.ServerID = 1 
    ORDER BY Count DESC
    Get all Dogtags of a specfic player overall server
    Code:
    SELECT tpd.SoldierName AS Killer ,tpd2.SoldierName AS Victim, SUM(dt.Count) AS Count
    FROM tbl_dogtags dt
    INNER JOIN tbl_server_player tsp ON tsp.StatsID = dt.VictimID 
    INNER JOIN tbl_server_player tsp2 ON tsp2.StatsID = dt.KillerID 
    INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID
    INNER JOIN tbl_playerdata tpd2 ON tsp2.PlayerID = tpd2.PlayerID
    WHERE tpd.SoldierName = '_I_XpKiller_I_' AND tsp.ServerID = 1 
    GROUP BY dt.KillerID
    ORDER BY Count DESC
    Count Countries of all known the players
    Code:
    SELECT CountryCode,  Count(*) AS Playercount
    FROM tbl_playerdata tpd
    GROUP BY CountryCode
    ORDER BY Playercount DESC;
    Note: Put an Index on the CountryCode Column if you want to use this query frequent to improve the performance.

    Count Countries of all known the players and calculate percent of all players
    Code:
    SELECT CountryCode,  COUNT(*) AS Playercount, (COUNT(*)/(SELECT COUNT(*) FROM tbl_playerdata)*100) AS PercentOfAllPlayers
    FROM tbl_playerdata tpd
    GROUP BY CountryCode
    ORDER BY Playercount DESC;
    Note: Put an Index on the CountryCode Column if you want to use this query frequent to improve the performance.


    Count Countries of all known the players for a specific server
    Code:
    SELECT CountryCode,  Count(*) AS Playercount
    FROM tbl_playerdata tpd
    INNER JOIN tbl_server_player tsp ON tsp.PlayerID = tpd.PlayerID
    WHERE tsp.ServerID = 1
    GROUP BY CountryCode
    ORDER BY Playercount DESC;
    Get the top10 Player(score) of a specific server
    Code:
    SELECT tpd.SoldierName, tps.Score, tps.Kills, tps.Deaths, tps.Headshots 
    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
    WHERE tsp.ServerID = 1
    ORDER BY tps.Score DESC LIMIT 10;
    Or ( 5x - 10x faster) possible since Release 1.0.0.2
    Code:
    SELECT tpd.SoldierName, tps.Score, tps.Kills, tps.Deaths, tps.Headshots 
    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
    WHERE tsp.ServerID = 1 AND tps.rankScore BETWEEN 1 AND 10
    ORDER BY tps.rankScore ASC;
    Get Serverstatistics (the fastest way^^)
    Code:
    SELECT ts.ServerName, tss.ServerID, tss.CountPlayers, tss.SumScore, tss.AvgScore, tss.SumKills, tss.AvgKills, tss.SumHeadshots, tss.AvgHeadshots, tss.SumDeaths, tss.AvgDeaths, tss.SumSuicide, tss.AvgSuicide, tss.SumTKs, tss.AvgTKs, tss.SumPlaytime, tss.AvgPlaytime, tss.SumRounds, tss.AvgRounds
    FROM tbl_server_stats tss
    INNER JOIN tbl_server ts ON tss.ServerID = ts.ServerID;
    Finding suspicious Player (overall Server) can be order as you want
    Code:
    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
    Rankcalculation in Query(per Server):
    Code:
    SELECT sub.PlayerID, sub.StatsID AS StatsID, sub.rank AS Rank
    FROM (SELECT(@num := @num+1) AS rank, tsp.StatsID, tsp.PlayerID
            FROM tbl_playerstats tps 
            STRAIGHT_JOIN tbl_server_player tsp ON tsp.StatsID = tps.StatsID 
            INNER JOIN (SELECT @num := 0) x           
            WHERE tsp.ServerID = 1
            ORDER BY Score DESC) sub 
    #Score can be replaced by any column in tbl_playerstats
    WHERE  sub.PlayerID = 115
    Overall Server
    Code:
    SELECT sub2.rank, sub2.PlayerID FROM
        (SELECT (@num := @num + 1) AS rank, sub.PlayerID 
         FROM(SELECT SUM(tps.Score) AS Totalscore, tsp.PlayerID
              FROM tbl_server ts  
              INNER JOIN tbl_server_player tsp ON tsp.ServerID = ts.ServerID
              INNER JOIN tbl_playerstats tps  ON  tsp.StatsID = tps.StatsID
              INNER JOIN (SELECT @num := 0) x 
              WHERE ts.ServerGroup = 2
              GROUP BY tsp.PlayerID 
              ORDER BY Totalscore DESC
              ) sub 
        ) sub2
    WHERE sub2.PlayerID = 1965
    And other way( get Top 20 Players of all Server of the same Server group
    Code:
    SELECT tpr.rankScore, tpd.PlayerID , tpd.Soldiername,  SUM(tps.Score) AS Totalscore,  SUM(tps.Kills) AS Totalkills, SUM(tps.Headshots) AS Totalheadshots, SUM(tps.Deaths) AS Totaldeaths
    FROM tbl_server ts  
    INNER JOIN tbl_server_player tsp ON tsp.ServerID = ts.ServerID
    INNER JOIN tbl_playerstats tps  ON  tsp.StatsID = tps.StatsID
    INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID
    INNER JOIN tbl_Playerrank tpr ON tpd.PlayerID = tpr.PlayerID 
    WHERE ts.ServerGroup = 0 AND tpr.ServerGroup =  ts.ServerGroup AND tpr.rankScore BETWEEN 1 AND 20
    GROUP BY tsp.PlayerID 
    ORDER BY tpr.rankScore ASC;
    Sessions:
    Code:
    Daily Top 10 Players based on sessions:
    SELECT 
    COUNT(*) AS Sessioncount,
    SoldierName, 
    SUM(tss.Score) AS TotalScore,
    MAX(HighScore) AS MaxHighScore, 
    SUM(Kills) AS SumKills,
    SUM(Headshots) AS SumHeadshots,
    SUM(Deaths) AS SumDeaths
    FROM tbl_sessions tss
    INNER JOIN tbl_server_player tsp USING(StatsID)
    INNER JOIN tbl_playerdata tpd USING(PlayerID)
    WHERE ServerID = 2 AND  tss.Starttime >= CURDATE()
    GROUP BY tsp.StatsID
    ORDER BY TotalScore DESC
    LIMIT 10;

    Delete old chat Messages(older than 30 days) :
    Code:
    DELETE
    FROM tbl_chatlog
    WHERE logDate < DATE_SUB(CURRENT_DATE(),INTERVAL '30' DAY);
    Last edited by XpKiller; 03-04-2012 at 18:43.


  2. #2
    Placeholder


  3. #3
    Community Contributor
    Join Date
    Nov 2011
    Posts
    3,010
    I have an issue with queries where a column has a dash in it. If I need data from that column, I have to select * to get all columns because php does not like dashes and considers them as a minus sign and not part of the column name; so it won't find the specific column unless I select *. Is there a way around this?

  4. #4
    @ty_ger07
    Try this:
    SELECT `column-name` from tbl
    Last edited by XpKiller; 07-02-2012 at 18:32.


  5. #5
    Community Contributor
    Join Date
    Nov 2011
    Posts
    3,010
    I have already tried the second method, but I will try the first method. Thanks!

  6. #6
    Community Contributor
    Join Date
    Nov 2011
    Posts
    3,010
    Ah yes, thank you XpKiller!

    I should have known there was a reason that myphpadmin used `column-name` instead of 'column-name'. Time to speed up my weapon stats.

  7. #7
    Community Contributor
    Join Date
    Nov 2011
    Posts
    3,010
    The dogtag stats query is very nice. Thank you!

    I used WHERE tpd2.SoldierName = ' player ' to get all players who have taken dog tags from soldier in question.

    Very nice and quick.
    Last edited by ty_ger07; 08-02-2012 at 00:41.

  8. #8
    If anybody has performance problems or other question feel free to ask in this thread.


  9. #9

    Cool Top Players in your Database

    Here is a Examples to make a Server Ranking of all Servers , ( not only one )

    This you get back, Example on my Page with 21 Servers

    To see, i use more Servers

    PHP Code:
    <?php

    function calcScore($id){
        
    $score 0;
        
    $time 0;
        
    $kills 0;
        
    $deaths 0;
        
    $rs mysql_query("    SELECT Score, Playtime, Kills, Deaths FROM tbl_server_player
                            LEFT JOIN tbl_playerstats 
                            ON tbl_server_player.StatsID = tbl_playerstats.StatsID
                            WHERE PlayerID = '
    $id'");
        while (
    $r = @mysql_fetch_array($rs)){ 
                
    $score += $r['Score'];
                
    $time += $r['Playtime'];
                
    $kills += $r['Kills'];
                
    $deaths += $r['Deaths'];
        }
        return 
    "$score | $time | $kills | $deaths | ".round($kills/$deaths,2);
    }

    $dbname "";
    $dbpw "";
    $dbuser "";
    $database "";
    $db mysql_connect($dbserver$dbuser$dbpw) or die ("datenbankverbindung fehlgeschlagen: ".mysql_error());
    $db_select mysql_select_db($database) or die ("auswahl der datenbank fehlgeschlagen");

    $result = @mysql_query("
    SELECT tbl_playerrank.rankScore as rks, tbl_playerdata.SoldierName as sname, tbl_playerrank.PlayerID as id
    FROM tbl_playerrank
    LEFT JOIN tbl_playerdata ON tbl_playerdata.PlayerID = tbl_playerrank.PlayerID
    LEFT JOIN tbl_playerstats ON tbl_playerstats.StatsID = tbl_playerrank.PlayerID
    WHERE tbl_playerrank.rankScore >0
    LIMIT 20 "
    ) or die ("hure");  

    while (
    $row = @mysql_fetch_array($result)){ 

    echo 
    $row["rks"].". ".$row["sname"]." - ".calcScore($row["id"])."<br>";

    }

    ?>

  10. #10
    You know that this a way inefficient.
    You could solve that in one Query.

    Code:
    SELECT tpr.rankScore, tpd.PlayerID , tpd.Soldiername,  SUM(tps.Score) AS Totalscore,  SUM(tps.Kills) AS Totalkills, SUM(tps.Headshots) AS Totalheadshots, SUM(tps.Deaths) AS Totaldeaths
    FROM tbl_server ts  
    INNER JOIN tbl_server_player tsp ON tsp.ServerID = ts.ServerID
    INNER JOIN tbl_playerstats tps  ON  tsp.StatsID = tps.StatsID
    INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID
    INNER JOIN tbl_Playerrank tpr ON tpd.PlayerID = tpr.PlayerID 
    WHERE ts.ServerGroup = 0 AND tpr.ServerGroup =  ts.ServerGroup AND tpr.rankScore BETWEEN 1 AND 20
    GROUP BY tsp.PlayerID 
    ORDER BY tpr.rankScore ASC;
    This would be one fast query instead of 21 single querys


 

 

Posting Permissions

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