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

Question

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 } ?>

What is the problem exactly? Please describe the intended behavior of your code. — kmoser, Mar 22 '22 at 04:36
var_dump($data) inside your while to check what is returning and then echo according to column returned. — Aqib Javed, Mar 22 '22 at 05:09
Please be warned that the given `SELECT` query is widely open for SQL injection. Have a look at prepared statements to avoid getting hacked — Nico Haase, Mar 22 '22 at 07:20
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Mar 22 '22 at 09:27

Pavel12398 · Accepted Answer · 2022-03-22T07:18:41.343

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>';
            }
        }
    
    ?>

@Raja "Not working" does not help us. Please be specific: what exactly is happening, and how does it differ from your expectations? — kmoser, Mar 22 '22 at 16:15
Hi,Pavel12398.....Thanks working fine I need one more thing now what ever bridge id is there that value data is displaying but i need that 4 event all time will display and others variable data is not there --NA-- will display how can do this?please... — Raja, Mar 24 '22 at 11:51

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

1 Answers1