Display data from SQL database as a topology

Question

i have the following situation:

Site_A   Link_AB   Site_B    Link_BC   Site_C
001      001-002   002       002-003   003
001      001-002   002       002-004   004
001      001-005   005       005-006   006

And i want to display it as:

001      001-002   002       002-003   003
                             002-004   004
         001-005   005       005-006   006

Is there a posibility?

Thanks

Which database engine? Do you use a programming context (PHP? ASP? ColdFusion, Java...)? — trincot, Feb 01 '16 at 22:25
hi, yes.. sorry for not stating that. i am using php and mysql. — George Petre, Feb 01 '16 at 22:27
please see this answer: http://stackoverflow.com/questions/30776904/break-on-instruction-in-mysql-queries — MaxU - stand with Ukraine, Feb 01 '16 at 23:19

score 0 · Answer 1 · answered Feb 01 '16 at 22:32

Although possible in MySQL, this type of processing is usually done in the presentation layer. Why? The meaning of the result set depends entirely on the ordering of the rows and SQL is generally working with unordered sets.

So, if you have a query that returns the table, you can do something. Probably the easiest method uses variables:

select if(@sa = Site_A, '', if(@sa := Site_A, @sa, @sa)) as Site_A,
       if(@l = Link_AB, '', if(@l := Link_AB, @l, @l)) as Link_AB,
       if(@sb = Site_B, '', if(@sb := Site_B, @sb, @sb)) as SIte_B,
       Link_BC, Site_C
from t cross join
     (select @sa := '', @l := '', @sb := '') params
order by t.Site_A, t.Link_AB, t.Site_B;

trincot · Accepted Answer · 2016-02-01T23:12:06.760

You could do this in PHP and leave the SQL simple and standard:

$query = "SELECT   Site_A, Link_AB, Site_B, Link_BC, Site_C 
          FROM     sitelinks
          ORDER BY 1, 2, 3, 4, 5";
$result = $mysqli->query($query) or die($mysqli->error());

// maintain an array with previous values
$previous = array();
echo "<table border=1>";
while ($row = $result->fetch_assoc()) {
    if (!count($previous)) {
        // display column headers
        echo "<tr>";
        foreach($row as $key => $value) {
            echo "<th>" . htmlentities($key) . "</th>";
        }
        echo "</tr>";
        // initialise $previous with empty values for each column:
        $previous = array_fill_keys(array_keys($row), '');
    }
    echo "<tr>";
    foreach($row as $key => $value) {
        // display value only when different from previous for this column:
        echo "<td>".($value !== $previous[$key] ? htmlentities($value) : '')."</td>";
    }
    // copy current values into $previous
    $previous = $row;
    echo "</tr>";
}
echo "</table>";

Output:

thank you very much. this is what i was working on right now actually, to keep the previous row in an array, but you code is cleaner :). thanks — George Petre, Feb 01 '16 at 23:36

Display data from SQL database as a topology

2 Answers2