I am working with mysql and php and i want to get data according to "city" in array Here is my table "merchants"
merchantid localityId
1 30
2 30
3 31
4 30
5 32
Here is table "locality"
localityId cityId name
30 1 abc
31 1 xyz
32 2 xya
Here is table "city"
cityId name
1 Chandigarh
2 Panchkula
3 Delhi
4 Mumbai
And i want to fetch data according to "city/locality",For example i want to fetch all record of city(group by city),in other words i want like following output,how can i do this ?
{
"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
}
I tried with following code but giving me list of locality/city , not giving me result as i want,Where i am wrong ?
$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;
}
You can try with following code
$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;
}
You can try this:
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;
Live demo here: SQL FIDDLE