php - 如何从codeigniter事件记录中获取result_array列表到row_array中

Question

您需要获取产品值及其变体，因为有一种产品及其变体有很多，因此为了显示数据，我需要这种格式的结果

[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
                     )

当我使用此查询时:

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;
		}
	}

这给了我这种格式的结果:

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
        )

)

我不想在其中使用其他函数，因为它会减慢处理函数是否有任何 mysql 解决方案可用，除了使用此函数中的另一个函数之外。

Answer 1

您可以迭代产品结果集的每一行，然后对product_sku 执行另一个查询。这会产生开销并且效率不高。但是，MYSQL group_concat这是你的 friend 。

这个想法是连接所需的字段，然后将它们分组到每个变体的选择中。然后您可以稍后分解它们以创建实际结果。确保您选择的分隔符不会出现在数据库中。

类似于:

$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;