[RESOLVED] Take query results, calculate and then resort?

Svoboda

Alright, I'm moving from noob to noob apprentice and I've come up with a project I'm trying to do but need a bit of assistance.

I'm pulling data from a couple tables in my database and now I need to perform some calculations on what was pulled out of the database and then need to resort by those calculations.

How would I go about doing this?

<?

$pickem = "SELECT *, abs( entry.pf - results.respf ) AS pfscore, abs( entry.pa - results.respa ) AS pascore FROM entry, 

results, user WHERE entry.userid = user.userid AND entry.season = results.season AND entry.week = results.week";
$result = mysql_query($pickem);

while ($row = mysql_fetch_array ($result)) 
{ 

  $pointsoc = "";

  if ($row['outcome']=$row['resoutcome']) {$pointsoc=5;}
  else {$pointsoc=0;}

  $scorepf = $row['pfscore'];
  $pointspf = "";

  if ($scorepf>=42) {$pointspf=1;}
  elseif ($scorepf<=41 AND $scorepf>=29) {$pointspf=2;}
  elseif ($scorepf<=28 AND $scorepf>=22) {$pointspf=3;}
  elseif ($scorepf<=21 AND $scorepf>=15) {$pointspf=4;}
  elseif ($scorepf<=14 AND $scorepf>=11) {$pointspf=5;}
  elseif ($scorepf<=10 AND $scorepf>=8) {$pointspf=6;}
  elseif ($scorepf<=7 AND $scorepf>=4) {$pointspf=7;}
  elseif ($scorepf<=3 AND $scorepf>=2) {$pointspf=8;}
  elseif ($scorepf>=1) {$pointspf=9;}
  else {$pointspf=10;} 

  $scorepa = $row['pascore'];
  $pointspa = "";

  if ($scorepa>=42) {$pointspa=1;}
  elseif ($scorepa<=41 AND $scorepa>=29) {$pointspa=2;}
  elseif ($scorepa<=28 AND $scorepa>=22) {$pointspa=3;}
  elseif ($scorepa<=21 AND $scorepa>=15) {$pointspa=4;}
  elseif ($scorepa<=14 AND $scorepa>=11) {$pointspa=5;}
  elseif ($scorepa<=10 AND $scorepa>=8) {$pointspa=6;}
  elseif ($scorepa<=7 AND $scorepa>=4) {$pointspa=7;}
  elseif ($scorepa<=3 AND $scorepa>=2) {$pointspa=8;}
  elseif ($scorepa>=1) {$pointspa=9;}
  else {$pointspa=10;} 

  $totalpoints = $pointsoc + $pointspf + $pointspa;

  echo "<tr valign=\"top\" class=\"tablesm\">";
  echo "<td><div><a href=\"user.php?u=\"" . $row['userid'] . "\">" . $row['username'] . "</a></div></td>";
  echo "<td>" . $pointsoc . "</td>";
  echo "<td>" . $pointspf . "</td>";
  echo "<td>" . $pointspa . "</td>";
  echo "<td>" . $totalpoints . "</td>";
  echo "</tr>";

}

?>

Svoboda

So basically I want to take those calculations and then readd them to the record set, then resort by the $totalpoints variable.

Is this possible?

laserlight

Firstly, I notice that you used this branching logic twice:

if ($scorepf>=42) {$pointspf=1;}
elseif ($scorepf<=41 AND $scorepf>=29) {$pointspf=2;}
elseif ($scorepf<=28 AND $scorepf>=22) {$pointspf=3;}
elseif ($scorepf<=21 AND $scorepf>=15) {$pointspf=4;}
elseif ($scorepf<=14 AND $scorepf>=11) {$pointspf=5;}
elseif ($scorepf<=10 AND $scorepf>=8) {$pointspf=6;}
elseif ($scorepf<=7 AND $scorepf>=4) {$pointspf=7;}
elseif ($scorepf<=3 AND $scorepf>=2) {$pointspf=8;}
elseif ($scorepf>=1) {$pointspf=9;}
else {$pointspf=10;}

For one thing, you need not specify both ends of the range. In fact, you could reduce the logic to use an array, and write a function for it:

function computePoints($score)
{
    static $score_mins = array(42, 29, 22, 15, 11, 8, 4, 2, 1);
    for ($i = 0; $i < count($score_mins); ++$i)
    {
        if ($score >= $score_mins[$i])
        {
            return $i + 1;
        }
    }
    return 10; // The default case.
}

$score_mins is declared static since we do not want to build it again on subsequent calls. Now, calculating the points and total points is easy:

$points = array();
while ($row = mysql_fetch_assoc($result))
{
    $pointsoc = ($row['outcome'] == $row['resoutcome']) ? 5 : 0;
    $pointspf = computePoints($row['pfscore']);
    $pointspa = computePoints($row['pascore']);
    $totalpoints = $pointsoc + $pointspf + $pointspa;
    $points[] = array('soc' => $pointsoc,
                      'spf' => $pointspf,
                      'spa' => $pointspa,
                      'total' => $totalpoints,
                      'userid' => $row['userid'],
                      'username' => $row['username']);
}

// Sort by total points.
usort($points, create_function('$a,$b', 'return $a["total"]-$b["total"];'));

// Print out sorted list.
for ($i = 0; $i < count($points); ++$i)
{
    // Print $points[$i]['soc'] etc.
}

Since you want to sort by total points, I used [man]usort/man with [man]create_function/man so that the comparison is done with the total points.

Svoboda

Wow, that is much easier. I think I understand most of it, but the following:

create_function('$a,$b', 'return $a["total"]-$b["total"];')

Can you explain what this does or means? Like, where did $a and $b come from?

laserlight

Well, it is equivalent to the function:

function compareByTotalPoints($a, $b)
{
    return $a["total"]-$b["total"];
}

You would then use this in usort() as:

usort($points, 'compareByTotalPoints');

However, since this function is a one liner that we will only use once, it is easier to write it in place as an anonymous function by using create_function().

Svoboda

Excellent, I can understand that.

From what I just read, usort() will sort them low to high. I will need to use rsort() to go from high total to low total, correct?

Would it be proper coding procedure to just add rsort($points) right after the usort() line or would that be bad habit?

laserlight

From what I just read, usort() will sort them low to high. I will need to use rsort() to go from high total to low total, correct?

No, usort() sorts them according to the comparator function. Change the comparator, change the sort.

The idea is that if the comparator returns a positive value, the first element ($a, in this case) is greater than the second element ($b). If the comparator returns 0, then the two elements are equal. Else (i.e., the comparator returns a negative value), the first element is less than the second element.

So, the expression $a["total"]-$b["total"] indicates that the sort is ascending. If we switch it around, $b["total"]-$a["total"], then the sort becomes descending. As such, we would write:

usort($points, create_function('$a,$b', 'return $b["total"]-$a["total"];'));

Svoboda

Did not know that. I was reading off another tut I found online. I need to be more careful on what I take as the gospel -- it appears I've taken in quite a bit of bad coding habits.

You should really be in the tut business. Your help has been concise and easy to understand for the non programmer.

laserlight

Did not know that. I was reading off another tut I found online. I need to be more careful on what I take as the gospel -- it appears I've taken in quite a bit of bad coding habits.

rsort() is not bad, just that it is not suitable here. You would use rsort() if you wanted to sort a single dimensional array in descending order.

You should really be in the tut business. Your help has been concise and easy to understand for the non programmer.

Thank you, I might consider writing an article for PHP Builder some day, heheh.

Svoboda

laserlight wrote:
rsort() is not bad, just that it is not suitable here. You would use rsort() if you wanted to sort a single dimensional array in descending order.

Thank you, I might consider writing an article for PHP Builder some day, heheh.

I was thinking more of a book... I know I'd buy it. The PHP books I've purchase have been terribly written and cryptic since they rarely deal with real world coding examples.