PHP onlist load concat

[jh]

Hi

I have been testing a search/filter field which is created by concat, using the php plugin onload. The fields are a regular text field ('factory_name') and a database join field ('country_retest'). The following code is sort of working:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->update('factory_table')->set('php_testing = concat(factory_name, \' \',country_retest)') ;
$myDb->setQuery($myQuery);
$myDb->execute();

However it shows the ID of the database join element, not the label/value. I think I need to access the value of the field and have tried to do so by following the wiki, but I seem to keep running into error messages.

If someone could kindly show me how to access the value correctly (not the ID) I would be very greatful.

Kind Regards

 

[cheesegrits]

You'll have to left join the countries table and get the 'country_name' (or whatever the field name is) from that ...

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->update("factory_table")
   ->leftJoin("countries on countries.id = factory_table.country_retest")
   ->set("php_testing = concat(factory_name, ' ',countries.country_name)") ;
$myDb->setQuery($myQuery);
$myDb->execute();

Replace table and field names as required.

-- hugh

 

[jh]

Thank you very much, works perfectly.

 

[jh]

Hi

Sorry to bother you again but I wanted to do a similar PHP but with the IDs coming from a repeat group. I have got the IDs using the following (so eg 2,5,11) is returned, working correctly:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->update('pattern_table as d')
->set('d.php_col = (SELECT GROUP_CONCAT(themes_retest) FROM pattern_table_repeat_themes_retest as p WHERE p.parent_id = d.id)');
$myDb->setQuery($myQuery);
$myDb->execute();


So I guess I needed to join pattern_table_repeat_themes_retest to themes (the table holding the data) to get the labels/values. But even after many attempts of trying to use ->leftJoin to join to the repeat group all I get is error messages.

If you could kindly help I would be extremely greatful.

Kind Regards