[SOLVED] How to get data from database and display to table row span in php

Issue

I want to display dynamic data to HTML table row span,I’m fetching data from database, all the data is coming but I need to this dynamic data to HTML table format,below I given my HTML format and my php code please check and help I’m new in PHP.

In below Format I Need how to make:

cycle   event        dateassigned       datecompleted

        inventory     21-3-2022         22-3-2022
1       inspection    21-3-2022         22-3-2022
        retest        21-3-2022         22-3-2022
        repairtest    21-3-2022         22-3-2022

        inventory     22-3-2022         22-3-2022
2       inspection    22-3-2022         22-3-2022
        retest        22-3-2022         22-3-2022  
        repairtest    22-3-2022         22-3-2022

Query:

 $query = "select bm.bridge_id, bm.status, bm.transaction_datetime,bm.review,bm.cycle,bm.event from bridge_details bm where bm.bridge_id = '$bridge_id'";
    
                        
                        $res = mysqli_query($maindb_handle, $query) or die(__FUNCTION__." Query Failed ".  "<br>($maindb_handle)<br>MySQL Error[".mysqli_error($maindb_handle)."]");
    
    
                        $row = mysqli_fetch_assoc($res);



    if ($row['bridge_id'] == '') {
                    ?>
                            <div align="center"><font color="red">Bridge Doesn't Existst - <?=$bridge_id?> </font></div>
                    <?php   } else { ?>
                        <table border="2" align="center" width="80%">
                            <tr>
                                <th style="line-height:15px; font-size:15px; color:#984806; font-family:Arial Rounded MT Bold;"><b>Cycle</b></th>
                                <th style="line-height:15px; font-size:15px; color:#984806; font-family:Arial Rounded MT Bold;"><b>Type</b></th>
                                <th style="line-height:15px; font-size:15px; color:#984806; font-family:Arial Rounded MT Bold;"><b>Date Assined</b></th>
                                <th style="line-height:15px; font-size:15px; color:#984806; font-family:Arial Rounded MT Bold;"><b>Date Completed</b></th>
                                
                            </tr>
    
    <?php
    
                        echo '<tr>';
                        for ($i = 0; $i <=1; $i++){
                          echo '<td rowspan="4">'.$row['cycle'].'</td>';
                          echo '<td>'.$row['event'].'</td>';
                          echo '<td>'.$row['transaction_datetime'].'</td>';
                          echo '<td>'.$row['review'].'</td>';
                        }
                        echo '</tr>';
    
                        
    
                        ?> 
    </table>
    <?php } ?>

 

Solution

you need to return the array from the database to the $data variable

$handler = new mysqli($hostname, $username, $password, $database);

$bridge_id = 1;

$query = "select bm.bridge_id, bm.status, bm.transaction_datetime,bm.review,bm.cycle,bm.event from bridge_details bm ";
$result = $handler->query($query, MYSQLI_STORE_RESULT);

$data = array();

//rows
if ($result !== FALSE) {
    $i = 0;
    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
        $data[$i] = $row;
        $i++;
    }
    $result->close();
} else {
    trigger_error('Error: ' . $handler->error . '<br />Error No: ' . $handler->errno . '<br />' . $query);
    exit();
}

next you need to redefine the array

<?php 
    $rows =[];   
    foreach($data as $k =>$v){
        $rows[$v['cycle']][] = $v; 
    }
 ?>

and in the loop output this array

    
    <?php
        foreach($rows as $n =>$val){
            foreach($val as $nom =>$value){
                echo '<tr>';
                    if($nom == 0){
                        echo '<td rowspan="4">'.$n.'</td>';
                    }
                    echo '<td>'.$value['event'].'</td>';
                    echo '<td>'.$value['transaction_datetime'].'</td>';
                    echo '<td>'.$value['review'].'</td>';
                echo '</tr>';
            }
        }
    
    ?> 

Answered By – Pavel12398

Answer Checked By – Terry (BugsFixing Volunteer)