I have a two Tables one with Countries and second with Cities.
First Table is Countries
country_id | country_name
Second Table is Cities
country_id | city_id | city_name
I want to limit amount of queries from second Table to 3 Cities. Because one Country have a lot of Cities. I need only 5 Cities.
My Query:
$query = “SELECT c.country_name, p.city_name FROM (SELECT * FROM Cities LIMIT 3) AS p LEFT JOIN Countries as c ON p.county_id = c.county_id”;ter code here
$result = mysqli_query($db, $query);
$my_array = array();
while($row = mysqli_fetch_assoc){
array_push($my_array, $row);
}
If I fill $my_array i get 3 results from Database
My output:
[0] => Array ( [country_name] => Country 1 [city_name] => City 1 )
[1] => Array ( [country_name] => Country 1 [city_name] => City 2 )
[2] => Array ( [country_name] => Country 1 [city_name] => City 3 )
I need something like this
County 1 ----------------------- City 1 of Country 1 City 2 of Country 1 City 3 of Country 1 City 4 of Country 1 City 5 of Country 1 County 2 ----------------------- City 1 of Country 2 City 2 of Country 2 City 3 of Country 2 City 4 of Country 2 City 5 of Country 2 County 3 ----------------------- City 1 of Country 3 City 2 of Country 3 City 3 of Country 3 City 4 of Country 3 City 5 of Country 3
What I do wrong? I use MySQL 5.6.26
Thank you.
Like that?
SELECT city_id, city_name FROM Cities AS p LEFT JOIN Countries AS c ON c.country_id = p.country_id WHERE c.country_id = 'id' LIMIT 0,5