Cascading Drop Down: reverse

[kokkentor]

I am struggeling with a reverse of the classic example.

I want to select a city and let a CDD show which state it's in.

My CDD doesnt work this "reverse" way...

Any suggestions?

 

[Jaanus]

I think people here need more details. Are the city and state in one or different tables, does the city table have some field with state id ... And so on.
Posted via Mobile Device

 

[kokkentor]

thanks for reply.

Yes, cities and states are in different db tables.

State db table has two columns: statenumber, statename
City db table has three columns: citynumber, cityname, statenumber

CDD works when I have a State db join element and a city CDD element which watches the state element.

But the reverse doesnt work ...

EDIT:
The reverse is the following scenario:

The city element is a db join element and the state element is a cdd element wathcing the city element.

The wanted result is for the user to choose a city and then get shown which state the city is in.

 

[kokkentor]

Anyone?
Thanks!

 

[Jaanus]

what if you disable 'please select' in state dropdown?
Posted via Mobile Device

 

[kokkentor]

Thanks for answering, but I can't get it working.

 

[troester]

Can you post the settings of your city (dbjoin) and state (ccd) element?

 

[kokkentor]

city (dbjoin) element:

state (cdd) element:

 

[troester]

I'm a bit confused but I think reverse CDD can't work.
You can do it with a calc element (ajax calc= yes)

$db =&JFactory::getDBO();
$db->setQuery("SELECT statename FROM state  where statenumber = 
(SELECT statenumber FROM city  WHERE citynumber='{full-elementname-of-your-city-dropdown_raw}')");
$s = $db->loadResult();
return $s;

 

[cheesegrits]

Troester is correct, the reverse won't work. In order for the join / CDD relationship to work, you would have to specify the city join element 'key' as the state (i.e. the two letter abbreviation, or state name itself), in order for the lookup from the state CDD to find the corresponding row.

Which would in itself work on the initial display of the form. But then key for the city join would not be a unique key (it would refer to every city in the city table in that state), so we'd be unable to display that city again once you saved the form.

So troester's suggestion of a calc element is probably the best way to go.

-- hugh

 

[kokkentor]

Thanks!
But I can't get it working with Troester's code...

 

[troester]

Can you post your code and your full element names.

 

[kokkentor]

$db =&JFactory::getDBO();
$db->setQuery("SELECT fylkenavn FROM fylker where fylkenummer = 
(SELECT fylkenummer FROM kommuner where kommunenummer = '{henvendelser___kommune_raw}')");
$s = $db->loadResult();
return $s;

henvendelser___kommune is the City db join element

 

[troester]

In your screenshot it says
fylkenr and kommunenr (not ...nummer as in your query).

You can run your query through e.g. phpMyAdmin (setting a fix value for {henvendelser___kommune_raw}) to check if column and table names are correct.

 

[kokkentor]

Ah! Thanks! *Blush* Now it works

 

[kokkentor]

The code

$db =&JFactory::getDBO();
$db->setQuery("SELECT fylkenavn FROM fylker where fylkenr = 
(SELECT fylkenr FROM kommuner where kommunenr = '{henvendelser___kommune_raw}')");
$s = $db->loadResult();
return $s;

saves state name (fylkenavn) in the db table. I would however prefer the state number (fylkenr) to be stored in the db table -- but the state name (fylkenavn) to be displayed in the form's drop down of course.

Ideas? Thanks!

 

[cheesegrits]

Hmmmm. Not sure that'll work with a calc, as it isn't a join element. In other words, it stores what it displays.

-- hugh

 

[cheesegrits]

Only way round that one I can think of would be to have a second, hidden, calc which stores the state number, and use that for whatever purpose you need the number for elsewhere.

-- hugh

 

[cheesegrits]

OBTW, a better way to write your existing query would be with a join rather than an inner query. Also, you should ALWAYS sanitize any input from a form before using it in a query, see http://xkcd.com/327/, either by casting them to an int (if you are expecting an integer), or running them through $db->quote() (if you are expecting a string). I'd suggest something like this:

$db = JFactory::getDBO();
$kommune = (int)'{henvendelser___kommune_raw}';
if (!empty($kommune)) {
    $db->setQuery("SELECT fylkenavn FROM fylker LEFT JOIN kommuner ON fylker.fylkenr = kommuner.fylkenr WHERE kommuner.kommunenr = $kommune");
    return $db->loadResult();
}
return '';

-- hugh