如何将resultarray列表从codeigniter活动记录中获取到rowarray中

Question

Hi需要获得一个乘积值及其变体，因为有一个乘积，其变体有很多，所以为了显示数据，我需要以这种格式的结果

[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是您的朋友。

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

类似于：

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