Sort database query result set rows by complex calculated value

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.'">'.$dlocation.'</a></td>
    <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-->

Peter Bailey · Accepted Answer · 2009-07-21 16:26:27Z

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-->

Thanks for typing it all out and all, but it does not seem to work, it loops through the final output the right amount of time but prints blanks.. se it in action here teamdelta.byethost12.com/postroute.php put D03:53:32:21 as location and econ as a number the others can just be random strings comment and players are optional.
i found the issue. i chopped out "$safeForHtml = array_map( 'htmlspecialchars', $resultSet );" and printed from $resultSet and it prints...but there not in order...
ok changed sorter and removed array reverse and its working :) thanks

Andrew Moore · Accepted Answer · 2009-07-21 16:28:44Z

1

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;

}

edited Jul 21, 2009 at 16:28

answered Jul 21, 2009 at 16:21

Andrew Moore

95.8k30 gold badges167 silver badges175 bronze badges

1 Comment

Daniel F. Thornton Over a year ago

I don't think he knows the profit until it's calculated in the code: round(Sqrt(min($decon,$econ))*(1+Sqrt($distance)/75+Sqrt($players)/10)

Community · Accepted Answer · 2023-11-20 22:45:59Z

0

You probably want something like uasort

edited Nov 20, 2023 at 22:45

CommunityBot

11 silver badge

answered Jul 21, 2009 at 16:18

Luc M

17.5k26 gold badges78 silver badges91 bronze badges

Comments

mickmackusa · Accepted Answer · 2024-10-24 20:57:30Z

This doesn't look like a calculation that should be conducted during read-time, but write-time.

Just before inserting or updating your database table, perform this calculation and store it.

Alternatively create the distance column as a computed column and have it automatically updated upon each row modification.

This will make your query faster and much simpler to manage because you can just ORDER BY distance DESC.

If the above isn't attractive, then fetch all of the results into an array of associative rows, then stuff your complex calculation into an array_map() call, then use array_multisort().

array_multisort(
    array_map(getDistance(...), $resultSet), // get distance for each row
    SORT_DESC,
    $resultSet
);
// now print out the sorted $resultSet

Collectives™ on Stack Overflow

Sort database query result set rows by complex calculated value

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related