Display data in table format from two tables

[mythri]

Hello,

 

I am trying to display the data from two tables with proper format. But Its not happening

 

Here is my 1st table - orders 

 

and my 2nd table - order_line_items

 

 

I want to display like this 

 

 

Here is my code

$query = $mysqli->query("SELECT orders.order_id, orders.company_id, orders.order_for, order_line_items.order_id, order_line_items.item, order_line_items.unit,SUM(order_line_items.unit_cost * order_line_items.quantity) AS 'Total', order_line_items.tax from orders INNER JOIN order_line_items ON orders.order_id = order_line_items.order_id where orders.order_quote = 'Order' GROUP BY order_line_items.id"); ?>

<table id="dt_hScroll" class="table table-striped">
						<thead><tr>
						<th>Order ID</th>
						<th>Company</th>
						<th>Contact Person</th>
						<th>Products</th>
                        <th>Total</th>
                        
                        
						
</tr>

</thead>
<tbody> <?php

while($row = $query->fetch_array())
{
?>
<tr>
<td><?php echo $row['order_id']; ?></td>
<td><?php echo $row['company_id']; ?></td>
<td><?php echo $row['contact_person'] ?></td>
<td><?php echo $row['item']; ?></td>
<td><?php echo $row['Total']; ?> %</td>

</tr>
<?php

}	

But here order ID, Company ID, Contact Person are also repeating thrice with item in order_line_items table

 

Please suggest me how to do this

[CroNiX]

I believe your data is correct. An order can have many products, correct? What you'd need to do is in your loop when you are outputting the data, check to see if the order_id is the same as the last order_id, if not display the order_id, if so it's part of the same order and output a   in the <td>.

 

You'd probably want to ORDER your query by (orders.order_id, orders.company_id, orders.order_for)

some pseudocode:

$last_order_id = null; //store the last order_id
while($row = $query->fetch_array())
{
  //check to see if this is a new order or part of the same one
  if ($row['order_id'] != $last_order_id)
  {
    //nope, it's a new order, store as $last_order_id and output the order_id
    $last_order_id = $row['order_id'];
    echo '<td>' . $row['order_id'] . '</td>';
  }
  else
  {
    //it's part of the same order, output a space for this td
    echo '<td> </td>';
  }
}

You'd need to do the same thing for anything else you don't want to repeat