I would like to make a collection according to some fields in the product information and product history table in the products table how do I do this in a single query?
my sample code:
<table>
<thead>
<tr>
<th>Product</th>
<th>Model</th>
<th>Actıve Total</th>
<th>Pasıve Total</th>
<th>Color Total</th>
</tr>
</thead>
<tbody>
<?php
$product = $db->get_results("SELECT * FROM product WHERE (status='ACTIVE')");
foreach ($product as $p ){
$active_total = $db->get_var("SELECT SUM(price) FROM product_history WHERE pid='$p->pid' AND status='AKTIVE'");
$pasive_total = $db->get_var("SELECT SUM(price) FROM product_history WHERE pid='$p->pid' AND status='PASIVE'");
$color_total = $db->get_var("SELECT SUM(price) FROM product_history WHERE pid='$p->pid' AND status='ACTIVE' AND color='BLUE'");
?>
<tr>
<td><?php echo $p->name; ?></td>
<td><?php echo $p->model; ?></td>
<td><?php echo $active_total; ?></td>
<td><?php echo $pasive_total; ?></td>
<td><?php echo $color_total; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
Thanks.