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 3 of 3
  1. #1
    New Coder
    Join Date
    Jul 2003
    Location
    Louisiana
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts

    order of mysql results, depending on SUM value from two variables from two different

    This one must be over my head, because I exhausted my ability to figure this one out, any help would be appreciated.

    I currently have a php page that pulls content from a MySQL database and displays it in a table format, however all the information is being pulled to the page just fine but what I would like to do now is sort this information in a fashion that I can't seem to figure out.

    The content is pulled from two separate tables. I have two different queries pulling this information. Depending on the first queries results will reflect the next query.

    example of first query:
    ---------------------------
    $query="SELECT responsible_member, SUM(count_wins) as total_wins FROM `our_game` WHERE `responsible_member` IS NOT NULL and `count_wins` IS NOT NULL GROUP BY responsible_member ORDER BY total_wins DESC, responsible_member ASC";

    $result=mysql_query($query);
    $num=mysql_num_rows($result);
    $i=0;
    while ($i < $num) {
    $results_responsible_member = mysql_result($result,$i,"responsible_member");
    $results_total_wins = mysql_result($result,$i,"total_wins");



    example of second query:
    ----------------------------
    $total = mysql_query("SELECT member, amount FROM`our_virtual_bank` WHERE amount ='-2.00' AND member='$results_responsible_member'");

    $rowtotal=mysql_fetch_assoc($total);
    $num_rows = mysql_num_rows($total);
    $dollars_spent = $num_rows * 2.00;
    $dollars_won = $results_total_wins * 4.00;
    $dollars_difference = $dollars_won - $dollars_spent;
    if ( $dollars_difference > 0 ) {
    $font_color ="#228B22";
    } else {
    $font_color ="#FF0000";
    }

    <tr <?php if($i&1) { echo 'class="odd"';} else {echo 'class="even"';} ?> bgcolor="<?php echo"$bgcolor"; ?>">

    <td width="" align ="left"><p class="event"><?php echo $results_responsible_member; ?></p></td>

    <td width="16px" align ="center"><p class="event"><?php echo $results_total_wins; ?></p></td>

    <td width="16px" align ="center"><p class="event"><?php echo $num_rows; ?></p></td>

    <td width="30px" align ="center"><p class="event"><?php echo "$" . $dollars_spent; ?></p></td>

    <td width="30px" align ="center"><p class="event"><?php echo "$" . $dollars_won; ?></p></td>

    <td width="30px" align ="center"><p class="event"><?php echo "<font color=\"$font_color\">" . $dollars_difference . "</font>"; ?></p></td>

    </tr>


    $i++;
    }


    -----------------------------------
    now what I would like to do is first order by $total_wins
    next order by $dollars_difference

    Any help would greatly appreciated.
    Thanks,
    Jeremy

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,461
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    You need to stop using two queries and do it all in one query.

    You should virtually NEVER have to nest one query inside the loop that results from another query. Doing so is horribly inefficient performance-wise and leads to just this kind of problem when you try to other more complex things.

    *SOMETHING* like this:
    Code:
    <head>
    <style type="text/css">
    table#mainTable tr.odd {
        background-color: ????;
    }
    table#mainTable tr.even {
        background-color: ????;
    }
    table#mainTable td {
        text-align: center;
        ... copy the event class style here ...
    }
    </style>
    </head>
    <body>
    ...
    <table id="mainTable">
    ... header row ...
    
    <?php
    ...
    
    $query=" SELECT G.responsible_member, 
                    G.total_wins,
                    ( 4 * G.total_wins ) AS dollars_won,
                    B.bankRows,
                    IFNULL( B.bankAmount, 0 ) AS spentAmount,
                    ( 4 * G.total_wins - IFNULL( B.bankAmount, 0 ) ) AS dollars_difference
             FROM ( 
                    SELECT responsible_member, SUM(count_wins) AS total_wins
                    FROM our_game 
                    WHERE responsible_member IS NOT NULL 
                    AND count_wins IS NOT NULL 
                    GROUP BY responsible_member ) AS G
             LEFT JOIN ( 
                    SELECT member, COUNT(*) AS bankRows, SUM(-amount) AS bankAmount
                    FROM our_virtual_bank 
                    WHERE amount = -2.00 
                    GROUP BY member ) AS B
             ON B.member = G.responsible_member
             ORDER BY total_wins DESC, dollars_difference DESC, responsible_member ASC";
    
    $result = mysql_query($query);
    $rownum = 0;
    while ( $row = mysql_fetch_array($result) ) 
    {
        $member        = $row["responsible_member"];
        $total_wins    = $row["total_wins"];
        $dollars_spent = $row["spentAmount"];
        $bank
        $dollars_won   = $row["dollars_won"];
        $dollars_difference = $row["dollars_difference"];
        
        $font_color = ( $dollars_difference > 0 ) ? "#228B22" : "#FF0000";
    
        echo  ( $rownum & 1 != 0 ) ? '<tr class="odd">' : '<tr class="even">';
    ?>
        <td width="" style="text-align: left;"><?php echo $row["responsible_member"]; ?></td>
        <td width="16px"><?php echo $row["total_wins"]; ?></td>
        <td width="16px"><?php echo $row["bankRows"]; ?></p></td>
        <td width="30px">$<?php echo $row["spentAmount"]; ?></td>
        <td width="30px">$<?php echo $row["dollars_won"]; ?></td>
        <td width="30px" style="color: <?php echo $font_color; ?>">$ <?php echo $row["dollars_difference"]; ?></td>
    </tr>
    <?php
        ++$rownum;
    }
    ?>
    </table>
    The SQL query there is a little bit of a guess, but not too much of one.

    I'm not sure you need a LEFT JOIN; I suspect that an INNER JOIN would work fine. But if I wrote it right, the LEFT JOIN should work regardless.

    Might be a typo or two in there. No way for me to test it out.

    And of course I don't use PHP, so easy for me to typo in the PHP code.

    *******

    Simplify your HTML/CSS as well. There is no need for <p>...</p> tags inside of <td>...</td>. Just create the correct class for the <td> and get rid of the <p>.

    And since all except your first <td> in each row are centered, do that in the CSS as well.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jeremyb (10-09-2012)

  • #3
    New Coder
    Join Date
    Jul 2003
    Location
    Louisiana
    Posts
    26
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Thanks!!!!

    Old Pedant, first and foremost I want you to know that I don't normally post many request on forums asking for help. I typically just read other members post as a learning tool. It seems that you spend alot of your time and knowledge helping other people out and you should be rewarded for that service you provide. In my few request over the last few years it seems that you are one of the first to respond to my request, as well as other post I have read made by other members. Not only do you reply, you seem to follow through to make sure the issue is resolved or the member has a good understanding of what you are talking about.

    I seem to believe coding comes to you naturally and you don't always have to spend alot of time on an issue, but your knowledge is valuable and saves others tremendous amount of time.

    As a token of my appreciation I would like to send you a small donation. If you have a paypal account please send me your information so that I can transfer my donation to you. If not send me your mailing address and I will send it through the mail. I do realize that you had no intentions or charging for your services, but I still would like to show my appreciation.

    Thanks,
    Jeremy


  •  

    Posting Permissions

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