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

Database join via calc-element many data-inputs

Jun 15, 2019
Database join via calc-element many data-inputs
  • Introduction
    Imagine there are two tables:
    • Tb_roof_photos (table1)
    • Tb_window_photos (table2)
    Question
    How can you automatically join a roof-photo with a window-photo?
    You want to link photos that are from the same housename, location, period, etc.

    Create batches
    You can make a calc-element like this for table1:

    $street = '{table1___street_raw}';
    $date = '{table1___date_time}';
    $date = date("ymd",strtotime($date));
    $house = '{table1___namehouse_raw}';
    $vvvv = ($date. $species. $userID .$town1);

    return crc32($vvvv);

    Than like such you can do for table2 as well in order to generate an equal batch-value.

    Compare batches

    $db = FabrikWorker::getDbo($loadJoomlaDb = false, $cnnId = 11);
    $query = $db->getQuery(true);
    $query->select('window_photo')->from('table2')
    ->where('batch ='. (int)'{table1___batch}');
    $db->setQuery($query);
    $results = $db->loadResult();

    if ($results == "")
    {return 'Add a <a href="index.php/windows">Window Photo</a> that matches the location of your roof';}
    else {return $results;}

    Limiting user-input
    UserID, DateTime, and StreetName can be automatically updated by users. Streetname can be retrieved from the field-element and set autocomplete to Geocomplete in combination with the googlemap-element.

    Result
    If the farmer opens the form a roof photo of the roof can be uploaded and the photo of the window will be automatically added if the autocomplete data that are used to create the batch match with the batch of the other table.