I was creating a database for my sister showing which Goalkeepers have the most clean sheets during the Soccer European Championships.

I know that if I want to just display the Goalkeepers in order of amount of clean sheets then my SQL code would read:

SELECT * FROM gk ORDER BY gkClean DESC LIMIT 10

but then my sister said she also wanted to deduct goals conceeded from overall total.

Here is my code:

echo "<table>";
    echo "<tr>";
    echo "<td width='15%'><strong>Position</strong></td>";
    echo "<td width='40%'><strong>Name</strong></td>";
    echo "<td width='15%'><strong>Clean Sheets</strong></td>";
    echo "<td width='15%'><strong>Conceeded</strong></td>";
    echo "<td width='15%'><strong>Total</strong></td>";
    echo "</tr>";
    $numberCount = 0;
    $gkSQL = "SELECT * FROM gk LIMIT 10";
    $gkResult = $glob->query($gkSQL) or die(mysqli_error());
    if($gkResult){
        while($gkRow = $gkResult->fetch_object()){
            echo "<tr>";
            echo "<td>";
            $numberCount = $numberCount + 1;
            echo $numberCount;
            echo "</td>";
            echo "<td>";
            echo $gkRow->gkName;
            echo "</td>";
            echo "<td>";
            echo $gkRow->gkClean;
            echo "</td>";
            echo "<td>";
            echo $gkRow->gkPoints;
            echo "</td>";
            echo "<td>";
            $gkTotal = $gkRow->gkClean - $gkRow->gkPoints;
            echo $gkTotal;
            echo "</td>";
            echo "</tr>";
        }
    }
    echo "</table>";

As you can see, my code pulls data from the database named gk then dynamically deducts conceeded from cleansheets, I would then like to display the table in order of total, is this possible?

Move the calculation of total to the query and your problem is solved.

SELECT *, gkClean - gkPoints AS gkTotal FROM gk ORDER BY gkTotal DESC LIMIT 10

Wow, that looks so easy! Thank you for takin the time to show me how to do it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.