mhopkins321 mhopkins321 - 1 year ago 68
MySQL Question

Rearrange data before printing

I currently have code that pulls data from a db, then does some math on part of it, and then prints it to a table. However, I would like to arrange the numbers printed from larges down to smallest. I'm familiar with ordering in the query, however the query doesn't know what the end result will be. What would be the most efficient way to go about rearranging this data.

$hourAmount = $mlYeild * (3600 / $cycleTime);
$oreVolumePriceQuery = "SELECT od.oreID, od.inGameID, od.size, ir.officialCorpRate FROM oreData od JOIN itemRates ir ON od.inGameID = ir.typeID order by od.oreID asc";
$oreVolumePriceResult = mysql_query($oreVolumePriceQuery);
$hourYeild = $mlYeild * (3600 / $cycleTime);
<TD><h1>Hourly Yeild</h1></TD>
<TD><h1>Hourly Sale</h1></TD>
$ores = array();
While($row = mysql_fetch_assoc($oreVolumePriceResult)){
$inGameID = $row['inGameID'];
$volume = $row['size'];
$rate = $row['officialCorpRate'];
$name = $row['name'];
$hourlyYeild = $hourAmount/$volume;
$hourlyIncome = $hourlyYeild * $rate;

echo "<TR>";
echo "<TD>".$name."</TD>";
echo "<TD>".$hourlyYeild."</TD>";
echo "<TD>".$hourlyIncome."</TD>";
echo "</TR>";

in short, from the data that comes in, I would like it to be arranged via
$hourlyIncome desc

Answer Source
  1. First store your computations back into the $row (it looked like $hourAmount was missing--I assumed that this was $rate, hope I got it right):

    $inGameID = $row['inGameID'];
    $volume = $row['size'];
    $rate = $row['officialCorpRate'];
    $name = $row['name'];
    $hourlyYield = $row['hourlyYield'] = $rate /$volume;
    $hourlyIncome = $row['hourlyIncome '] = $hourlyYield * $rate;

  2. Append the $row to a $rows array: $rows[] = $row;

  3. When you have all the $rows, use a custom comparison function to sort the $rows array.

    User-defined comparison:

    function cmp($a, $b)  
        if ($a['hourlyIncome'] == $b['hourlyIncome']) {  
            return 0;  
        return ($a['hourlyIncome'] < $b['hourlyIncome']) ? 1 : -1;  
  4. Sort $rows via usort($rows, $comp);

  5. Do a standard loop over $rows to display.

Note: Still seems to me like all of these calculations could have been done on the SQL side.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download