One or more queries to database?

Good afternoon. Need to get a large list of cities sorted by region. What sequence of actions is more correct?
1. Define the number of regions in the database. Later in the cycle, the requested list of cities for each region. Thereby create a huge number of "small" queries.
2. Take out all cities in one big array and then work with him.
3. Perhaps there is a "tricky" query to the database, which solves this problem?
Thanks in advance!
July 9th 19 at 11:23
2 answers
July 9th 19 at 11:25
Solution
To do a loop query - very bad, because the load rise from the empty seats, better:
if you want to select only cities (no of regions):
SELECT * FROM cities
if you want to select only specific to certain regions:
SELECT *
FROM cities
WHERE region_id IN (1, 2, 3)

if you want to select information in cities and regions:
SELECT cities.*, regions.* FROM cities
LEFT JOIN regions ON regions.id = cities.region_id


well, do not forget that information about cities/regions updated very often, so you should cache this information
July 9th 19 at 11:27
The requested desired regions, and then IN the Condition:
SELECT *
FROM cities
WHERE region_id IN ('1', '2', '3');

Find more questions by tags MySQLPHP