CODEWITHSUNDEEP
codeigniterone-to-many
ci_lover
ci_lover

Reputation: 718

How to display data from query - one to many relationship between db tables

I'm trying to display data from query but I can't show it as it should. My tables in database are: ordersheader and orderitems. It's one to many relationship between them. One ordersheader have many orderitems. They are joined ON idOrder. One idOrder in ordersheader corresponds to many rows in orderitems. I have to show all orders for current date and to show all orderitems for each idOrder. It should look like this for all orders:

http://prntscr.com/7lwoan

First row is order - it has to be displayed all orderitems for this order and when you click Details it has to be shown each orderitem on separate row. Now, as I used code below, each orderitem is shown on separate row. How to make it looks like the above link? Thanks! My model is:

 function getOrders(){
    $date = new DateTime("now");
 
    $curr_date = $date->format('Y-m-d ');
    $this->db->select('ordersheader.*,customer.name,orderitems.*');
    $this->db->from('ordersheader'); 
    $this->db->join('orderitems', 'orderitems.idOrder = ordersheader.idOrder');
    $this->db->join('customer', 'customer.idCustomer = ordersheader.idCustomer');
    $this->db->where('DATE(orderDueDate)', $curr_date);   

    $query = $this->db->get();
    return $query->result();
    
    }

My view is:

<?php
 if($orderlist){
foreach($orderlist as $row) {
 ?> <tr class="overview">
                        <td><?php echo $row->idOrder; ?></td>
                        <td class="text-center"><img src="<?= base_url();?>/assets/images/tick.png"></td>
                        <td><?php echo $row->name; ?></td>
                        <td><?php echo $row->eggSize; ?></td>
                        <td><?php echo $row->quantity; ?></td>
                        <td>
                            <div class="progress">
                                <div class="progress-bar" role="progressbar" style="width: 60%;">
                                    60%
                                </div>
                            </div>
                        </td>
                        <td>18:00</td>
                        <td><button type="button" class="btn btn-link btn-xs view-full-order">Full</button></td>
                    </tr> 
                    <tr class="full hide">
                        <td></td>
                        <td></td>
                        <td></td>
                        <td><?php echo $row->eggSize; ?></td>
                        <td><?php echo $row->quantity; ?></td>
                        <td>
                            <div class="progress">
                                <div class="progress-bar progress-bar-info" role="progressbar" style="width: <?php echo $rand; ?>%;">
                                  <?php echo $rand; ?>%  
                                </div>
                            </div>
                        </td>
                        <td>14:00</td>
                        <td></td>
                    </tr>
 <?php } } ?>
             </tbody></table>

Edited: I made it with group_concat and explode. But now, problem is that for each idOrder I should calculate average rand (this is the value of progress bar) of all orderitems. This should be for each order - avegare sum of orderitems. Now, it's not calculated correct. It now looks like that:

http://prntscr.com/7mu0k3 This row where is Billa should be average of the next 2 rows. My view is:

<tbody class="client">
            <?php 
            if ($orderlist) {
                foreach ($orderlist as $row) {
                    $i = 0;

                    ?> <tr class="overview">
                        <td><?php echo $row->idOrder; ?></td>
                        <td class="text-center">
                            <?php
                             $s = $row->eggSize; 
                    $egg_size_array = explode(",", $row->eggSize);
                    $quantity_array = explode(",", $row->quantity);
                    
                    if (!empty($egg_size_array))
                        foreach ($egg_size_array as $key => $value) {
                            $rand[$key] = rand(1, 100);
                            $random_hour[$key] = rand(01, 24);  
                          
                        }
?>                      
                            
                        </td>
                        <td><?php echo $row->name; ?></td>
                        <td><?php echo $row->eggSize; ?></td>
                        <td><?php
                            $s = $row->quantity;
                            $s = explode(',', $s);
                            echo array_sum($s);
                         print_r($rand);  echo "<br>";print_r(array_sum($rand)); echo "<br>";   print_r(count($egg_size_array));
                            ?></td>
                        <td>
                            <div class="progress">
                            
                                <div class="progress-bar" role="progressbar" style="width: <?php 
                                echo array_sum($rand)/ count($egg_size_array); ?>%;">
                                     
                                      
                                <?php  echo array_sum($rand)/ count($egg_size_array); echo "%";  ?>

                                </div>
                            </div>
                        </td>
                        <td><?php echo max($random_hour); echo " ч."; ?></td>
                        <td><button type="button" class="btn btn-link btn-xs view-full-order">
                    <?php echo lang('details'); ?>  </button></td>
                    </tr> 
                    <?php
                   
                    if (!empty($egg_size_array))
                        foreach ($egg_size_array as $key => $value) {
                            // $rand = rand(1, 100);
                            //$random_hour = rand(01, 24);
                            ?>
                            <tr class="full hide">
                                <td></td>
                                <td></td>
                                <td></td>
                                <td> 
                <?php echo $value; ?></td>
                                <td><?php echo $quantity_array[$key]; ?></td>
                                <td>
                                    <div class="progress">
                                        <div class="progress-bar progress-bar-info" role="progressbar" style="width: <?php echo $rand[$key]; ?>%;">
                <?php echo $rand[$key]; ?>%  
                                        </div>
                                    </div>
                                </td>
                                <td><?php echo $random_hour[$key] . ' ч.' ?></td>
                                <td></td>
                            </tr>
                            <?php
                        }
                    ?><?php
                }
            }
            ?>
        </tbody></table>

My model is:

  function getOrders(){
        $date = new DateTime("now");
        $curr_date = $date->format('Y-m-d');
        $this->db->select('orderitems.eggSize as size');
        $this->db->select('ordersheader.*,customer.name,GROUP_CONCAT(orderitems.itemNumber) as itemNumber');
        $this->db->select('ordersheader.*,customer.name,ordersheader.*,customer.name,GROUP_CONCAT(orderitems.quantity ) as quantity ');
        $this->db->select('ordersheader.*,customer.name,ordersheader.*,customer.name,GROUP_CONCAT(orderitems.unitPrice) as unitPrice');
        $this->db->select('ordersheader.*,customer.name,ordersheader.*,customer.name,GROUP_CONCAT(orderitems.eggSize ) as eggSize');
        $this->db->from('ordersheader'); 
        $this->db->join('orderitems', 'orderitems.idOrder = ordersheader.idOrder');
        $this->db->join('customer', 'customer.idCustomer = ordersheader.idCustomer');
        $this->db->where('DATE(orderDueDate)', $curr_date);
        $this->db->group_by('orderitems.idOrder');
        $query = $this->db->get();
        return $query->result();
    }

Upvotes: 0

Views: 1744

Answers (1)

M&#225;rcio Gonzalez
M&#225;rcio Gonzalez

Reputation: 1040

You should create a function to return order children.

function GetItems($idOrder)
{
    return $this->db->get_where("orderitems", "idOrder" => $idOrder)->array_result();
}

After that, you will remove the JOIN with the orderitems table. And then you should get the array_result of your query and iterate with it by foreach and attach the children in your collection like this:

$curr_date = $date->format('Y-m-d ');
$this->db->select('ordersheader.*,customer.name');
$this->db->from('ordersheader'); 
$this->db->join('customer', 'customer.idCustomer = ordersheader.idCustomer');
$this->db->where('DATE(orderDueDate)', $curr_date);   

$query = $this->db->get()->array_result();

$index = 0;

foreach($query as $item)
{
    $query[$index]["children"] = GetItems($item->idOrder);
    $index++;
}

I've created a mockup in a fiddle to show you an example: http://codepad.org/LfjQJQCP

Upvotes: 1

Related Questions