Keep the data GROUPED without being separated by the ORDER

Question

In my tables here

           names                           countries                       scores
[id  --  FirstName  -- LastName]    [id --  cid  --  Country]      [id   --  sid  --  Score]
[1   --   FName1    --  LName1 ]    [1  --   1   --  USA    ]      [1    --   1   --      5] 
[2   --   FName2    --  LName2 ]    [2  --   1   --  France ]      [2    --   1   --      6]
[3   --   FName3    --  LName3 ]    [3  --   1   --  Germany]      [3    --   2   --      7]
[4   --   FName1    --  LName1 ]    [4  --   2   --  Germany]      [4    --   2   --      8] 
                                    [5  --   2   --  Czech  ]      [5    --   2   --      2]
                                    [6  --   3   --  Germany]      [6    --   4   --     12]
                                    [7  --   4   --  Czech  ]      [7    --   3   --     15]

I get the data and make it in this structer

.Container div {
    border: 1px solid blue;
    margin: 10px;
}
.Container div div {
    border: 1px solid red;
    margin: 10px;
}

<div>
    <div id="Master German">German</div>
    <div id="German">
        <div class="Container">
           <div id='FName2 German'>
                <div>FName2</div>
                <div>LName2</div>
            </div>
            <div id='FName1 German'>
                <div>FName1</div>
                <div>LName1</div>
            </div>
            <div id='FName3 German'>
                <div>FName3</div>
                <div>LName3</div>
            </div>
        </div>
    </div>

    <div id="Master France">France</div>
    <div id="France">
        <div class="Container">
            <div id='FName1 France'>
                <div>FName1</div>
                <div>LName1</div>
            </div>
        </div>
    </div>

   <div id="Master Czech">Czech</div>
    <div id="Czech">
        <div class="Container">
            <div id='FName4 Czech'>
                <div>FName4</div>
                <div>LName4</div>
            </div>
            <div id='FName2 Czech'>
                <div>FName2</div>
                <div>LName2</div>
            </div>
        </div>
    </div>
    
    <div id="Master USA">USA</div>
    <div id="USA">
        <div class="Container">
            <div id='FName1 USA'>
                <div>FName1</div>
                <div>LName1</div>
            </div>
        </div>
    </div>
</div>

using the following query

SELECT
    a.FirstName,
    a.LastName,
    c.Country,
    SUM(b.Scores) AS Score
FROM names a
INNER JOIN countries c
    ON a.id = c.cid
LEFT JOIN scores b
    ON a.id = b.sid
WHERE a.id = :id
GROUP BY a.id
ORDER BY Score ASC

Now my problem appears when for example id[1] and id[4] has the same FName and LName but the scores are different

Since i order them by Score and id[4] > id[1]

the structer supposed to be like

Germany: 
FName2 -- LName2 [Score: 15]
FName3 -- LName3 [Score: 15]
FName1 -- LName1 [Score: 11]

Czech: 
FName2 -- LName2 [Score: 15]
FName1 -- LName1 [Score: 12]

But what i get is

Germany: 
FName2 -- LName2 [Score: 15]
FName3 -- LName3 [Score: 15]

Czech: 
FName2 -- LName2 [Score: 15]
FName1 -- LName1 [Score: 12]

Germany: 
FName1 -- LName1 [Score: 11]

I tried all the possible group by but nothing worked there is always an extra country div

My php code is

$pre = null;
echo "<div>";
$end = "";
while ($row = $stmt->fetch()) {
    $FName = $row['FirstName'];
    $LName = $row['LastName'];
    $Country = $row['Country'];

    if ($Country != $pre) {
        echo $end;
        echo "<div id='Master $Country'>$Country</div>";
        echo "<div id='$Country'>";
    }
    $end = "</div>
</div>";
    $pre = $Country;

    echo "
<div id='$FName USA'>
    <div>$FName</div>
    <div>$LName</div>
</div>";
}
echo $end;
echo "</div>";

FYI: it's a good task for LINQ's GroupBy(). Look into some [PHP's LINQ](https://stackoverflow.com/questions/5792388/is-there-something-in-php-equivalent-to-linq-in-c) when you have free time. — Vanity Slug - codidact.com, Sep 27 '17 at 16:36
@alex Is it a method for SQL like AJAX for JavaScript or entirly another language that i have to learn? I read about it now, But really don't understand what is it — AXAI, Sep 27 '17 at 16:53
I come from C# background, so I am not sure how that PHP LINQ stuff works exactly (but I hope it is not much different from C# LINQ). Essentially LINQ allows you to query objects. Your rows have to be grouped into array of arrays, and LINQ should make a easier. — Vanity Slug - codidact.com, Sep 27 '17 at 17:35
I quick example of what LINQ can do (sorry, using C#): [here](https://ideone.com/tV5A7W). (LINQ is one of the main reasons I am sticking with C#) — Vanity Slug - codidact.com, Sep 27 '17 at 17:58
direct link to documentation of [GroupBy() of YaLinqo](https://athari.github.io/YaLinqo/docs/classes/YaLinqo.Enumerable.html#method_groupBy). I would try to learn this first. — Vanity Slug - codidact.com, Sep 27 '17 at 18:06

score 1 · Accepted Answer · answered Sep 27 '17 at 21:46

Order by country, then score:

SELECT
    a.FirstName,
    a.LastName,
    c.Country,
    SUM(b.Scores) AS Score
FROM names a
INNER JOIN countries c
    ON a.id = c.cid
LEFT JOIN scores b
    ON a.id = b.sid
WHERE a.id = :id
GROUP BY a.id
ORDER BY c.Country, Score ASC

Keep the data GROUPED without being separated by the ORDER

1 Answers1