Welcome to WebmasterWorld Guest from 54.196.238.210

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Sorting in array or SQL statement

     

asusplay

12:20 pm on Jan 25, 2011 (gmt 0)

5+ Year Member



I need some help with something which in theory shouldnt be complicated but really has me stumped! I'm not sure whether the answer to this lies in the php code or in the SQL statement.

I am doing a bonus comparison so I am extracting 2 columns from the table: Bonus and Max Bonus. Bonus is given as a percentage of how much someone deposits (50%, 100%, 200% etc) and MaxBonus is the maximum amount that is given as a bonus (25, 50, 100 etc). So what I am trying to do is do a comparison of how much someone gets by depositing a certain amount given the bonus percentage and capped by the maximum amount they get.

Doing this calculation is easy enough with PHP :
The variable $bonusForm is how much the user enters to deposit

while($row = mysql_fetch_array($buk)){

$name = $row['Name'];
$bonus = $row['Bonus'];
$bonusMax = $row['BonusAmount'];

$deposit = ($bonus/100*$bonusForm);
if ($deposit > $bonusMax) {
$deposit = $bonusMax;
}

echo $name. " - ". $deposit;
echo "<br />";
}


But I want the results to appear in order of the amount of bonus receieved capped or otherwise, and this is where I am stuck. Can I sort this is the array and how? Is there a way to sort these results that I am missing?


Or should the results already come out ordered from the SQL statement but if so how can i add a conditional statement to the ORDER BY clause? Such as :
SELECT * FROM games 
WHERE Points>0
ORDER BY ((Bonus/100)*$bonusForm) ----- as long as it is less than MaxBonus and if => then ((Bonus/100)*$bonusForm) must be set as equal to MaxBonus and the results are ordered by this amount.

coopster

3:28 pm on Feb 3, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Rather than SELECT all columns, name those you want/need. And one of them can be a named expression that does the calculation for you. You may then use that in your ORDER BY clause. You can use the IF syntax in the expression, or CASE perhaps. Control Flow Functions [dev.mysql.com] are going to come in handy.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month