Display records in a table format dynamically

mythri · August 18, 2021

Not able to display the result to end-user as requested format in table form.

Here are my tables

maintab table contains,

maintab.PNG.4ab1ba19fb41e00504dba1f99f780b4e.PNG

dataset contains,

dataset.PNG.9e035055111f7d832bb054b9144a9991.PNG

Now , user needs a display like this

Here Result and Method name(m1,m2...) are static.

I tried like this

$sel = $con->prepare("SELECT m.id,
                             m.edate,
                             m.result,
                             d.nos
                      FROM maintab m
                      INNER JOIN dataset d
                      ON m.id=d.mid");
$sel->execute();

<table class="table">
           <tr>
           <th></th>
           <th>Result</th>
           <th>m1</th>
           <th>m2</th>
           <th>m3</th>
           <th>m4</th>
           <th>m5</th>
           <th>m6</th>
           <th>m7</th>
           <th>m8</th>
           <th>m9</th>
           <th>m10</th>
           </tr>
           <?php foreach($sel as $r1)
		   {
			    ?>
           <tr>
          <td><?php echo date('d-m-Y', strtotime($r1['edate'])); ?></td>
          <td><?php echo $r1['result']; ?></td>
          <td><?php echo $r1['nos']; ?></td>
          </tr>
           <?php } ?>
          </table>

Couldn't achieve the desired display. I got something like this

Not getting where exactly i need to break <td> or i need to change my query.

Can somebody please suggest?

Barand · August 18, 2021

Do you mean something like this?

<?php 
// get the "name" headings that you need for the columns
// and also use them as keys in a "template" array
//
$res = $db->query("SELECT DISTINCT name
                   FROM dataset
                   ORDER BY name
                  ");
$names = $res->fetchAll();
$heads = array_column($names, 'name');
$temp = array_fill_keys($heads, '');
$table_header  = "<tr><td></td><td class='thead'>Result</td><td class='thead'>" . join("</td><td class='thead'>", $heads) . "</td></tr>\n";

//  now get the data
//  store in an array by "id"
//  witd subarrays for each name

$res = $db->query("SELECT 
                           id
                         , edate
                         , result
                         , name
                         , nos
                    FROM maintab m 
                         JOIN dataset d ON m.id = d.mid
                    ORDER BY id       
                   ");
$data = [];
foreach ($res as $r) {
    if (!isset($data[$r['id']])) {
        $data[$r['id']] = [ 'edate' => $r['edate'],
                            'result' => $r['result'],
                            'names' => $temp                 // the template array from earlier
                          ];
    }
    $data[$r['id']]['names'][$r['name']] = $r['nos'];         // put value in tempate array
}

// now we simply output data array into html table rows

$tdata = '';
foreach ($data as $row) {
    $tdata .= "<tr><td>{$row['edate']}</td><td>{$row['result']}</td><td>" . join('</td><td>', $row['names']) . "</td></tr>\n";
}
?>
<html>
<head>
<title>Example</title>
<style type='text/css'>
    td {
        padding: 4px 10px;
    }
    .thead {
        font-weight: 600;
        border-top: 1px solid gray;
        border-bottom: 1px solid gray;
    }
</style>
</head>
<body>
    <table>
        <?= $table_header ?>
        <?= $tdata ?>
    </table>
</body>
</html>

OUTPUT

image.png.3dac6f64e0b4cf10741213d2caada595.png

[edit] PS Sorry about the data typo. That's what happens when people post pictures instead of copyable text.

Edited August 18, 2021 by Barand

mythri · August 18, 2021

Yeassss! Thank you very much.

Sign In

Display records in a table format dynamically

Recommended Posts

mythri

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

mythri

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information