Grouping data based on their values

Question

I want to group and display data (numbers) comma separated in cells in an html table. I want to group data based on their values. So if there are numbers between 1-20, they would be in the first column like 6,10,11,12,15,17. How can I achieve this easily? Thank you.

My code is like this:

   <table class="tbstyle">
        <tr>
        <th>1-20</th> 
        <th>21-40</th>
        <th>41-60</th>
        <th>61-80</th>
        <th>81-100</th>
        </tr>

        <?php

          include ("config.php");   
          $sql = "SELECT Rpa, Rpb, Rpc, Rpd, Rpe, Rpf, Rpg, Rph, Rpi, Rpj, Rpk, Rpl, Rpm, Rpn, Rpo, Rpp, Rpq, Rpr FROM RpDb";
          $result = $conn->query($sql);
          if ($result->num_rows > 0) {
           while($row = $result->fetch_assoc()) 
           {

//I am stuck in this part.  

           }  
              echo "</table>";

        } else { echo "0 results"; }
        $conn->close();
            ?>   

        </table>

Answer 1

Ok so based on my understanding of your question you want to fetch all numbers(data's) from all of your columns in your database and order them into your table, so this is an example on how to do this:-

...
$num_list = array();
if ($result->num_rows > 0)
   while($row = $result->fetch_assoc()) 
       foreach ($row as $value)
           array_push($num_list, $value);

//sort the numbers
sort($num_list);

//insert them into the table
for($i=1; $i<=100; $i+=20){
    $res = "";
    //store the numbers between these indexes into this string
    foreach($num_list as $value)
        if($value >= $i && $value < $i+20)
            $res .= $value . ", ";
    //remove the end comma
    if(strlen($res) != 0)
        $res = substr($res, 0, -1);
    //echo the data
    echo "<td>$res</td>\n";
}
...

Edit

I don't understand this speaking I want to group and display data (numbers) comma separated in cells because you said in the comment below that you don't want the values comma separated in a single cell, So I try to divide the results into the rows but without separating them by comma or anything else, So let's try to do some changes in my code before inserting for loop:-

...
$all_values = array();

//insert them into the table(old comment)
//insert them into the array(new comment)
for($i=1; $i<=100; $i+=20){
    //insert an array into the array
    array_push($all_values, array());
    //store the numbers between these indexes into this array
    foreach($num_list as $value)
        if($value >= $i && $value < $i+20)
            array_push($all_values[count($all_values)-1], $value);
}

//Getting the count of the longest group of numbers and sorting them
$longet_length = 0;
foreach($all_values as $value){
    sort($value);
    if(count($value) > $longet_length)
        $longet_length = count($value);
}

//Finally insert them into the table
$current_index = 0;
while($current_index < $longet_length){
    echo "<tr>\n";
    foreach($all_values as $value)
        if(count($value) > $current_index)
            echo "<td>{$value[$current_index]}</td>\n";
        else
            echo "<td>Empty</td>\n";
    echo "</tr>\n";

    $current_index++;
}
...

Answer 2

Here's a relatively simple solution using array_reduce:

$rows = array(
    array(1, 30, 40, 22, 12, 14, 55, 68, 91, 80, 99, 23, 63, 61, 83),
    array(8, 17, 59, 14, 93, 31, 57, 91, 29, 38, 54, 47, 28, 12, 15)
    );

// replace this line with your while($row = $result->fetch_assoc()) 
foreach ($rows as $row) {
    echo "<tr>";
    sort($row);
    $groups = array_reduce($row, function ($c, $v) { $c[(int)floor(($v-1) / 20)][] = $v; return $c; }, array());
    for ($i = 0; $i < 5; $i++) {
        echo "<td>" . implode(',', isset($groups[$i]) ? $groups[$i] : array()) . "</td>";
    }
    echo "</tr>\n";
}

Output:

<tr><td>1,12,14</td><td>22,23,30,40</td><td>55</td><td>61,63,68,80</td><td>83,91,99</td></tr> 
<tr><td>8,12,14,15,17</td><td>28,29,31,38</td><td>47,54,57,59</td><td></td><td>91,93</td></tr>

Answer 3

this has been untested and off the cusp, however the base logic of what i think you're trying to achieve is there. Let me know if there's any issues and i'll amend the answer.

$row_nums = array_values($row);
asort($row_nums);

$grouped_scores = [];

foreach($row_nums as $num){

    switch (true) {
      case  ($num <= 20):
        $grouped_scores[1][] = $num;
        break;
      case  ($num > 20 && $num <= 40):
        $grouped_scores[2][] = $num;
        break;
      case  ($num > 40 && $num <= 60):
        $grouped_scores[3][] = $num;
        break;
      case  ($num > 60 && $num <= 80):
        $grouped_scores[4][] = $num;
        break;
      case  ($num > 80):
        $grouped_scores[5][] = $num;
        break;
    }

}

echo '<tr>';

foreach($grouped_scores as $score_array){

    echo '<td>'.implode(',', $score_array).'</td>';
}

echo '</tr>';

Answer 4

Here's a code snippet that does what I think you want to acomplish. There might be quicker ways (with less loops), but it does it's job.

<?php

$borders = [20,40,60,80];
// some test data
$rows[] = ["Rpa" => 30, "Rpb" => 14, "Rpc" => 1, "Rpd" => 24];
$rows[] = ["Rpa" => 41, "Rpb" => 33, "Rpc" => 20, "Rpd" => 79];


$grouprows = []; // we'll need some array to re-structure your rows.
foreach($rows as $row) { // this is your while($row = $result->fetch_assoc())
    $groups = []; // the former columns will be packed into "groups" (1-20, 21-40,..)
    foreach($row as $column => $value) {  // walk through all Rpa, Rpb, ...
        foreach($borders as $i => $border) {   // walk through the borders (<20, <40, <60,..)
            if($value <= $border) {  // if it fits into the current group/border, add it to that group
                $groups[$border][] = "$column: $value";
                break;  // ..and don't look any further
            }
        }
        ksort($groups);  // sort the groups to be ascending
    }
    $grouprows[] = $groups;  // add the just edited row to the main array
}

// actual output
echo "<table border=1>
        <tr>
        <th>1-20</th> 
        <th>21-40</th>
        <th>41-60</th>
        <th>61-80</th>
        <th>81-100</th>
        </tr>";

foreach($grouprows as $row) {
    echo "<tr>\n";
    $colcount = 0;
    foreach($row as $col) {

        if(is_array($col)) {
            echo "\t<td>" . implode(", ",$col) . "</td>\n";
        } else {
            echo "\t<td></td>\n";
        }
        $colcount++;
    }
    // if we haven't filled all column yet (because there were no fitting values), add empty tds
    for($colcount;$colcount<count($borders);$colcount++) {
        echo "\t<td></td>\n";
    }
    echo "</tr>\n"; 
}
echo "</table>";

A snippet: https://3v4l.org/SFPBW

Output:

<table border=1>
        <tr>
        <th>1-20</th> 
        <th>21-40</th>
        <th>41-60</th>
        <th>61-80</th>
        <th>81-100</th>
        </tr>
<tr>
    <td>Rpb: 14,Rpc: 1</td>
    <td>Rpa: 30,Rpd: 24</td>
    <td></td>
    <td></td>
</tr>
<tr>
    <td>Rpc: 20</td>
    <td>Rpb: 33</td>
    <td>Rpa: 41</td>
    <td>Rpd: 79</td>
</tr>
</table>