Sort an array and echo values out in descending order from a while loop?

PHP
1

I have done research but cannot find an exact working method of doing this. I tried jquery plugins and of course following stack overflow solutions sort rows of HTML table that are called from MySQL I also tried asking stackoverflow…but you can imagine how that went.

  1. I query the two tables in the same database using inner join

    $sql = “SELECT users.empid, users.empfirst, users.emplast,
    prodata.monval, prodata.montar,
    prodata.tueval, prodata.tuetar,
    prodata.wedval, prodata.wedtar,
    prodata.thuval, prodata.thutar,
    prodata.frival, prodata.fritar
    FROM users INNER JOIN prodata ON
    users.empid = prodata.empid WHERE users.level = 2” ;
    $result = mysqli_query($db, $sql);
    // Set total variables to 0 for later use
    $totalscore = 0;
    $totaltarget = 0;

  2. then I echo these values out into a basic table within a while loop and sum values for $totalscore & $totaltarget

    while($res = mysqli_fetch_array($result))
    {
    //Add all
    echo “”;
    echo “”.$res[‘empid’].“”;
    echo “”.$res[‘empfirst’].“”;
    echo “”.$res[‘emplast’].“”;
    $totalscore = $res[‘monval’] + $res[‘tueval’] + $res[‘wedval’] + $res[‘thuval’] + $res[‘frival’];
    $totaltarget = $res[‘montar’] + $res[‘tuetar’] + $res[‘wedtar’] + $res[‘thutar’] + $res[‘fritar’];
    echo “”.$totalscore.“”; //Score total
    echo “”.$totaltarget.“”; //Target total
    }

How can I order the table contents to show in descending order, $totalscore (numeric). Is it better to do this through PHP or through the query?

2

I would get the totals through the query and then you can order them right in the query. I think it would be lot less complicated.

1 Like
3

I understand, would this be better done before or after inner join?

4

When I first started I used to put all of my data into “one big table with everything”. The queries were relatively simple and I would then use PHP to do all sorts of filtering, ordering, type juggling, string formatting, date conversions, etc. It “worked” and as long as the amount of data I was working with was minimal I was blissfully ignorant of the inefficiency.

Anyway, it looks like you are SELECTing individual values not to use them individually, but so that PHP can add them together and display the total.

I would try using the MySQL function SUM in the SELECT instead. eg.

SUM(val1, val2, val3, val4) AS val_total

As with most things code, you should look at documentation to see what happens with the unexpected. eg. what if val2 is NULL, is the whole thing binned or does the NULL get treated as though it were a zero.

2 Likes
5

i understand about null values, you just add ISNULL (#,0), however what if i wanted to add these columns together for each row in the table

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.