在列中放置多个MySQL Row

Question

the problem that i've is, that I have a Table in my Database and want to fetch some of the rows of this table in one column.

For example I've this table names klz:

|-------+-----------+-----+-----|
| ID    | Name      | LNr | LID |
|-------+-----------+-----+-----|
| 1     | 0000_01   | 1   | 16  |
| 2     | 0000_01   | 2   | 35  |
| 3     | 0000_02   | 1   | 16  |
| 4     | 0000_02   | 2   | 35  |
| 5     | 0000_10   | 1   | 18  |
| ..    | ..        | ..  | ..  |
| 297   | 0214_01   | 1   | 23  |
| 298   | 0214_01   | 1   | 66  |
| 299   | 0214_01   | 2   | 24  |
| 300   | 0214_01   | 2   | 67  |
| 301   | 0214_01   | 3   | 26  |
| 302   | 0214_01   | 4   | 28  |
| 303   | 0214_01   | 4   | 69  |
| 304   | 0214_01   | 5   | 30  |
| 305   | 0214_01   | 5   | 70  |
| 306   | 0214_01   | 6   | 31  |
| 307   | 0214_01   | 6   | 71  |
|-------+-----------+-----+-----|

If I fetch this table with a while-loop in PHP I would get the same table.

So what I want to have is a table like this:

|-----------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------|
| Name      | LNr1    | LNr2    | LNr3    | LNr4    | LNr5    | LNr6    | LNr7    | LNr8    | LNr9    | LNr10   |
|-----------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------|
| 0000_01   | 16      | 35      |         |         |         |         |         |         |         |         |
| 0000_02   | 16      | 35      |         |         |         |         |         |         |         |         |
| 0000_10   | 18      |         |         |         |         |         |         |         |         |         |
| 0214_01   | 23 - 66 | 24      | 26 - 68 | 28 - 69 | 30 - 70 | 31 - 71 |         |         |         |         |
|-----------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------|

This table have the column Name and an enumeration of the possible LNr from 1 to 10 as columns. The data inside the LNr columns arethe LIDs from the table above.

My Question is, how can I put in dependency of the Name and the LNr, the LIDs in the right columns? In this table you have listed all Names once and set for all LNr the right LID.

Here is the Code I'm trying to do this with... Until now it works only for the first LNr column, to write the LID in:

<?php
  include "dbconnect.php";

  $klz = mysqli_query($db, "SELECT      *
                            FROM        klz;");  

?>  
  <div class="container-fluid">
  <div class="row">
    <div class="table-responsive">
    <div class="col-xs-12">
    <table id="grid-klz" class="table table-condensed table-hover table-striped">
      <thead>   
        <tr>
          <th> Name </th>
          <th> Leitungsnr 1</th>
          <th> Leitungsnr 2</th>
          <th> Leitungsnr 3</th>
          <th> Leitungsnr 4</th>
          <th> Leitungsnr 5</th>
          <th> Leitungsnr 6</th>
          <th> Leitungsnr 7</th>
          <th> Leitungsnr 8</th>
          <th> Leitungsnr 9</th>
          <th> Leitungsnr 10</th>
        </tr>  
      </thead>

    <tbody>
<?php         
  $name_old = ""; 
  $lnr_old  = "";
  $name     = []; 
  $lnr      = [];
  $lid      = [];

  while($row = mysqli_fetch_array($klz, MYSQL_ASSOC)){
    $name[] = $row['Name'];
    $lnr[] = $row['LNr'];
    $lid[] = $row['LID'];
  }     

  for($i=0; $i <= sizeof($name)-1; $i++){

    if($name[$i] != $name_old){

      echo "<tr>";
        echo "<td>". $name[$i] . "</td> 
";

        if($lnr[$i] != $lnr_old){
          echo "<td>". $lid[$i] . "</td> 
";

          $lnr_old != $lnr[$i];
        }

      echo "</tr>";

      $name_old = $name[$i];
    }
  }    
?>
    </tbody>
  </table>

    </div>
    </div>
  </div>  
  </div>

<?php  
  mysqli_close($db);
?>

I hope you know what I mean and trying to do. If not feel free to ask please. Thank you

Answer 1

Start with this query

SELECT
    Name AS tableRows,
    LNr AS tableCols,
    GROUP_CONCAT(LID ORDER BY LID SEPARATOR ' - ') AS cellValue
FROM klz
GROUP BY 1,2
ORDER BY 1,2;

Now put the results in an array. Also, get every distinct column (LNr) and row (Name) in other arrays.

$ar = array();
$tableRows = array();
$tableCols = array();

while($row = mysqli_fetch_array($klz, MYSQL_ASSOC)){
    $ar[$row['tableRows']][$row['tableCols']] = $row['cellValue'];
    $tableRows[] = $row['tableRows'];
    $tableCols[] = $row['tableCols'];
}

$tableRows = array_unique($tableRows);
$tableCols = array_unique($tableCols);

sort the 2 arrays with rows and columns

sort($tableRows);
sort($tableCols);

foreach row, foreach column, echo value of the multi array

echo "<table>";
echo "<tr><th>Name</th>";

foreach($tableCols as $x){
    echo "<th>LNr$x</th>";
}

echo "</tr>";

foreach($tableRows as $y){
    echo "<tr><td>$y</td>";
    foreach($tableCols as $x){
        echo "<td>" . $ar[$y][$x] . "</td>";
    }
    echo "</tr>";
}
echo "</table>";