1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

PHP onlist load concat

Discussion in 'Community' started by jh, Oct 11, 2019.

  1. jh

    jh Member

    Level: Community
    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
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

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

    Code (Text):

    $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 likes this.
  3. jh

    jh Member

    Level: Community
    Thank you very much, works perfectly.
     
  4. jh

    jh Member

    Level: Community
    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
     

Share This Page