[Zope-DB] Loading JavaScript Arrays with Z SQL Methods & Python script

[norman@khine.net]

Hello,
As per http://javascript.internet.com/forms/country.html#source what would be the most efficient way to load this data in.

What I've dome so far is to have two ZSQL methods
 sql_list_country: select country_id, country from country order by country
 sql_list_region: select region from region

Now I create a python script
#this returns a list of all the regions for each country
countrylist=[1,2,3.....]
region = []
for country_id in countrylist:
    for sql in container.getRegionById(country_id=country_id):
       region.append(sql[0])

return region

and a python script
#this returns a list of all the counties/states for each country.
countylist=[1,2,3.....]
county = []
for region_id in countylist:
    for sql in container.getCountyById(region_id=region_id):
       county.append(sql[0])

return county

Now my problem: what is the best way to generate, the following from the above scripts

var country_idArray =  new Array("('Select country','',true,true)",
"('Ethiopia')",
"('Somalia')",
"('South Africa')",
"('Other')");


I can do it in dtml, but the problem is that if you have a list of 200+ countries I have to add 200+ methods and 200+ zsql methods which will be as follows:

DTML Method: 1_region
<dtml-in 1_region_list>
<dtml-in sequence-item>
"('<dtml-var sequence-item>')"</dtml-in>
<dtml-if sequence-end>);
<dtml-else>,</dtml-if>
</dtml-in>

ZSQL Method: 1_region_list
select region from region where country_id=1

so the JavaScript becomes
<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
var country1Array =  new Array("('Select country','',true,true)",
<dtml-var 1_region>
var country2Array =  new Array("('Select country','',true,true)",
<dtml-var 2_region>
var country3Array =  new Array("('Select country','',true,true)",
<dtml-var 3_region>
......
......
and the form

<form name="globe">
<select name="region" onChange="populateCountry(document.globe,document.globe.region.options[document.globe.region.selectedIndex].value)">
<option selected value=''>Select country</option>
<dtml-var  select_country_list>
</select>
<select name="country" onChange="populateUSstate(document.globe,document.globe.country.options[document.globe.country.selectedIndex].text)">
<option value=''><-------------------</option>
</select>
</form>

where <dtml-var  select_country_list> is as follows
<dtml-in sql_list_country>
<Option value='country<dtml-var country_id>'>
<dtml-var country></Option></dtml-in>


many thanks

Norman