Dropdown Text_Values from 2 DB-Columns possible ?

Question

HI All,

it might be a simple question, but i could not find an answer ...
I have a databbas Table with different columns. For Example forename , surname , title.
i managed to get a dropdown with the Text_Key = forname or the Text_Key = surname. But i am looking for a possibility to get the Textkay a combination of both.

Example :
forename Column : Tim, Jim, Tom,...
surname Column : Red, Green, Blue
tittle Column : "", Dr. , ""

and the text in the Dropdown should be : Tim Red , Dr. Jim Green , Tom Blue

there might be a way to add an extra column in the Databese Table with the combined text which is filled in the bachround during Save_data. but then i have more indormation to store. Is there an easy way ?

Thx & Regards from Germany
TRIJ

GreyHead · Accepted Answer

Hi TRIJ,

There are two ways to do this.

A. Use a DB Load action and get all four columns: id, forename, surname, title - use a Model ID e.g. names to put them in a sub-array of the $form->data array. Then use a Custom Code action to build the extra data:

<?php
foreach ( $form->data['names'] as $k => $v ) {
  $form->data['names'][$k]['name'] = $form->data['names'][$k]['title'].' '.$form->data['names'][$k]['forename'].' '.$form->data['names'][$k]['surname'];
  $form->data['names'][$k]['name'] = trim($form->data['names'][$k]['name']);
}
?>

!! Not tested and may need debugging !!

Then you can use names.id and names.name in the Dynamic Data box of the dropdown.

B. Use a Custom Code action with a MySQL query:

<?php
$db = JFactory::getDBO();
$query = "
    SELECT `id`, CONCAT(`title`, ' ', `forename`, ' ', `surname`) AS `name`
        FROM `#__table_name` ;
";
$db->setQuery($query);
$form->data['names'] = $db->loadAssocList();
?>

!! Not tested and may need debugging !!

Again using names.id and names.name should work OK.

I haven't checked these and the code may need adjusting to get the format of the sub-array exactly right.

Either should work, if you are OK with MySQL the second gives you much finer control over the data you get back from the table.

Bob

Forums

Dropdown Text_Values from 2 DB-Columns possible ?