Sorting tables on calculated fields before echoing

Report · Jun 11, 2011

I have a table with a calculated field, based on these two lines:

$sql = 'SELECT *, (`Disk I/O`+`MPEG2-DVD`+`H.264-BR`+`MPE On`) As Total FROM `Personal_data` WHERE 1 ORDER BY (`Disk I/O`+`MPEG2-DVD`+`H.264-BR`+`MPE On`), `Date & Time` ASC LIMIT 0, 500 ';
$result = mysql_query($sql);

However, I need to modify that, because it is possible that MPE On is zero. In that case I need to add a field MPE Off.

I have circumvented this problem with the following code:

while($row = mysql_fetch_array($result))
    {
   if ($row['MPE On']==0) ($row['Total']+=$row['MPE Off']);
    echo "<tr>";
    echo "<td>" . $row['Date & Time'] . "</td>";
    echo "<td>" . $row['Email'] . "</td>";
    echo "<td>" . $row['Computer ID'] . "</td>";
    echo "<td>" . $row['Model CPU'] . "</td>";
    echo "<td>" . $row['Clock speed'] . "</td>";
    echo "<td>" . $row['RAM installed'] . "</td>";
    echo "<td>" . $row['Version'] . "</td>";
    echo "<td>" . $row['Graphics card'] . "</td>";
    echo "<td>" . $row['Total'] . "</td>";
    echo "<td>" . $row['Disk I/O'] . "</td>";
    echo "<td>" . $row['MPEG2-DVD'] . "</td>";
    echo "<td>" . $row['H.264-BR'] . "</td>";
    echo "<td>" . $row['MPE On'] . "</td>";
    echo "<td>" . $row['MPE Off'] . "</td>";
    echo "</tr>";
    }
echo "</table>";

While this works to show the correct number, it messes up the sorting of the table, which is based on ORDER BY Total ASC.

Ideally the calculation should be done in the query, but I can't seem to get that to work. Any suggestions?

On another note, is adding a second calculated field a simple matter of adding it to the query, separated by another comma?

Report · Jun 11, 2011

I am thinking the way I would approach it is this

if($row['MPE On'] >0){

$sql = 'SELECT *, (`Disk I/O`+`MPEG2-DVD`+`H.264-BR`+`MPE On`) As Total FROM `Personal_data` WHERE 1 ORDER BY (`Disk I/O`+`MPEG2-DVD`+`H.264-BR`+`MPE On`), `Date & Time` ASC LIMIT 0, 500 ';
$result = mysql_query($sql);

}else if($row['MPE On'] <0){

$sql = 'SELECT *, (`Disk I/O`+`MPEG2-DVD`+`H.264-BR`+`MPE On`) As Total FROM `Personal_data` WHERE 1 ORDER BY (`Disk I/O`+`MPEG2-DVD`+`H.264-BR`+`MPE Off`), `Date & Time` ASC LIMIT 0, 500 ';
$result = mysql_query($sql);

}

Have two queries allowing one to execute if the parameter is met.

Gary

Report · Jun 11, 2011

Gary,

I haven't tried it yet, but I have some problems with the logic.

I have connected to my database, but not yet made any query to get the data. How can I start with a statement like:

if($row['MPE On'] >0){

at a moment where I have not yet gotten data from the database. Logically I would assume you first have to get the data, making a query, before you can manipulate the data. Or is this another noob mistake?

I will try it later on. Thanks for helping so much.

Report · Jun 13, 2011

Gary,

I tried your suggestion, but that does not work.

Logic dictates that once you have queried the database and have your results in an array, you do the calculation for each row, like in the code below:

<?php do { ?>
    <tr>
      <td><?php echo $row_Recordset1['Date & Time']; ?></td>
      <td><?php echo $row_Recordset1['Computer ID']; ?></td>
      <td><?php echo $row_Recordset1['Motherboard']; ?></td>
      <td><?php echo $row_Recordset1['Model CPU']; ?></td>
      <td><?php echo $row_Recordset1['Phys_Cores']; ?></td>
      <td><?php echo $row_Recordset1['Clock speed']; ?></td>
      <td><?php echo $row_Recordset1['RAM installed']; ?></td>
      <td><?php echo $row_Recordset1['Version']; ?></td>
      <td><?php echo $row_Recordset1['Graphics card']; ?></td>
      <td><?php if ($row_Recordset1['MPE On']==0) ($row_Recordset1['Total']+=$row_Recordset1['MPE Off']);
                        echo $row_Recordset1['Total']; ?></td>
      <td><?php echo $row_Recordset1['RPI']; ?></td>
      <td><?php echo $row_Recordset1['Disk I/O']; ?></td>
      <td><?php echo $row_Recordset1['MPEG2-DVD']; ?></td>
      <td><?php echo $row_Recordset1['H264-BR']; ?></td>
      <td><?php echo $row_Recordset1['MPE On']; ?></td>
      <td><?php echo $row_Recordset1['MPE Off']; ?></td>
    </tr>
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

However, by echoing you do not have the table sorted on Total, because that value can change when processed. It may be better to perform the calculation on each row and store it in a new array and sort the new array on column 'Total' before echoing that array to the screen. The question is HOW?

Report · Jun 13, 2011

Harm Millaard wrote:
While this works to show the correct number, it messes up the sorting of the table, which is based on ORDER BY Total ASC.
Ideally the calculation should be done in the query, but I can't seem to get that to work. Any suggestions?
On another note, is adding a second calculated field a simple matter of adding it to the query, separated by another comma?

To answer your last question first, yes.

There are two ways to deal with your other problem:

Use the loop to store the results in a series of arrays, and use array_multisort() to resort them according to the totals.
Use a stored procedure (available from MySQL 5.0) with conditional logic.

I have never used stored procedures, so can't help a great deal there. The principle of the first solution looks like this:

$date = array();
$compID = array();
$total = array();
while($row = mysql_fetch_array($result)) {
    if ($row['MPE On']==0) ($row['Total']+=$row['MPE Off']);
    $date[] = $row['Date & Time'];
    $compID[] = $row['Computer ID'];
    $total[] = $row['Total'];
    // and so on
}
array_multisort($total, $date, $compID, [$other_results]);
for ($i = 0; $i < mysql_num_rows($result); $i++) {
    echo '<tr>';
    echo '<td>' . $total[$i] . '</td>';
    // and so on
{

Report · Jun 13, 2011

David,

Thanks for your suggestion, I will try it out tomorrow as it is now nearly midnight over here.

Expanding on my question, consider the following scenario:

Once I have my table sorted on Total, I need to add another calculated field and the calculation is rather difficult for a complete noob like me.

The top row of my sorted table shows figures like 129 (for Total), 59, 20, 45 and 5. Based on these 5 figures, I need to calculate - using variable weights for the last 4 figures - a new figure that is added to the table, but that calculation needs to be done on the basis of the top row and then applied to all consecutive rows and displayed in the table.

The thought behind this is the Relative Performance Index:

Total Time:

The Total Time is the sum total of the individual test scores, where only the lowest of the MPE scores is counted, as shown in the CPU / GPU Result column.

Relative Performance Index:

To avoid a heavy impact of any single test on the total time, the four test results each have a performance weight attached and each result is normalized against the top ranking machine. In this way each test has the same impact on the RPI, despite the sometimes large differences in measured speed.

On a properly balanced system, both the Total Time and the Relative Performance Index will show around the same distance from the top machine. So a Total Time slower by 20% and a RPI of 120 means well balanced. This shows in each test having about the same color.

Color grading and legends:

If test results on the same line have different colors and different legends (D9, Q3, Med, Q1 or D1—descending order) that is a good reason to investigate the cause. If a single test has a significantly different color than the other tests, it means that on that specific test the system performs either remarkably better (more towards blue) or remarkably worse (more towards red) than the other tests.

See: http://ppbm5.com/Test.html

This may sound challenging to you, it is daunting for me, so any help is appreciated.

PS: I checked and am still using MySQL 5.0, so stored procedures are out. I'll try the array multisort instead.

Report · Jun 14, 2011

David,

Just to let you know, I figured out a way to solve this issue in the query itself.

I deleted the statement in the loop:

/* if ($row_Recordset1['MPE On']==0) ($row_Recordset1['Total']+=$row_Recordset1['MPE Off']); */

and changed the query to:

SELECT *, `Disk I/O`+`MPEG2-DVD`+`H264-BR`+IFNULL(`MPE On`,`MPE Off`) As Total, `CPU`*`Cores` As Phys_Cores FROM `Personal_data` WHERE 1 ORDER BY (`Disk I/O`+`MPEG2-DVD`+`H264-BR`+IFNULL(`MPE On`,`MPE Off`)), `Date & Time` ASC

All I needed to do was make sure that records where MPE On was set to 0 were changed to NULL

That means I have to figure out a way to import from a .CSV file the last field not in the form of "" which is replaced by 0 (zero) but by NULL.