How to make tabular data output from 2 table?

Question

I have 2 table : item_table and transaction_table :

item_table :
+-------+---------+
|id_item|item_name|
+-------+---------+
|i_1    |item_a   |
+-------+---------+
|i_2    |item_b   |
+-------+---------+
|i_3    |item_c   |
+-------+---------+
|i_4    |item_d   |
+-------+---------+

transaction_table
+--------------+----------------+-------+
|id_transaction|transaction_code|id_item|
+--------------+----------------+-------+
|1             |t_1             |i_4    |
+--------------+----------------+-------+
|2             |t_1             |i_1    |
+--------------+----------------+-------+
|3             |t_2             |i_2    |
+--------------+----------------+-------+
|4             |t_3             |i_1    |
+--------------+----------------+-------+
|5             |t_3             |i_4    |
+--------------+----------------+-------+
|6             |t_3             |i_3    |
+--------------+----------------+-------+

I have input those 2 table into 2 array i call item_array and transaction_array. From those 2 array, i want to make tabular output like below :

+----------------+---+---+---+---+
|transaction_code|i_1|i_2|i_3|i_4|
+----------------+---+---+---+---+
|t_1             |1  |0  |0  |1  |
+----------------+---+---+---+---+
|t_2             |0  |1  |0  |0  |
+----------------+---+---+---+---+
|t_3             |1  |0  |1  |1  |
+----------------+---+---+---+---+

my code below not output correctly

<table id="example1" class="table table-bordered table-striped">
    <thead>
    <tr>
        <th>kd transaksi</th>
        <?php for ($k = 0; $k < 1513; $k++) {
            echo "<th>" . $item_array[$k]['item_name'] . "</th>";
        }
        ?>
    </tr>
    </thead>
    <tbody>
    <?php for ($i = 0; $i < 5; $i++) {
        echo "<tr><td>" . $transaction_array[$i]['transaction_code'] . "</td>";

        for ($j = 0; $j < 1513; $j++) {
            if ($transaction_array[$i]['id_item'] == $item_array[$j]['id_item']) {
                echo "<td>1</td>";
            }
            else {
                echo "<td>0</td>";
            }
        }

        echo "</tr>";
    }
    ?>
    </tbody>
</table>

this is the result that incorrect :

+----------------+---+---+---+---+
|transaction_code|i_1|i_2|i_3|i_4|
+----------------+---+---+---+---+
|t_1             |0  |0  |0  |1  |
+----------------+---+---+---+---+
|t_1             |1  |0  |0  |0  |
+----------------+---+---+---+---+
|t_2             |0  |1  |0  |0  |
+----------------+---+---+---+---+
|t_3             |0  |0  |0  |1  |
+----------------+---+---+---+---+
|t_3             |1  |0  ||0 |0  |
+----------------+---+---+---+---+
|t_3             |0  |0  |1  |0  |
+----------------+---+---+---+---+

_not output correctly_ meaning? Be explicit about what doesn't work as expected, show the result. It may help to reproduce the issue if you post (part of) the source arrays. — jibsteroos, Dec 20 '19 at 08:22
The following might be helpful to you: https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql — Carsten Massmann, Dec 20 '19 at 09:30

Carsten Massmann · Answer 1 · 2019-12-20T10:11:06.477

0

Here is a suggestion on how you might modify your tbody part of your code to achieve what you want:

<tbody>
<?php
    // some preprocessing into new array $tbl:
    foreach ($transaction_array as $ta) {
      $tbl[$ta['transaction_code']][substr($ta['id_item'],2)]=1;
    }
    // now output $tbl into the table:
    foreach ($tbl as $key => $row) {
      echo "<tr><td>$key</td>";
      for ($i=1; $i<5; $i++) echo '<td>'.(isset($row[$i])?1:0).'</td>';
      echo "</tr>\n";
    }
?>
</tbody>

I just tested it, the script will return:

<tr><td>t_1</td><td>1</td><td>0</td><td>0</td><td>1</td></tr>
<tr><td>t_2</td><td>0</td><td>1</td><td>0</td><td>0</td></tr>
<tr><td>t_3</td><td>1</td><td>0</td><td>1</td><td>1</td></tr>

You can find a little demo here: https://rextester.com/GVTEA56326

edited Dec 20 '19 at 10:11

answered Dec 20 '19 at 09:59

Carsten Massmann

26,510
2
22
43

thank you for your solution, it's work. but in my table, the `id_item` is not always like that. some time it can be an number, or alphanumeric. please replay me, it close to solved. – Linkoln Park Dec 26 '19 at 15:26
the result now is all 0 – Linkoln Park Dec 26 '19 at 15:43

How to make tabular data output from 2 table?

1 Answers1