0
    <?
$tablae = mysql_query("SELECT * FROM order_history where (type!='rent_referral' AND type!='rental_balance') AND date>'" . strtotime($time1) . "' AND date<'" . strtotime($time2) . "'  GROUP BY user_id"); 
while ($order = mysql_fetch_array($tablae)) { 

?>
<tr>
<?
$tablaes = mysql_query("SELECT * FROM members where id='$order[user_id]'"); 
$user = mysql_fetch_array($tablaes);
$idsd=$user['id'];
$rPaid=mysql_query("SELECT SUM(`price`) AS total FROM order_history WHERE (type!='rent_referral' AND type!='rental_balance') AND date>'" . strtotime($time1) . "' AND date<'" . strtotime($time2) . "'");
$hdPaid = mysql_fetch_array($rPaid);
$sPaid=mysql_query("SELECT SUM(`price`) AS total FROM order_history WHERE user_id='$idsd' AND (type!='rent_referral' AND type!='rental_balance') AND date>'" . strtotime($time1) . "' AND date<'" . strtotime($time2) . "'");
while ($hPaid = mysql_fetch_array($sPaid)) { 
?>
<td><?=$user['username']?></td>
<td><?=$hPaid['total']?></td>
<?  
}
?>

</tr>

<? } ?>

This gets me this result http://dl.dropbox.com/u/14384295/test.jpeg

I want to order the price totals by DESC.

I would need 

$sPaid=mysql_query("SELECT SUM(`price`) AS total FROM order_history WHERE user_id='$idsd' AND (type!='rent_referral' AND type!='rental_balance') AND date>'" . strtotime($time1) . "' AND date<'" . strtotime($time2) . "'");

the total on that to be ordered by DESC.

friendishan
  • 299
  • 1
  • 3
  • 9
  • passing in non-dates to your script would cause the query to fail. If you have errors on, I would likely get the names of your tables and more information I could use to then attack more directly. – DampeS8N Dec 22 '10 at 15:26
  • The dates are specified in the script... this is not the whole page i have posted. – friendishan Dec 22 '10 at 15:28

2 Answers2

1

Be really carefull with GROUP BY instructions in your SQL query. All columns which are in the result and which are not aggregate expressions (expressions would be the count, SUM, max, etc working on the group and not on the rows) should be in your group by expression; Here you use a select *, you should try to list the real columns instead, and get this list in your group by, or use only SELECT user_id.

Most database would prevent you of running such not-very-well-formted group by query, but MySQl is not bailing you, tthat does not mean he won't gives you completly wrong results if you do not rexpect this rule (all columns which are not aggregates must be in the group by).

Then you should be able to order by an agregate expression by reusing this expression and not his alias in the order clause.

regilero
  • 29,806
  • 6
  • 60
  • 99
  • $tablae = mysql_query("SELECT user_id,price FROM order_history where (type!='rent_referral' AND type!='rental_balance') AND date>'" . strtotime($time1) . "' AND date<'" . strtotime($time2) . "' GROUP BY user_id"); – friendishan Dec 22 '10 at 16:22
  • then you should GROUP BY user_id,price or do a SELECT user_id,SUM(price) to get only GROUP BY user_id. – regilero Dec 22 '10 at 17:01
0

You could either use client side sorting with javascript, there are some nice jQuery addons that can do that.

Or you have to totaly rewrite your code to have a single sql using joins and group by.

But I cannot realy follow the logic with $rPaid, $hPaid and $sPaid so I cannot help you there.

David Mårtensson
  • 7,550
  • 4
  • 31
  • 47
  • $rPaid was used for defining totals.. not included in the code posted.. Can you post some links to any jQuery addon? – friendishan Dec 22 '10 at 15:44