php – Database value display in multiple table cell based on category

<table class="table">
    <tr>
        <td>Sr.</td>    
        <td>Body</td> 
        <td>Material</td>
        <td>Elastomer</td>                                       
    </tr>  
    <?php for($i=1;$i<=$quantity_sum_show;$i++){?>
        <tr>
            <td><?php echo $i; ?></td>
            <td></td>
            <td></td>
            <td></td>
        </tr>
    <?php }?>
</table> 

Let us assume the variable $quantity_sum_show value as 8.

From the above table, Body,Material,Elastomer row field values are getting from database. I need to display the values based on the category in the table cells. Below is the sql query to fetch hno_count, hno, but confused how to display it in the table based on the category field.

Sql query:

select hno_count, hno, category from hnos

Sql Result:

| hno_count | hno   | category |
| -------- | -------| -------- |
| 4        | hno1   | Body     |
| 1        | hno2   | Body     |
| 3        | hno3   | Body     |
| 3        | Mhno1  | Material |
| 2        | Ehno1  | Elastomer|

Example: In the above result table as we can see, there are three rows with 4,1,3 respectively for the Body category. What I’m expecting to display in the table is for the starting 4 rows of Body field, the heat no(hno1) need to display. For fifth row(hno2) need to display and for the next 3 rows(hno3) need to display.

FYI, it’s not mandatory that sum of all hno_count should be the $quantity_sum_show value, but the sum of hno_count will not be greater than the $quantity_sum_show value.

Expected Output:

| Sr. | Body H No | Material | Elastomer |
| --- | ----------| --------| ---------  |
| 1   | hno1      | Mhno1   |  Ehno1     |
| 2   | hno1      | Mhno1   |  Ehno1     |
| 3   | hno1      | Mhno1   |            |
| 4   | hno1      |         |            |
| 5   | hno2      |         |            |
| 6   | hno3      |         |            |
| 7   | hno3      |         |            |
| 8   | hno3      |         |            |

Answer

From your question your input data looks like this:

$raw_data = [
    [ 4, "hno1",  "Body"],
    [ 1, "hno2",  "Body"],
    [ 3, "hno3",  "Body"],
    [ 3, "Mhno1", "Material"],
    [ 2, "Ehno1", "Elastomer"]
];

So the first thing we need to do is expand that data into a format which we can iterate over…

$table_data = [];

foreach($raw_data as $item){
    for($i = 0; $i < $item[0]; $i++){
        $table_data[$item[2]][] = $item[1];
    }
}


// Output of: print_r($table_data);
/*
Array
(
    [Body] => Array
        (
            [0] => hno1
            [1] => hno1
            [2] => hno1
            [3] => hno1
            [4] => hno2
            [5] => hno3
            [6] => hno3
            [7] => hno3
        )

    [Material] => Array
        (
            [0] => Mhno1
            [1] => Mhno1
            [2] => Mhno1
        )

    [Elastomer] => Array
        (
            [0] => Ehno1
            [1] => Ehno1
        )

)
*/

Next we need to know how many rows of data there are…

$row_count = count(max($table_data));

Then we can loop through the result set and output the data in the format intended…

for($i = 0; $i < $row_count; $i++){
    $row = $i + 1;
    echo "<tr><td>{$row}</td>";
    foreach($table_data as $column){
        $field = $column[$i] ?? NULL;
        echo "<td>{$field}</td>";
    }
    echo "</tr>n";
}

Output

<tr><td>1</td><td>hno1</td><td>Mhno1</td><td>Ehno1</td></tr>
<tr><td>2</td><td>hno1</td><td>Mhno1</td><td>Ehno1</td></tr>
<tr><td>3</td><td>hno1</td><td>Mhno1</td><td></td></tr>
<tr><td>4</td><td>hno1</td><td></td><td></td></tr>
<tr><td>5</td><td>hno2</td><td></td><td></td></tr>
<tr><td>6</td><td>hno3</td><td></td><td></td></tr>
<tr><td>7</td><td>hno3</td><td></td><td></td></tr>
<tr><td>8</td><td>hno3</td><td></td><td></td></tr>


// Without markup

1  hno1  Mhno1  Ehno1
2  hno1  Mhno1  Ehno1
3  hno1  Mhno1
4  hno1  
5  hno2  
6  hno3  
7  hno3  
8  hno3