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

jeremyb · 10-08-2012, 10:52 PM

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"><?php echo $results_responsible_member; ?></td>

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

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

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

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

<td width="30px" align ="center"><?php echo "" . $dollars_difference . ""; ?></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

Old Pedant · 10-09-2012, 12:50 AM

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 ... tags inside of <td>...</td>. Just create the correct class for the <td> and get rid of the .

And since all except your first <td> in each row are centered, do that in the CSS as well.

jeremyb · 10-09-2012, 02:41 AM

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