What's wrong with my MYSQL Code query in php file?

Question

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();

}

Answer 1

As I can see here you only filter based on state and Delhi is Union Territory so to include that in a list you need to use In operator to filter type. you can also skip a join condition from where clause.

SELECT *,
   Group_concat(place_capital.place_child) AS Group
FROM   place
   left join place_capital
          ON place.place_id = place_capital.place_parent
WHERE active = 1
   AND TYPE IN ('State', 'Union Territory')
GROUP  BY place.place_id
ORDER  BY place.place_id

Answer 2

If you want to achieve this in production, I would recommend using mature API gateways like Apache APISIX or Kong. To implement it yourself, maybe you could store paths with wildcard or Lua patterns in Redis to allow later matching to the original URI. Applying some simple heuristics would help reduce the range of checking.