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;
\$bonus = \$row;
\$bonusMax = \$row;

\$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.

give us the two table with SHOW CREATE TABLE tableName
give us also what is the logical operation for the selection

the sql i have at the moment is

mysql_query("SELECT Name, Bonus, BonusAmount, ((Bonus/100)*\$bonusForm) AS Dep FROM games ORDER BY Bonus, Dep, BonusAmount ")

but it doesnt get ordered properly

What I want is for the results to be ordered by the ((Bonus/100)*\$bonusForm) calculation and if it exceeds the BonusAmount (maximum bonus) then to be set to this amount in the sorting.

Is this possible at all? I can do the calculation in PHP no problem but can't get the results ordered in value.

You din’t provide the SHOW CREATE TABLE tableName the input and the expected output so my idea is really what I think you want. The “if it exceeds” is the problem … example SELECT Name, Bonus, BonusAmount, ((Bonus/100)*\$bonusForm) AS Dep , (((Bonus/100)*\$bonusForm) / BonusAmount) as NewOrder FROM games ORDER BY Bonus, NewOrder, Dep, BonusAmount

in that case could it be sorted out in an array? no matter how much i look at array sorting i just cant find how i can sort an array based on a calculation variable.

I think the main problem is the way you are solving the expression. i.e.
\$deposit = (\$bonus/100*\$bonusForm);

Instead of above, use the following
\$deposit = (\$bonus*\$bonusForm)/100;

I would suggest using an associative array with \$row as your key (i.e. array('name_key' => deposit) ). Then after putting all data from the query result into the array use the asort() (or arsort() ) function after the while loop to sort the array deposit amount.
Then output results.

Here's the php documentation on asort() http://www.php.net/manual/en/function.asort.php

regards,
Caleb S.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.