I have two tables in mysql database
one table named is place:
| place_id | place | active | shortform | country | type |
|---|---|---|---|---|---|
| 1 | Uttar Pradesh | 1 | UP | India | State |
| 2 | Delhi | 1 | DELHI | India | Union territory |
| 3 | Punjab | 1 | PUN | India | State |
| 4 | Karnataka | 1 | KAR | India | State |
| 5 | Kerala | 1 | KER | India | State |
| 6 | Lucknow | 1 | LKO | India | Capital |
| 7 | Chandigarh | 1 | CHA | India | Capital |
| 8 | Bengaluru | 1 | BEN | India | Capital |
| 9 | Thiruvanthapuram | 1 | THI | India | Capital |
| 10 | Saharanpur | 1 | SAH | India | District |
| 11 | Meerut | 1 | MEE | India | District |
| 12 | Gorakhpur | 1 | GOR | India | District |
and other named as place_capital
| id_place_place | place_parent | place_child |
|---|---|---|
| 1 | 1 | 6 |
| 2 | 3 | 7 |
| 3 | 4 | 8 |
| 4 | 5 | 9 |
| 5 | 1 | 10 |
| 6 | 1 | 11 |
| 7 | 1 | 12 |
I run this query
select *, group_concat(place_capital.place_child) AS Group from place
left join place_capital on place.place_id = place_place.place_parent
where place.place_id = place_place.place_parent and active = :active AND type = :type
group by place ORDER BY place
and it give the following result
| place_id | place | active | shortform | country | type | id_place_place | place_parent | place_child | Group |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Uttar Pradesh | 1 | UP | India | State | 1 | 1 | 6 | 6,10,11,12 |
| 3 | Punjab | 1 | PUN | India | State | 2 | 3 | 7 | NULL |
| 4 | Karnataka | 1 | KAR | India | State | 3 | 4 | 8 | NULL |
| 5 | Kerala | 1 | KER | India | State | 4 | 5 | 9 | NULL |
What query I used in php file to give the following result that include Delhi also.
The place table have some relationship with place_place table. with my above sql query that not give the Delhi in the result. So, what is the thing I missed in the php file to get the below result.
| place_id | place | active | shortform | country | type | id_place_place | place_parent | place_child | Group |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Uttar Pradesh | 1 | UP | India | State | 1 | 1 | 6 | 6,10,11,12 |
| 2 | Delhi | 1 | DELHI | India | Union Territory | NULL | NULL | NULL | NULL |
| 3 | Punjab | 1 | PUN | India | State | 2 | 3 | 7 | NULL |
| 4 | Karnataka | 1 | KAR | India | State | 3 | 4 | 8 | NULL |
| 5 | Kerala | 1 | KER | India | State | 4 | 5 | 9 | NULL |
and here is the code in the php file:-
public function __construct(Querier $db, $type = "", $active = 1) {
$this->db = $db;
$this->type = $type;
$this->active = $active;
$connection = $this->db->getConnection();
if ($this->type != "") {
$statement = $connection->prepare("select *, group_concat(place_capital.place_child) AS Group from place
left join place_capital on place.place_id = place_place.place_parent
where place.place_id = place_place.place_parent and active = :active AND type = :type
group by place ORDER BY place ");
$statement->bindParam(":type", $this->type);
} else {
$statement = $connection->prepare("SELECT * FROM place WHERE active = :active ORDER BY place ");
}
$statement->bindParam(":active", $this->active);
$statement->execute();
$this->_guide_list = $statement->fetchAll();
}