PHP/MYSQL to group cities under each state

[yaseen1]

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.

[SamA74]

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.

[rpkamp]

@SamA74

You could probably do this using PDO::FETCH_GROUP and a JOIN .

<offtopic>
Ooooh, I didn’t know about that one. That’s awesome!
</offtopic>

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