Welcome to WebmasterWorld Guest from 54.196.232.162

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)

#### Junior Member

joined:May 7, 2006
posts:169

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>0ORDER 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)

joined:July 31, 2003
posts:12533

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.