How to join country state and city in a single query?

Question

1 Table Name: Country

id  country_name
1   India
2   America

2 Table Name: States

id  state_name country_id
1   punjab      1
2   california  2

3 Table Name: Cities

id  city_name  state_id
1   Ludhiana   1
2   Chico      2

here is my code which i had written... Visit: tinyurl.com/myphpmysqli

At this time i'm getting a result if there are thousands of records in each table then how do i fetch using a single query with mysqli joins??

Country -> states -> cities

PHP version: 5.6.16 phpmyadmin: Version information: 4.5.2

post code here as text, don't ask us to follow some unknown tinyurl link to see your code — barbsan, Feb 08 '19 at 09:17
Have a look at this question, it will give you a good idea as they had a similar problem https://stackoverflow.com/questions/3709560/joining-three-tables-using-mysql — Stephen, Feb 08 '19 at 09:20

score 2 · Answer 1 · answered Feb 08 '19 at 09:24

2

try this. hope it will work for you

$sql = "SELECT * FROM country c
        JOIN States s on c.id= s.country_id
        JOIN Cities ci on s.id = ci.state_id"

answered Feb 08 '19 at 09:24

ashir haroon

1 Answers1