Jump to content

Display records in a table format dynamically


Go to solution Solved by Barand,

Recommended Posts

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

 

result.thumb.PNG.f2ae4e9d33751ca6fd70613552653a92.PNG

 

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

 

res.thumb.PNG.782ac3b1c9619b33a0ae185ef8b3db74.PNG

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

Can somebody please suggest?

  • Solution

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 by Barand
  • Like 1
  • Thanks 1
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.