Looping the product based on the package

Question

Product_Package

  ------------------------------------------------------------------------------------
    code               size           price           image             description
  ------------------------------------------------------------------------------------
    0001                2kg             20            0012.jpg         description0012
    0001                4kg             40            0014.jpg         description0014
    0001                6kg             60            0016.jpg         description0016
    0001                8kg             80            0018.jpg         description0018

    0002                3kg             30            0023.jpg         description0023
    0002                5kg             50            0025.jpg         description0025
    0002                7kg             70            0027.jpg         description0027
    0002                8kg             80            0028.jpg         description0028

    0003                1kg             10            0031.jpg         description0031
    0003                2kg             20            0032.jpg         description0032
    0003                6kg             60            0036.jpg         description0036
    0003                9kg             90            0039.jpg         description0039

Product_List

  ------------------------------
    code               name     
  ------------------------------
    0001                FR      
    0002                GR      
    0003                WR      
    0004                BR      

And then I try to get the data with this code

$results = $mysqli->query
          ("    
            SELECT product_package.size,
                   product_package.price, 
                   product_package.description, 
                   product_package.image,

                   product_list.code, 
                   product_list.name                

            FROM product_package
            LEFT JOIN product_list ON product_list.code = product_package.code"
          );      
        $orders = array();     
        $html = '';     
        if ($results) {
            while($obj = $results->fetch_object()) {
                $orders[$obj->code][$obj->name]  = array(
                    'name' => $obj->name, 
                    'price' => $obj->price, 
                    'size' => $obj->size, 
                    'description' => $obj->description, 
                    'image' => $obj->image);
            }

            $html .= '<table width="70%"><tr>';
            $html .= '<td>id</td>';
            $html .= '<td>name</td>';
            $html .= '<td>package_size</td>';
            $html .= '<td>price</td>';
            $html .= '<td>image</td>';

            foreach ($orders AS $order_id => $order) {
          $orderCount = count($order);

                $html .= '<tbody>';
                $html .= '<tr>';
                $html .= '<td rowspan="' . count($order) . '">' . $order_id . '</td>';
                $row = 1;

                foreach ($order AS $item => $data) {
                    if ($row > 1) { $html .= '</tr><tr>'; }

                    $html .= '<td>' . $item . '</td>';
                    $html .= '<td>' . $data['size'] . '</td>';
                    $html .= '<td>' . $data['price'] . '</td>';
                    $html .= '<td>'. $data['description'] . '</td>';
                    $html .= '<td>' . $data['image'] . '</td>';
                    $row++;
                }
        }
                $html .= '</tr>';
                $html .= '<tbody>';

            $html .= '</table>';
        } 
        echo $html;

What I want the result is like this:

  ------------------------------------------------------------------------------------
    code                name        package size       price               image
  ------------------------------------------------------------------------------------
                                        2kg             20                0012.jpg         
    0001                FR              4kg             40                0014.jpg         
                                        6kg             60                0016.jpg         
                                        8kg             80                0018.jpg         

                                        3kg             30                0023.jpg         
    0002                GR              5kg             50                0025.jpg         
                                        7kg             70                0027.jpg         
                                        8kg             80                0028.jpg         

                                        1kg             10                0031.jpg         
    0003                WR              2kg             20                0032.jpg         
                                        6kg             60                0036.jpg         
                                        9kg             90                0039.jpg         

However the php code above give me this result:

  ------------------------------------------------------------------------------------
     id        name         package_size      price      descripttion          image   
  ------------------------------------------------------------------------------------
    0001        FR              2kg            20       description0012      0012.jpg         
    0002        GR              4kg            40       description0024      0024.jpg         
    0003        WR              6kg            60       description0036      0036.jpg         
    0004        BR              8kg            80       description0048      0048.jpg         
                                               90       description0039      0039.jpg         

Can anybody help me out with this? Thanks in advance.

Answer 1

You need to use group by and group concate functions of mysql please refer below sample query.

select id,group_concat(package_size) as package, group_concat(price) as Price, group_concat(image) as Image from mytbl group by code