如何从codeigniter活动记录中将result_array列表导入row_array

Question

Hi need to get a products value and its variant as there is one product and its variants are many, so in ordr to show the data i need the result in this format

[0] => Array
        (
            [id] => 4
            [cat_id] => 8
            [subcat_id] => 46
            [manu_id] => 108
            [pro_name] => sample product 1
            [slug] => sample-product-1
            [meta_title] => sample product 1
            [meta_desc] => sample product 
            [pro_desc] => sample product
            [feature_img] => 20181118100828.png
            [feature] => 0
            [view] => 0
            [created_at] => 2018-11-18 10:08:34
            [updated_at] => 
            [variant](
                     [0] =>  [sku_no] => JMS/20181118100834/0/4
                             [rprice] => 2500
                             [sprice] => 2000
                             [availability] => 1,
                     [1] =>  [sku_no] => JMS/20181118100834/0/4
                             [rprice] => 2500
                             [sprice] => 2000
                             [availability] => 1
                     )

As i am using this query:

public function GetProductData($id){
        $this->db->select('A.*, B.variant, sku_no, rprice, sprice, availability');
        $this->db->from('products A');
        $this->db->join('product_sku B','A.id = B.product_id');
        $this->db->where('A.id', $id);
        $query = $this->db->get();
        if($query->num_rows()>0){
            $results = $query->result_array();
            echo '<pre>';
            print_r($results);
            die;
        }
    }

Which gives me result into this format:

Array
(
    [0] => Array
        (
            [id] => 4
            [cat_id] => 8
            [subcat_id] => 46
            [manu_id] => 108
            [pro_name] => sample product 1
            [slug] => sample-product-1
            [meta_title] => sample product 1
            [meta_desc] => sample product 1sample product 1sample product 1sample product 1
            [pro_desc] => 
sample product 1sample product 1sample product 1sample product 1


            [feature_img] => 20181118100828.png
            [feature] => 0
            [view] => 0
            [created_at] => 2018-11-18 10:08:34
            [updated_at] => 
            [variant] => cotton large
            [sku_no] => JMS/20181118100834/0/4
            [rprice] => 2500
            [sprice] => 2000
            [availability] => 1
        )

    [1] => Array
        (
            [id] => 4
            [cat_id] => 8
            [subcat_id] => 46
            [manu_id] => 108
            [pro_name] => sample product 1
            [slug] => sample-product-1
            [meta_title] => sample product 1
            [meta_desc] => sample product 1sample product 1sample product 1sample product 1
            [pro_desc] => 
sample product 1sample product 1sample product 1sample product 1


            [feature_img] => 20181118100828.png
            [feature] => 0
            [view] => 0
            [created_at] => 2018-11-18 10:08:34
            [updated_at] => 
            [variant] => cotton medium
            [sku_no] => JMS/20181118100834/1/4
            [rprice] => 2500
            [sprice] => 1800
            [availability] => 1
        )

)

I don't want to use other function into this as it will slow the process function is there any mysql solution available apart form using another function within this function.

</div>

Answer 1

You could iterate each row of the result set from products and then do another query on product_sku. This has over-head and isn't efficient. But, MYSQL group_concat is your friend here.

The idea is to concatenate the fields you want, and then group them in the select for each of the variants. Then you can explode them later to create the actual result. make sure the delimiters you choose can't occur in the database.

Something like:

$this->db->simple_query('SET SESSION group_concat_max_len = 1000000');
$q = $this->db->select("A.id,A.cat_id,A.subcat_id,A.manu_id,A.pro_name,A.slug,A.meta_title,A.meta_desc,A.pro_desc,
      A.feature_img,A.feature,A.view,A.created_at,A.updated_at, 
      GROUP_CONCAT(CONCAT(B.sku_no, 'æ',B.rprice,'æ',B.sprice,'æ',B.availability) SEPARATOR 'ø') as variants",FALSE)
  ->from('products A')
  ->join('product_sku B','A.id = B.product_id')
  ->where('A.id', $id)
  ->group_by('A.id,A.cat_id,A.subcat_id,A.manu_id,A.pro_name,A.slug,A.meta_title,A.meta_desc,A.pro_desc,
      A.feature_img,A.feature,A.view,A.created_at,A.updated_at')
  ->get();
$results = [];
foreach ($q->result_array() as $row) {
  $variants = array();
  if($row['variants']){
   foreach (explode('ø',$row['variants']) as $variant) {
     list($sku,$rprice,$sprice,$availability) = explode('æ',$variant);
     $variants[] = [
       'sku' => $sku,
       'rprice' => $rprice,
       'sprice' -> $sprice,
       'availability' => $availability
     ];
   }
  }
  $row['variant'] = $variants;
  unset($row['variants']);
  $results[] = $row;
}
return $results;