how to create queries like this...

[eluser]tusukgigi[/eluser]
how to create queries in mysql like this

district table
----------------
id | district_name
1 ::: countries1
2 ::: countries2
3 ::: countries3
4 ::: countries4

city table
----------------
id | city_name | district_id
1 ::: jakarta ::: 1
2 ::: bandung ::: 1
3 ::: newyork ::: 2
4 ::: tokyo ::: 3
________________________________________
i want to create navigation like this

[+] Country1
-jakarta.
-bandung.

[+] Country2
-newyork

[+] Country3
-tokyo

[+] Country4
- [No list]

how to create the queries...
Im newbie

thanks very much for your answer

[eluser]WanWizard[/eluser]

Code:

SELECT * FROM district LEFT JOIN city ON city.district_id = district.id

You will have to loop through the result in your code to convert the flat result into a tree structure.

[eluser]tusukgigi[/eluser]
wow thanks for your answer..
but how can i create a navigation in browser?

[eluser]tusukgigi[/eluser]
this is my view


if(count($getCountry)){
foreach($getCountry as $key=>$list){
echo $list['district_name']."<br />";
echo $list['city_name']."<br />";
}
}

$getCountry is a variable from controller

this is my model database
SELECT DISTINCT city_name, district_name FROM district LEFT JOIN city ON city.district_id = district.id

and this is the result

country1
jakarta
country1
bandung

country2
newyork

....

there is a COUNTRY repeat's

[eluser]loosetops[/eluser]
Look for a jquery tree plugin and give it the right structure as the wizard suggests. It should probably be the the semi-standard <ul> <li> <ul>

[eluser]tusukgigi[/eluser]
What you mean like this..?

foreach($getCountry as $key=>$list){
echo "<ul><li>".$list['district_name']."<ul><li>".$list['city_name']."</li></ul></li>";
}

[eluser]tusukgigi[/eluser]
who can help me?

what wrong with my query in mysql or my view in browser?

[eluser]davidbehler[/eluser]
Assuming the country and city are ordered alphabetically, like

Code:

$this->db->order_by('district.district_name', 'asc');
$this->db->order_by('city.city_name', 'asc');

then you could to this (untested!):

Code:

$last_district = '';
if(count($getCountry) > 0) {
  echo '<ul>';
  foreach($getCountry as $city) {
    // check if we are still in the same country/district or have a new one
    if($last_district != $city['district_name']) {
      echo '<li>'.$city['district_name'].'</li>';
      $last_district = $city['district_name'];
    }
    echo '<li>'.$city['city_name'].'</li>';
  }
  echo '</ul>';
}

You could enhance this code and make it a listed nest for each country, but I'll leave that up to you.

[eluser]tusukgigi[/eluser]
Can order by two fields in query mysql?

[eluser]tusukgigi[/eluser]
i got error like this
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

this is my query.

$sql = "SELECT * FROM district LEFT JOIN city ON city.d_id = district.id order by district.district_name order by city.city_name ";