+- 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: help with sql query (/showthread.php?tid=10955)
help with sql query - El Forum - 08-19-2008
[eluser]Unknown[/eluser]
hi guys i have 2 tables ,country table and city table
I'm looking for an sql query that will genarate an out put like this
UK
london birmingham manchester
USA
newyork alabama
basicaly country and the city below it . any help much apriciated
help with sql query - El Forum - 08-19-2008
[eluser]Sumon[/eluser]
Lets consider UK country id is 1.
Code:
$CountryId=1; //Change as needed
SELECT Cnt.country_name, City.city_name
FROM country Cnt join (city as City)
ON (Cnt.country_id=City.country_id)
WHERE Cnt.country_id=$CountryIdhelp with sql query - El Forum - 08-20-2008
[eluser]obiron2[/eluser]
Expanding on the above, what you need is a multi dimensional array:
The easiest way to do this is to generate a $objCountry=db->query() of the countries you want and then loop through each country record and build an array/object by running the query above setting $CountryId to the value in the $objCountry->row()
you want to build an array/object that looks like this:
$citylist = array(array(ID=>1,Code=>'UK',cities=>array('Birmingham','Bristol','Cardiff'...))
array(ID=>2,code=>'USA',cities=>array('Birmingham','Boston','Chicago'...))
...
)
you can then pass $citylist through your $data to your view
($data['citylist'] = $citylist
where you can then run for each loops on the nested data and format it however you want.
Best of luck
Obiron