Sorting Php Sql Query Results By Later Created Dynamic Php Variable

[gdeliana]

Hello,

 

I am doing a kind of financial calculator which gives you results about the best alternatives to choose from. I do a sql query to the table having the banks data. After that i create a bunch of variables in php and echo the results. These variables are math operations consisting in sql results variables and user input variables. I know how to order sql results, but the problem is that my criteria of sorting is from the php variables created later (which consist of math oparations between sql and user input variables). I have tried sorting it by sql rows....but the dynamic php variables created later are sometimes negatively related to the sql data....so the result does not always get the same. I have an idea like putting the dynamic php variables in an array/arrays and than sorting them according to one of the php variables. But i have no idea how it can be done. Here is a partial code, just to give you an idea of what i mean.

 

 

<form method="post" action="calc2.php" />
<input name="ss" class="highlight"/>
<input class="highlight" name="nn" size="5" />
</form>

<?php
if (isset($_POST['ss']) && $_POST['nn']>5)
{
$target = $_POST['ss'];
$result = mysql_query("SELECT * FROM list1 WHERE minimum <= {$target} ORDER BY i_rate DESC LIMIT {$number_result}");
if (mysql_num_rows($result) > 0){
while($row = mysql_fetch_array($result))
  {
$s = $target-$support+$pu+$pv; // according to this variable i want to sort the results, and $pu, $pv are created from user input, which i don't have it here, $traget is sql output. $s is a mix.
}
}
}
?>

PLS HEEELP, i have searched everywhere for this and cennot find nothing about exactly what i want.

[fesan]

Hi...

 

Can you implode all your variables in to an array. Then sort the array and explode it back out for the users output?

http://php.net/manual/en/array.sorting.php

[gdeliana]

Yes i understood. The problem is in outputting the results back to the user. For the moment i do something like creating divs with while loop. Where each div has all the results needed from a bank. The next div has all the results of the other bank analogically. This variable which i want to do the sorting of the output (divs), is loacated within the while loop which fetches the sql results. Now the questions are: How is gonna be my new array feeded with the data? How are they gonna be outputted with each while loop?

[fesan]

Can you show the code where you uses the divs to sort you output?

[Jessica]

SQL can do a lot of math, faster and easier than PHP. If you share the math you're doing, we may be able to help you do it all in SQL.

[gdeliana]

Ok guys, sorry for my late reply but i needed to translate the code variable names in english and make it a bit more clean . Here you have it:

 

 

<form method="post" action="calc2.php" />
<input name="target" class="highlight"/>Your target amount <br/>
<input class="highlight" name="nn" size="5" /> Length of the period of savings<br/>
</form>

<?php
if (isset($_POST['target']) && $_POST['nn']>5)
{
$target = $_POST['target'];
$n = $_POST['nn'];
$freq = $_POST['Frequency of Payment'];

if ($freq == "monthly") {$m = 12;}
else if ($freq == "Yearly") {$m = 1;}
else if ($freq == "Half-yearly") {$m = 2;}
else if ($freq == "quarter-yearly") {$m = 4;}

$result = mysql_query("SELECT * FROM list1 WHERE minimum <= {$target} ORDER BY i_rate DESC LIMIT {$number_result}");
if (mysql_num_rows($result) > 0){
while($row = mysql_fetch_array($result))
{

$administration_year = $row['administration'];
$i = $row[[color="#008800"][size=2]'[/size][/color]i_rate'];
$fee_rate = $row['fee'];
$max_fee = $row['max_fee'];

$ii = $i + 1;
$iii = pow($ii,$n);
$n_aid = round($n,0,PHP_ROUND_HALF_DOWN);
$iii_aid = pow($ii,$n_aid);
$iiii = $iii - 1;
$iiii_aid = $iii_aid - 1;
$iiiii = $iiii/$i;
$iiiii_aid = $iiii_aid/$i;

$n_adm = round($n,0,PHP_ROUND_HALF_UP);
$pow_adm = (pow($ii,$n_vedeni)-1)/$i;

if ($max_adm <> 0 && $fee_rate*$target*$iii>$max_popl) {
$fee_amount = $max_fee;
}
else {
$fee_amount = $fee_rate*$target*$iii;
}

$administration_total = $administration_year * $ii * $pow_adm;
$state_aid = 2000 * $iiiii_aid;


$s = $target - $state_aid + $administration_total + $fee_amount;

$mm1 = $m+1;
$mm2 = $m*2;
$mmm = ($mm1/$mm2)*$i;
$mmmm = 1+$mmm;

$monthly_payment_amount = $s/($m * $iiiii * $mmmm);

$savings = $monthly_payment_amount * $m * $iiiii * $mmmm;

$profit = $savings + $state_aid - ($monthly_payment_amount * $m * $n) - $administration_total - $fee_amount; // according to this variable i want to sort the results, and $pu, $pv are created from user input, which i don't have it here, $traget is sql output. $s is a mix.



/* RESULTS OUTPUT */

echo "<div class='results'>";
echo "Name of savings company: ".$row['name'] . " - "."Tariff: " . $row['Tariff'];
echo "<br/>";
switch ($freq) {
case "monthly":
echo "Monthly payment: ". number_format(round($monthly_payment_amount,2,PHP_ROUND_HALF_DOWN), 2, ',', ' ') ." Kc"." - "."Interest rate p.a: ".$i*100 . "%";
break;
case "yearly":
echo "Yearly payment: ". number_format(round($monthly_payment_amount,2,PHP_ROUND_HALF_DOWN), 2, ',', ' ') ." Kc"." - "."Interest rate p.a: ".$i*100 . "%";
break;
case "half-yearly":
echo "half-yearly payment: ". number_format(round($monthly_payment_amount,2,PHP_ROUND_HALF_DOWN), 2, ',', ' ') ." Kc"." - "."Interest rate p.a: ".$i*100 . "%";
break;
case "quarter-yearly":
echo "quarter-yearly payment: ". number_format(round($monthly_payment_amount,2,PHP_ROUND_HALF_DOWN), 2, ',', ' ') ." Kc"." - "."Interest rate p.a: ".$i*100 . "%";
break;
}


echo "<br />";
echo "Savings: ". number_format(round($savings,2,PHP_ROUND_HALF_DOWN), 2, ',', ' '). " Kc";
echo "<br/>";
echo "Account fee: ". number_format(round($fee_amount,2,PHP_ROUND_HALF_DOWN), 2, ',', ' ')." Kc";
echo "<br />";
echo "State aid (total): ". number_format(round($state_aid,2,PHP_ROUND_HALF_DOWN), 2, ',', ' ') ." Kc";
echo "<br/>";
echo "Administration of account (total): ". number_format(round($administration_total,2,PHP_ROUND_HALF_DOWN), 2, ',', ' ') ." Kc for the entire year";
echo "<br/>";
echo "Total Profit: ". number_format(round($profit,2,PHP_ROUND_HALF_DOWN), 2, ',', ' ') . " Kc";
echo "<br/>";

}
}
}
?>

The sql database has all the possible savings companies in my country. Now i need to tell the client which is the best. I calculate the profit, and i need to sort them by the profit. In the sql table profit isn't, as you can see by the code it is calculated later. But i need to tell the client which is the best As you can see i have sorted the results according to the interest rate, but the profit is not corelated only to it. I am trying also doing multiple order in sql, but can't see the profit being sorted from the biggest to the smallest. I am looking at the math formulas maybe s.m.th can be done in sql. The table has the following rows: company name; tariff; i_rate; administration fee; account fee (as % of target amount); max account fee.

Edited October 27, 2012 by gdeliana