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