SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict Melter's Avatar
    Join Date
    Sep 2002
    Posts
    305
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help working out SUM query

    I'm trying to put together a table that displays total scores per player. Each player has 2 columns in the dbase which I want to add individually and display the results, then add the 2 columns' totals together to display an overall total score.

    I don't often use mysql and php so struggle with anything more than a single basic * query on a page and not needed to do multiple queries before. So having done a general query that displays the data from these columns can I make use of that query to SUM the data or do I have to close it and do a fresh query or not close it but still do a separate query? Thanks.

    Code:
    <head>
    <style type="text/css">
    * {
          margin:0;
          padding:0;
    }
    #scoretable {
        width: 400px;
        padding: 0;
        margin:0 0 20px 0;
    }
    #totaltable {
          float:right;
        width: 400px;
        padding: 0;
        margin:0 0 20px 0;
    }
    .clear {clear:right;}
    th {
        font: bold 10px Verdana, Arial, Times, Georgia, sans-serif;
        color: #4f6b72;
        border-right: 1px solid #C1DAD7;
        border-bottom: 1px solid #C1DAD7;
        border-top: 1px solid #C1DAD7;
        text-align: left;
        padding: 6px 6px 6px 12px;
        background-color:#CAE8EA;
    }
    th.center {
          text-align:center;
    }
    th.nobg {
        border-top: 0;
        border-left: 0;
        border-right: 1px solid #C1DAD7;
        background: none;
    }
    td {
        border-right: 1px solid #C1DAD7;
        border-bottom: 1px solid #C1DAD7;
        background: #fff;
        padding: 5px 5px 5px 5px;
        color: #4f6b72;
    }
    td.alt {
        background: #F5FAFA;
        color: #797268;
    }
    th.spec {
        border-left: 1px solid #C1DAD7;
        border-top: 0;
        background-color:#f5fafa;
        font: bold 8px Verdana, Arial, Times, Georgia, sans-serif;
          text-align:center;
    }
    th.specalt {
        border-left: 1px solid #C1DAD7;
        border-top: 0;
        background-color:#fff;
        font: bold 8px Verdana, Arial, Times, Georgia, sans-serif;
          text-align:center;
        color: #797268;
    }
    </style>
    </head>
    <body>
    <?
    $username="*******";
    $password="*******";
    $database="*******";
    
    mysql_pconnect(localhost,$username,$password);
    @mysql_select_db($database) or die(mysql_error());
    $query="SELECT * FROM Scoring_Track";
    $result=mysql_query($query)or die(mysql_error());
    
    $num=mysql_numrows($result);
    
    mysql_close();
    ?>
    
    <table id="scoretable" cellspacing="0" summary="The Scoring Track">
    <tr>
      <th scope="col" colspan="2" abbr="Gaming Scores" class="nobg">Gaming Scores</th>
      <th scope="col" colspan="2" abbr="Dylan" class="center">Dylan</th>
      <th scope="col" colspan="2" abbr="James" class="center">James</th>
      <th scope="col" colspan="2" abbr="Mark" class="center">Mark</th>
      <th scope="col" colspan="2" abbr="Shaun" class="center">Shaun</th>
      <th scope="col" colspan="2" abbr="Simon" class="center">Simon</th>
    </tr>
    <tr>
      <th scope="row" class="spec">Date</th>
      <th scope="row" class="spec">Game</th>
      <th scope="row" class="spec">PPs</th>
      <th scope="row" class="spec">VPs</th>
      <th scope="row" class="spec">PPs</th>
      <th scope="row" class="spec">VPs</th>
      <th scope="row" class="spec">PPs</th>
      <th scope="row" class="spec">VPs</th>
      <th scope="row" class="spec">PPs</th>
      <th scope="row" class="spec">VPs</th>
      <th scope="row" class="spec">PPs</th>
      <th scope="row" class="spec">VPs</th>
    </tr>
    
    <?
    $i=0;
    while ($i < $num) {
    
    $date=mysql_result($result,$i,"date");
    $game=mysql_result($result,$i,"game");
    $dylanplayerp=mysql_result($result,$i,"dylanplayerp");
    $dylanvictoryp=mysql_result($result,$i,"dylanvictoryp");
    $dylantributep=mysql_result($result,$i,"dylantributep");
    $jamesplayerp=mysql_result($result,$i,"jamesplayerp");
    $jamesvictoryp=mysql_result($result,$i,"jamesvictoryp");
    $jamestributep=mysql_result($result,$i,"jamestributep");
    $markplayerp=mysql_result($result,$i,"markplayerp");
    $markvictoryp=mysql_result($result,$i,"markvictoryp");
    $marktributep=mysql_result($result,$i,"marktributep");
    $shaunplayerp=mysql_result($result,$i,"shaunplayerp");
    $shaunvictoryp=mysql_result($result,$i,"shaunvictoryp");
    $shauntributep=mysql_result($result,$i,"shauntributep");
    $simonplayerp=mysql_result($result,$i,"simonplayerp");
    $simonvictoryp=mysql_result($result,$i,"simonvictoryp");
    $simontributep=mysql_result($result,$i,"simontributep");
    $dylanbadge=mysql_result($result,$i,"dylanbadge");
    $jamesbadge=mysql_result($result,$i,"jamesbadge");
    $markbadge=mysql_result($result,$i,"markbadge");
    $shaunbadge=mysql_result($result,$i,"shaunbadge");
    $simonbadge=mysql_result($result,$i,"simonbadge");
    ?>
    
    <tr>
      <th scope="row" class="specalt"><? echo $date; ?></th>
      <th scope="row" class="specalt"><? echo $game; ?></th>
      <th scope="row" class="specalt"><? echo $dylanplayerp; ?></th>
      <th scope="row" class="specalt"><? echo $dylanvictoryp; ?></th>
      <th scope="row" class="specalt"><? echo $jamesplayerp; ?></th>
      <th scope="row" class="specalt"><? echo $jamesvictoryp; ?></th>
      <th scope="row" class="specalt"><? echo $markplayerp; ?></th>
      <th scope="row" class="specalt"><? echo $markvictoryp; ?></th>
      <th scope="row" class="specalt"><? echo $shaunplayerp; ?></th>
      <th scope="row" class="specalt"><? echo $shaunvictoryp; ?></th>
      <th scope="row" class="specalt"><? echo $simonplayerp; ?></th>
      <th scope="row" class="specalt"><? echo $simonvictoryp; ?></th>
    </tr>
    
    <? 
    $i++;
    }
    echo "</table>";
    ?>
    <table id="totaltable" cellspacing="0" summary="The Total Points">
    <tr>
      <th scope="col" abbr="The Showdown" class="nobg">The Showdown</th>
      <th scope="col" colspan="2" abbr="Dylan">Dylan</th>
      <th scope="col" colspan="2" abbr="James">James</th>
      <th scope="col" colspan="2" abbr="Mark">Mark</th>
      <th scope="col" colspan="2" abbr="Shaun">Shaun</th>
      <th scope="col" colspan="2" abbr="Simon">Simon</th>
    </tr>
    <tr>
      <th scope="row" class="specalt">&nbsp;</th>
      <th scope="row" class="specalt">10</th>
      <th scope="row" class="specalt">6</th>
      <th scope="row" class="specalt">3</th>
      <th scope="row" class="specalt">3</th>
      <th scope="row" class="specalt">20</th>
      <th scope="row" class="specalt">22</th>
      <th scope="row" class="specalt">20</th>
      <th scope="row" class="specalt">26</th>
      <th scope="row" class="specalt">20</th>
      <th scope="row" class="specalt">27</th>
    </tr>
    <tr>
      <th scope="row" class="specalt">Total Points</th>
      <th scope="row" colspan="2" class="specalt">16</th>
      <th scope="row" colspan="2" class="specalt">6</th>
      <th scope="row" colspan="2" class="specalt">42</th>
      <th scope="row" colspan="2" class="specalt">46</th>
      <th scope="row" colspan="2" class="specalt">47</th>
    </tr>
    </table>
    <div class="clear"></div>
    <?
    mysql_free_result($result);
    ?>
    </body>
    Last edited by Melter; Apr 26, 2009 at 02:03. Reason: Added more code
    vinyl digging - on a secondhand vinyl journey

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Not much can be said without seeing the structures for he tables your referring to and how they relate.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    first, you should add an ORDER BY clause to your query, otherwise your palyer scores might come back all jumbled

    second, if you are going to display detail rows, then you would accumulate the totals in php while looping over the result set produced by the detail query

    moving thread to php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict Melter's Avatar
    Join Date
    Sep 2002
    Posts
    305
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay amended the OP to include all the code for the page. It's probably a bit long-winded and I'm sure there's a simpler way to do this but I'm still learning.

    Anyway the first table on the page displays each player's individual scores. The second table is where I'm struggling. So for example in this table I need to SUM dylanplayerp and display the figure, SUM dylanvictoryp and display the figure, then add those two totals together to display a full total.

    So I'm not sure if I need to make use of the initial query, amend it, or do another query and if so how to structure extra queries in the code.

    Hope that makes more sense. Thanks.
    vinyl digging - on a secondhand vinyl journey

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    By table I was referring to the create table statement. Not the the mark-up table.

  6. #6
    SitePoint Addict Melter's Avatar
    Join Date
    Sep 2002
    Posts
    305
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I don't have a create table statement because I pre-built the database before doing the code for the page. The column names are in the code if that's what you need to know. Also shown here:
    Code:
    $date=mysql_result($result,$i,"date");
    $game=mysql_result($result,$i,"game");
    $dylanplayerp=mysql_result($result,$i,"dylanplayerp");
    $dylanvictoryp=mysql_result($result,$i,"dylanvictoryp");
    $dylantributep=mysql_result($result,$i,"dylantributep");
    $jamesplayerp=mysql_result($result,$i,"jamesplayerp");
    $jamesvictoryp=mysql_result($result,$i,"jamesvictoryp");
    $jamestributep=mysql_result($result,$i,"jamestributep");
    $markplayerp=mysql_result($result,$i,"markplayerp");
    $markvictoryp=mysql_result($result,$i,"markvictoryp");
    $marktributep=mysql_result($result,$i,"marktributep");
    $shaunplayerp=mysql_result($result,$i,"shaunplayerp");
    $shaunvictoryp=mysql_result($result,$i,"shaunvictoryp");
    $shauntributep=mysql_result($result,$i,"shauntributep");
    $simonplayerp=mysql_result($result,$i,"simonplayerp");
    $simonvictoryp=mysql_result($result,$i,"simonvictoryp");
    $simontributep=mysql_result($result,$i,"simontributep");
    $dylanbadge=mysql_result($result,$i,"dylanbadge");
    $jamesbadge=mysql_result($result,$i,"jamesbadge");
    $markbadge=mysql_result($result,$i,"markbadge");
    $shaunbadge=mysql_result($result,$i,"shaunbadge");
    $simonbadge=mysql_result($result,$i,"simonbadge");
    vinyl digging - on a secondhand vinyl journey

  7. #7
    SitePoint Addict Melter's Avatar
    Join Date
    Sep 2002
    Posts
    305
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I've added more queries and this works up to a point. If I do a separate query for each SUM I can output the results fine but if I combine the query for each player as below it outputs the 1st SUM in the query twice. Where am I going wrong?

    What I've done is probably overkill but it's the only way I can think with my ltd knowledge to get this to work.

    The new queries:
    Code:
    $dquery="SELECT SUM(dylanplayerp) AS dpp,SUM(dylanvictoryp) AS dvp FROM Scoring_Track";
    $dresult=mysql_query($dquery) or die(mysql_error());
    
    $jquery="SELECT SUM(jamesplayerp) AS jpp,SUM(jamesvictoryp) AS jvp FROM Scoring_Track";
    $jresult=mysql_query($jquery) or die(mysql_error());
    
    $mquery="SELECT SUM(markplayerp) AS mpp,SUM(markvictoryp) AS mvp FROM Scoring_Track";
    $mresult=mysql_query($mquery) or die(mysql_error());
    
    $shquery="SELECT SUM(shaunplayerp) AS shpp,SUM(shaunvictoryp) AS shvp FROM Scoring_Track";
    $shresult=mysql_query($shquery) or die(mysql_error());
    
    $siquery="SELECT SUM(simonplayerp) AS sipp,SUM(simonvictoryp) AS sivp FROM Scoring_Track";
    $siresult=mysql_query($siquery) or die(mysql_error());
    
    $dpptotal=mysql_result($dresult,"dpp");
    $dvptotal=mysql_result($dresult,"dvp");
    $jpptotal=mysql_result($jresult,"jpp");
    $jvptotal=mysql_result($jresult,"jvp");
    $mpptotal=mysql_result($mresult,"mpp");
    $mvptotal=mysql_result($mresult,"mvp");
    $shpptotal=mysql_result($shresult,"shpp");
    $shvptotal=mysql_result($shresult,"shvp");
    $sipptotal=mysql_result($siresult,"sipp");
    $sivptotal=mysql_result($siresult,"sivp");
    ...and where it outputs:
    Code:
    <tr>
      <th scope="row" class="specalt">&nbsp;</th>
      <th scope="row" class="specalt"><? echo $dpptotal; ?></th>
      <th scope="row" class="specalt"><? echo $dvptotal; ?></th>
      <th scope="row" class="specalt"><? echo $jpptotal; ?></th>
      <th scope="row" class="specalt"><? echo $jvptotal; ?></th>
      <th scope="row" class="specalt"><? echo $mpptotal; ?></th>
      <th scope="row" class="specalt"><? echo $mvptotal; ?></th>
      <th scope="row" class="specalt"><? echo $shpptotal; ?></th>
      <th scope="row" class="specalt"><? echo $shvptotal; ?></th>
      <th scope="row" class="specalt"><? echo $sipptotal; ?></th>
      <th scope="row" class="specalt"><? echo $sivptotal; ?></th>
    </tr>
    vinyl digging - on a secondhand vinyl journey

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    first of all, you need not break down the different sums into separate queries, they can all be retrieved with one query

    but more importantly, you do ~not~ need to do any extra query for the sums at all -- since your first query retrieves the entire contents of the table from the database, you can simply calculate the (running) sums with php as you loop over the query result set

    i'd show you how to do it, but i don't do php

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict Melter's Avatar
    Join Date
    Sep 2002
    Posts
    305
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay that's what I thought I might be able to do but have no idea how to work this out in php so ended up doing it this way. If anyone can simplify the process for me that would be great. Thanks.
    vinyl digging - on a secondhand vinyl journey

  10. #10
    SitePoint Addict Melter's Avatar
    Join Date
    Sep 2002
    Posts
    305
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay so this has been suggested to me which seems to make sense but I'm getting an invalid argument error on the line in red. Any thoughts?

    Code:
    <?
    $total = 0;
    foreach($row['dylanplayerp'] as $c)
    {
    $dpptotal = $total + $c;
    }
    ?>
    Output is like this:
    Code:
    <tr>
    <th class="specalt"><? echo $dpptotal; ?></th>
    </tr>
    vinyl digging - on a secondhand vinyl journey


Bookmarks

Posting Permissions

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