Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Jul 2009
    Posts
    119
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Question How can I total the field values of a mysql table row and display them

    Currently I use the following to find a list of players and arrange them according to their current points totals. I need a cumulative points total.
    I need to add the values of the $row['points'], $row['Pts_Q1'], $row['Pts_Q2'], $row['Pts_Q3'], $row['Pts_Q4'] and then display them in order of the largest to smallest.

    I could add a field to the table to store the total then order them by that field, something like:
    Update Players
    Set Total_Points = points+Pts_Q1+Pts_Q2+Pts_Q3Pts_Q4
    Then query the table and order it by Total_Points

    Or could I just store them to a variable and list them without having to add the field to the table?

    From the following code I just need to display the total of all the points values instead of displaying them individually.

    PHP Code:
    echo '<div id="indent_50">';
                    
    $counter 1
                    
    $playerQuery "SELECT * FROM `players` WHERE `tavern_id` = '{$tavern_id}' ORDER BY `points` DESC";
                    
    $player_set mysql_query($playerQuery) or die(mysql_error());
                        echo 
    '<table><tbody>';
                        echo 
    '<tr>
                        <td class = lbl1>'
    ."Rank" ."</td>
                        <td class = lbl1>"
    "Points" ."</td>
                        <td class = lbl2>"
    "Name" ."</td>
                        <td class = lbl2>"
    "Quarter 1" ."</td>
                        <td class = lbl2>"
    "Quarter 2" ."</td>
                        <td class = lbl2>"
    "Quarter 3" ."</td>
                        <td class = lbl2>"
    "Quarter 4" ."</td>
                        </tr>"
    ;    
                            while (
    $row mysql_fetch_array($player_set)) 
                            {
                                echo 
    '<tr>
                                <td class = data1>'
    ."$counter" ."</td>
                                <td class = data1>"
    $row['points'] ."</td>
                                <td class = data2>"
    $row['name'] ."</td>
                                <td class = data2>"
    $row['Pts_Q1'] ."</td>
                                <td class = data2>"
    $row['Pts_Q2'] ."</td>
                                <td class = data2>"
    $row['Pts_Q3'] ."</td>
                                <td class = data2>"
    $row['Pts_Q4'] ."</td>
                                </tr>"
    ;
                                
    $counter++;
                            }
                        echo 
    '</tbody></table>';
                echo
    '</div>'
    Last edited by Skip_B; 01-13-2010 at 12:40 AM. Reason: clarification

  • #2
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    SELECT *, (`points` + `Pts_Q1` + `Pts_Q2` + `Pts_Q3` + `Pts_Q4`) as `totalpts` FROM `players` WHERE `tavern_id` = '{$tavern_id}' ORDER BY `totalpts`, `points` DESC
    Something like that?
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • Users who have thanked JAY6390 for this post:

    Skip_B (01-13-2010)

  • #3
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    An all PHP solution could be something like:

    PHP Code:
    <?php
    $db_data 
    = array('234','123','6712','23','64');
    sort($db_data);

    $total 0;

    foreach (
    $db_data as $value) {
    $total $value $total;
    echo 
    "$value<br>";
    }

    echo 
    "<br>$total";

    ?>

    output

    Code:
    23
    64
    123
    234
    6712
    
    Total 7156
    Sort would have to be changed to descending.



    -----------
    Last edited by Len Whistler; 01-13-2010 at 01:25 AM.
    Leonard Whistler

  • #4
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    You mean rsort()?
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • #5
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    Quote Originally Posted by JAY6390 View Post
    You mean rsort()?
    Yes ....... rsort . When I was proof reading my post and code I noticed the OP wanted it ordered in reverse.



    --------------------
    Leonard Whistler

  • #6
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    hehe I never proof read...throw code at it and hope for the best is my motto
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • #7
    Regular Coder
    Join Date
    Jul 2009
    Posts
    119
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Getting close

    I think I am very close but I can't display totalpts.

    PHP Code:
        echo '<div id="indent_50">';
                    
    $counter 1
                    
    $playerQuery "SELECT *, (`points` + `Pts_Q1` + `Pts_Q2` + `Pts_Q3` + `Pts_Q4`) as `totalpts` FROM `players` WHERE `tavern_id` = '{$tavern_id}' ORDER BY `totalpts`, `points` DESC";
                    
    $player_set mysql_query($playerQuery) or die(mysql_error());
                        echo 
    '<table><tbody>';
                        echo 
    '<tr>
                        <td class = lbl1>'
    ."Rank" ."</td>
                        <td class = lbl1>"
    "Points" ."</td>
                        <td class = lbl2>"
    "Name" ."</td>
                        </tr>"
    ;    
                            while (
    $row mysql_fetch_array($player_set)) 
                            {
                                
                                echo 
    '<tr>
                                <td class = data1>'
    ."$counter" ."</td>
                                <td class = data1>"
    "$totalpts"   ."</td>
                                <td class = data2>"
    $row['name'] ."</td>
                                
                                                        </tr>"
    ;
                                
    $counter++;
                            }
                        echo 
    '</tbody></table>';
                echo
    '</div>'
    <td class = data1>". "$totalpts" Yields undefined variable.
    How do I convert totalpts to a variable so I can echo it onto the screen?

  • #8
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    <td class = data1>". "$totalpts" ."</td>

    should be

    <td class = data1>". $row['totalpts'] ."</td>
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • Users who have thanked hinch for this post:

    Skip_B (01-13-2010)

  • #9
    Regular Coder
    Join Date
    Jul 2009
    Posts
    119
    Thanks
    18
    Thanked 0 Times in 0 Posts

    It works, now why

    Of course that works, you guys are the best. But help me understand why.
    totalpts is not actually a field of a row in the table. It is a value we created by adding together several several fields from a row. Does it then become like a field in the row from the list created by the query?

    I was thinking of it as a temporary variable but it appears to be more like a field in a row of a table without actually being added to the table.

    In other words, while it is not a row in the table it is a row in the query, right?
    Last edited by Skip_B; 01-13-2010 at 01:59 PM.

  • #10
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    what your doing in your query is your saying

    (`points` + `Pts_Q1` + `Pts_Q2` + `Pts_Q3` + `Pts_Q4`) as `totalpts`

    which means your creating a new "virtual" if you like row called totalpts which is the sum of all the columns listed int he brackets.

    This means that the array returned by the query now has * (all columns) and on the end of it a new column called totalpts.

    so you can call the contents of that column by using the $row['totalpts'] reference.

    $row is simply an array of elements.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • Users who have thanked hinch for this post:

    Skip_B (01-14-2010)

  • #11
    Regular Coder
    Join Date
    Jul 2009
    Posts
    119
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Thanks

    Thank you. That was a concise explanation. I totally get it and that is saying a lot.


  •  

    Posting Permissions

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