PHP/MYSQL to group cities under each state

I have 2 separate tables states and cities.
I’ll need an SQL (Mysql) statement or PHP query to get output data in the following format. (Basically grouping the cities under their respective states).

Output example I’ll need (JSON)

"Andaman and nicobar" : ['Port Blair', 'Adilabad'],
"Tamilnadu" : ['Chennai', 'Coimbatore'],
"Maharastra" : ['Mumbai', 'Lonavala', 'Pune'],

I have the database tables as below.

States Table

enter image description here

You could probably do this using PDO::FETCH_GROUP and a JOIN.
You need to make state_name the first column in the query to use it as the group.

Then use json_encode() on the result.


Ooooh, I didn’t know about that one. That’s awesome! :slight_smile:

Edit: I’m talking about FETCH_GROUP, I obviously knew joins :wink:


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.