+- 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]davidbehler[/eluser]
Sure, you can order by as many fields as you want.
What it does is this:
It orders the result alphabetically by the first field (in this case district_name). As there are multiple rows with the same district_name they can't be ordered properly and some default order would be in place for them (propably by primary key). Anyway, those with the same district_name will be ordered again, this time by city_name.
Take this example:
Code:
Un-ordered:
district_name | city_name
--------------------------
country 1 | test city
country 2 | other city
country 1 | more city
country 1 | city2000
country 2 | big_city
country 3 | small_city
country 2 | medium_city
order by district name asc, city_name asc:
district_name | city_name
--------------------------
country 1 | city2000
country 1 | more city
country 1 | test city
country 2 | big_city
country 2 | medium_city
country 2 | other city
country 3 | small_cityhow to create queries like this... - El Forum - 07-29-2010
[eluser]davidbehler[/eluser]
To solve your query problem:
Code:
$this->db->select('city.*, district.district_name');
$this->db->from('city');
$this->db->join('district', 'city.district_id = district.id');
$this->db->order_by('district.district_name', 'asc');
$this->db->order_by('city.city_name', 'asc');
OR
$sql = "select city.*, district.district_name from city join district on city.district_id = district.id order by district.districr_name asc, city.city_name asc";Does this work?
how to create queries like this... - El Forum - 07-29-2010
[eluser]tusukgigi[/eluser]
great............................................
thankiyou
i got like this
# amerika
los angeles
# german
urtrich
# indonesia
bandung
# indonesia
jakarta
# jepang
okinawa
# jepang
tokyo
but there is still the same
# indonesia
bandung
# indonesia
jakarta
indonesia and jepang reccured twice
i wanna count the same field like this
# amerika (1)
-los angeles
# german(1)
-urtrich
# indonesia(2)
-bandung
- indonesia
# jepang(2)
-okinawa
-tokyo
how to create query?
how to create queries like this... - El Forum - 07-29-2010
[eluser]davidbehler[/eluser]
Have you tried my piece of code to create the list? That should fix your problem.
how to create queries like this... - El Forum - 07-29-2010
[eluser]tusukgigi[/eluser]
yes i tried your code like this
$sql = mysql_query("select city.*, district.district_name from city join district
on city.district_id = district.id order by district.district_name asc,
city.city_name asc");
how to create queries like this... - El Forum - 07-29-2010
[eluser]tusukgigi[/eluser]
and i create my view
echo "<ul>";
foreach($getCountry as $city) {
if($last_district != $city['district_name']) {
echo "<li>".$city['district_name']."</li>";
$last_district = $city['district_name'];
}
echo $city['city_name']."<br />";
}
echo "</ul>";
how to count the same field in city table?
thanks very much
how to create queries like this... - El Forum - 07-29-2010
[eluser]tusukgigi[/eluser]
ohh okk...
thanks
I was wrong to write code
and now it s correct...
many thanks again
regards ::tusukgigi
how to create queries like this... - El Forum - 07-29-2010
[eluser]tusukgigi[/eluser]
i wanna count the same field
like this
indonesia(2)
-jakarta
-bandung
jepang(2)
-tokyo
-okinawa
but i got error...
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
$sql = "select city.city_name, count(city.city_name) AS t district.district_name from city join district
on city.district_id = district.id WHERE city.district_id = district.id order by district.district_name asc,
city.city_name asc";