如何根据groupby Mysql Php获取和合并数据

Question

我正在使用mysql和php，我想根据"city"获取数据在数组中这是我的餐桌<<

>

merchantid              localityId
1                           30                       
2                           30       
3                           31
4                           30
5                           32

这里是表格"locality">

localityId               cityId           name
30                          1              abc 
31                          1              xyz
32                          2              xya

这里是表格"city">

cityId                  name
1                       Chandigarh
2                       Panchkula
3                       Delhi
4                       Mumbai

我想根据"city/locality"获取数据，例如，我想获取城市(按城市分组)的所有记录，换句话说，我想像下面的输出，我怎么能做到这一点?

{
"Status": "1",
"data": [
{
"localityName": "Chandigarh",
"FilterType": "filter",
"result": [
{
"id": "1",
"name": "",
//other info
},
{
"id": "2",
"name": "",
//other info
},
"localityName": "Panchkula",
"FilterType": "filter",
"result": [
{
"id": "3",
"name": "",
//other info
},
{
"id": "4",
"name": "",
//other info
},  
...And so on   
}       

我试过下面的代码，但给我位置/城市列表，不给我结果如我所愿，我错在哪里?

$this->db->select('l.localityId,c.name,c.cityId');
$this->db->from('merchants m'); 
$this->db->join('locality l', 'l.localityId=m.localityId');
$this->db->join('city c', 'c.cityId=l.cityId');
$this->db->group_by('l.cityId');         
$query = $this->db->get(); 
if($query->num_rows() != 0)
{
return $query->result_array();
}
else
{
return false;
}

Answer 1

您可以尝试使用以下代码

$this->db->select('l.localityId,l.name')
->from('merchants m') 
->join('locality l', 'l.localityId=m.localityId')
->group_by('m.localityId')
->limit($limit);        
$query = $this->db->get();
if($query->num_rows() != 0)
{
return $res=$query->result_array();
}
else
{
return false;
}

Answer 2

你可以试试:

SELECT c.cityId,c.name,GROUP_CONCAT(DISTINCT(CONCAT(l.localityId))) from locality l 
JOIN merchants m on l.localityId=m.localityId
JOIN city c on c.cityId=l.cityId
GROUP BY l.cityId;

现场演示:SQL FIDDLE