Sorting in a php array

Question

I have this code that prints out columns in my DB and adds a column "Profit" for me. Distance is calculated in a complex way and so is done in a loop, while the transformation of distance to "profit" is done as it is printed out.

What I wish to do is print them out in descending order of "profit". I believe (but do not know) that the best way would be to store them in an array and "sort them in there" then print them out from there.

How do I determine what row of the array to stick them in?
How do I sort in an array?
How do I loop through the array so I can't print them back out?

//display results
// now we retrieve the routes from the db
$query = "SELECT * FROM routes ORDER BY `id`;";

// run the query. if it fails, display error
$result = @mysql_query("$query") or die('<p class="error">There was an unexpected error grabbing routes from the database.</p>');

?>
<tr>
   <td style="background: url(http://www.teamdelta.byethost12.com/barbg.jpg) repeat-x top;">
      <center><b><font color="#F3EC84">»Matches«</font></b></center>
   </td>
</tr>

<tr>
<td style="background: #222222;">

</font>
<table border="0" width="100%"><tr>


<td width="10%"><b><center><b>Player</b></center></b></td>
<td width="10%"><center><b>Base</b></center></td>
<td width="10%"><b>Location</b></td>
<td width="5%"><b>Econ</b></td>
<td width="10%"><b>Distance</b></td>
<td width="10%"><center><b>Profit cred./h</b></center></td>
<td width="40%"><b>Comment</b></td>
<td width="5%"><align="right"><b>Delete</b></align></td>

</tr> 

<?

// while we still have rows from the db, display them
while ($row = mysql_fetch_array($result)) {

    $dname = stripslashes($row['name']);
    $dbase = stripslashes($row['base']);
    $dlocation = stripslashes($row['location']);
    $dx = stripslashes($row['x']);
    $dy = stripslashes($row['y']);
    $dgalaxy = stripslashes($row['galaxy']);
    $dplanet = stripslashes($row['planet']);
    $dcomment = stripslashes($row['comment']);
    $did = stripslashes($row['id']);
    $decon = stripslashes($row['econ']);

    $distance = -1 ;//default

    //distance calc
    if($dgalaxy == $galaxy)
    {//interstellar
       if(($dx == $x) && ($dy == $y))
       {//inter planitary
          if ((floor($planet/10)*10) == (floor($dplanet/10)*10))
          {// intra planitary loonar
             $distance = abs(fmod($planet,10)-fmod($planet,10))*0.1;  
          }
          else
          {// inter planitary
             $distance = abs((floor($planet/10)*10)-(floor($planet/10)*10))*0.2;  
          }
       }else
       {//interstllar
          $distance = round(Sqrt(pow(($dx-$x),2)+pow(($dy-$y),2)));//interstllar
       }
    }
    else
    {//intergalatic
       if ((floor($galaxy/10)*10) == (floor($dgalaxy/10)*10)) 
       {//intra galactic cluster
          $distance = abs(($galaxy-$dgalaxy))*200;
       }     
       else
       {//inter galactic cluster
          if ($galaxy < $dgalaxy)
          {//anti clockwise inter galactic cluster
             $distance = (((9-fmod($galaxy,10))*200)+2000+(fmod($dgalaxy,10)*200));
          }
          else
          {//clockwise inter galactic cluster
             $distance = (((fmod($galaxy,10))*200)+2000+(fmod(9-$dgalaxy,10)*200));
          }
       }
    }

    echo('<tr>
    <td width=\'20px\'><center>('.$dname.')</center></td>
    <td><center>'.$dbase.'</center></td>
    <td><a href="http://delta.astroempires.com/map.aspx?loc='.$dlocation.'"&gt;'.$dlocation.'&lt;/a&gt;&lt;/td&gt;
    <td>'.$decon.'</td><td>'.$distance.' </td>
    <td>'.round(Sqrt(min($decon,$econ))*(1+Sqrt($distance)/75+Sqrt($players)/10)).'</td>
    <td>['.$dcomment.']</td>
    <td><a href=deleterouteconfirm.php?id='.$did.'>Delete</a></td>
    </tr>');
}
?></table><!--display results table-->

Answer 1

You probably want something like uasort

Answer 2

You are getting your data from MySQL. Why not sort your results directly from the query?

$query = "SELECT * FROM routes ORDER BY `profit` DESC, `id`;";

EDIT: Re-read your question, profit isn't a field, but you might want to populate a table with your profit values instead of re-calculating it each time.

EDIT 2: Or, belay your output, calculate your profit, put everything in an array, and then use the following:

$resultArray; //Your array with all your rows plus a profit key-value pair.

$sortedArray = array_msort($resultArray, array('profit'=>SORT_DESC));

// array_msort by cagret at gmail dot com
function array_msort($array, $cols)
{
    $colarr = array();
    foreach ($cols as $col => $order) {
        $colarr[$col] = array();
        foreach ($array as $k => $row) { $colarr[$col]['_'.$k] = strtolower($row[$col]); }
    }
    $params = array();
    foreach ($cols as $col => $order) {
        $params[] =& $colarr[$col];
        $params = array_merge($params, (array)$order);
    }
    call_user_func_array('array_multisort', $params);
    $ret = array();
    $keys = array();
    $first = true;
    foreach ($colarr as $col => $arr) {
        foreach ($arr as $k => $v) {
            if ($first) { $keys[$k] = substr($k,1); }
            $k = $keys[$k];
            if (!isset($ret[$k])) $ret[$k] = $array[$k];
            $ret[$k][$col] = $array[$k][$col];
        }
        $first = false;
    }
    return $ret;

}

Answer 3

I think the easiest solution to implement is going to be a double-pass over your database results.

The first pass will to generate the "distance" and "profit" values for each row and store these rows into an array that we'll sort.

The second pass will just simply loop over the array created in the first pass and display them, after it has been properly sorted and escaped for output.

<?php
//display results
// now we retrieve the routes from the db
$query = "SELECT * FROM routes ORDER BY `id`;";

// run the query. if it fails, display error
$result = @mysql_query( "$query" ) or die( '<p class="error">There was an unexpected error grabbing routes from the database.</p>' );

?>
<tr>
    <td
     style="background: url(http://www.teamdelta.byethost12.com/barbg.jpg) repeat-x top;">
    <center><b><font color="#F3EC84">»Matches«</font></b></center>
    </td>
</tr>

<tr>
    <td style="background: #222222;"></font>
    <table border="0" width="100%">
     <tr>


      <td width="10%"><b>
      <center><b>Player</b></center>
      </b></td>
      <td width="10%">
      <center><b>Base</b></center>
      </td>
      <td width="10%"><b>Location</b></td>
      <td width="5%"><b>Econ</b></td>
      <td width="10%"><b>Distance</b></td>
      <td width="10%">
      <center><b>Profit cred./h</b></center>
      </td>
      <td width="40%"><b>Comment</b></td>
      <td width="5%"><align="right"><b>Delete</b></align></td>

     </tr> 

<?

// while we still have rows from the db, display them
$resultSet = array();
while ( $row = mysql_fetch_array( $result ) )
{

  $dname = stripslashes( $row['name'] );
  $dbase = stripslashes( $row['base'] );
  $dlocation = stripslashes( $row['location'] );
  $dx = stripslashes( $row['x'] );
  $dy = stripslashes( $row['y'] );
  $dgalaxy = stripslashes( $row['galaxy'] );
  $dplanet = stripslashes( $row['planet'] );
  $dcomment = stripslashes( $row['comment'] );
  $did = stripslashes( $row['id'] );
  $decon = stripslashes( $row['econ'] );

  $distance = -1; //default


  //distance calc
  if ( $dgalaxy == $galaxy )
  { //interstellar
    if ( ( $dx == $x ) && ( $dy == $y ) )
    { //inter planitary
      if ( ( floor( $planet / 10 ) * 10 ) == ( floor( $dplanet / 10 ) * 10 ) )
      { // intra planitary loonar
        $distance = abs( fmod( $planet, 10 ) - fmod( $planet, 10 ) ) * 0.1;
      } else
      { // inter planitary
        $distance = abs( ( floor( $planet / 10 ) * 10 ) - ( floor( $planet / 10 ) * 10 ) ) * 0.2;
      }
    } else
    { //interstllar
      $distance = round( Sqrt( pow( ( $dx - $x ), 2 ) + pow( ( $dy - $y ), 2 ) ) ); //interstllar
    }
  } else
  { //intergalatic
    if ( ( floor( $galaxy / 10 ) * 10 ) == ( floor( $dgalaxy / 10 ) * 10 ) )
    { //intra galactic cluster
      $distance = abs( ( $galaxy - $dgalaxy ) ) * 200;
    } else
    { //inter galactic cluster
      if ( $galaxy < $dgalaxy )
      { //anti clockwise inter galactic cluster
        $distance = ( ( ( 9 - fmod( $galaxy, 10 ) ) * 200 ) + 2000 + ( fmod( $dgalaxy, 10 ) * 200 ) );
      } else
      { //clockwise inter galactic cluster
        $distance = ( ( ( fmod( $galaxy, 10 ) ) * 200 ) + 2000 + ( fmod( 9 - $dgalaxy, 10 ) * 200 ) );
      }
    }
  }
  $row['distance'] = $distance;
  $row['profit'] = round( Sqrt( min( $decon, $econ ) ) * ( 1 + Sqrt( $distance ) / 75 + Sqrt( $players ) / 10 ) );
  $resultSet[] = $row;
}

//  Perform custom sort
usort( $resultSet, 'sorter' );
function sorter( $a, $b )
{
  if ( $a['profit'] == $b['profit'] ) return 0;
  return ( $a['profit'] < $b['profit'] ) ? -1 : 1;
}

//  Switch to "descending"
array_reverse( $resultSet );

//  Output escape the values
$safeForHtml = array_map( 'htmlspecialchars', $resultSet );

foreach( $safeForHtml as $row )
{
  echo ( '<tr>
                <td width=\'20px\'><center>(' . $row['name'] . ')</center></td>
                <td><center>' . $row['base'] . '</center></td>
                <td><a href="http://delta.astroempires.com/map.aspx?loc=' . $row['location'] . '">' . $row['location'] . '</a></td>
                <td>' . $row['econ'] . '</td>
                <td>' . $row['distance'] . ' </td>
                <td>' . $row['profit'] . '</td>
                <td>[' . $row['comment'] . ']</td>
                <td><a href=deleterouteconfirm.php?id=' . $row['id'] . '>Delete</a></td>
                </tr>' );
}
?>
</table>
    <!--display results table-->