+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: how to create queries like this... (/showthread.php?tid=32623)
Pages:
1
2
how to create queries like this... - El Forum - 07-29-2010
[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
how to create queries like this... - El Forum - 07-29-2010
[eluser]WanWizard[/eluser]
Code:
SELECT * FROM district LEFT JOIN city ON city.district_id = district.idYou will have to loop through the result in your code to convert the flat result into a tree structure.
how to create queries like this... - El Forum - 07-29-2010
[eluser]tusukgigi[/eluser]
wow thanks for your answer..
but how can i create a navigation in browser?
how to create queries like this... - El Forum - 07-29-2010
[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
how to create queries like this... - El Forum - 07-29-2010
[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>
how to create queries like this... - El Forum - 07-29-2010
[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>";
}
how to create queries like this... - El Forum - 07-29-2010
[eluser]tusukgigi[/eluser]
who can help me?
what wrong with my query in mysql or my view in browser?
how to create queries like this... - El Forum - 07-29-2010
[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');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.
how to create queries like this... - El Forum - 07-29-2010
[eluser]tusukgigi[/eluser]
Can order by two fields in query mysql?
how to create queries like this... - El Forum - 07-29-2010
[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 ";