Jump to content

Display data in table format from two tables


mythri

Recommended Posts

Hello,

 

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

 

Here is my 1st table - orders 

post-168283-0-17551700-1410437080_thumb.png

 

and my 2nd table - order_line_items

 

post-168283-0-65875100-1410437112_thumb.png

 

I want to display like this 

 

post-168283-0-40244500-1410437379_thumb.png

 

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.